Connecting Power BI to Monday.com API 2023-10

I’m posting this for the Monday.com customers who are using Power BI to connect to Monday.com.

I attended a webinar last week that discussed the changes to the Monday.com API that will be released in October of this year. The syntax used in the query from the code found here → Outdated – Loading GraphQL data (monday.com API v2) into PowerBI · GitHub will need to be updated.

I forked a copy of the code created by @dipro and updated the M language code to work with the new API.

If you want to continue to use the 2023-07 version of the API you will need to modify your current Power BI Query to include the API-Version you wish to use.

Currently the first bit of your code might look like this:

let
Source = Web.Contents(“https://api.monday.com/”,
[
RelativePath=“v2”,
Headers=[
#“Method”=“POST”,
#“Content-Type”=“application/json”,
#“Authorization”=“<your_personal_token_here>”
],

The API migration document says that the 2023-07 version of the API will still be the default once October 2023 hits but if you want to be certain of the version of the API being used you can modify your code to look like this:

let
Source = Web.Contents(“https://api.monday.com/”,
[
RelativePath=“v2”,
Headers=[
#“Method”=“POST”,
#“Content-Type”=“application/json”,
#“Authorization”=“<your_personal_token_here>”,
#“API-Version” = “2023-10”
],

From the API Migration document the timelines are as follows:

Timeline

July 2023 – version 2023-10 is announced and available for preview

October 2023 – version 2023-10 becomes stable (no new changes), but 2023-07 will still be used by default

January 2024 – version 2023-07 is no longer supported

So you will need to update and test all of your Power BI queries before January or it looks like you Power BI queries will break, causing your reports to no longer work.

Hoping this saves some of you some time.

7 Likes

Thank you for sharing this @hbouk !

2 Likes

This is fantastic, thank you @hbouk !!

1 Like

Please note that the above query will work as long as you have 100 or fewer rows of data. I’m working on a new query that will bring back all pages of data with each page having up to 100 rows. I will post this once I have it figured out. This is turning out to be very complex. :frowning:

5 Likes

Thank you for the note and the help @hbouk !

2 Likes

Howard - thank you so much! Have you had a chance to try out your new query that brings back all pages? Thanks again!

Hi @jimgreene. No, I haven’t had the time to investigate how to accomplish this. I haven’t found a way with the Power Query language to perform a loop so that I could retrieve the cursor and then loop until the cursor is null.

One thought was to rewrite the queries using Pythoin then feed the results into Power BI. It took me a couple of weeks of effort to come up with the solution above so I’m not sure how long it would take to solve this problem.

My client doesn’t have boards with a large number of records to the method I created works and gives them a lot of headroom.

Not a great answer for you and everyone else though.

FYI. According to the API documentation you can up the limit to 500 rows of data so this would expand the query to be able to retrieve 2500 rows of data.

Howard,

Thank you for the feedback.

I’ve been reading and it appears it’s doable looking at some of the examples I’ve seen provided online.

I’ll give it a shot some day when my dataset gets bigger.

At this point in time what you helped with does the trick!

Cheers,

jim

I’m sure someone’s solved this problem before – cursor pagination is a pretty common API pattern; these days it’s considered a best practice in a lot of cases.

I did some googling of “Cursor pagination powerBI” and found this article – apparently List.Generate can get you where you need to go: Article

Anyway, I am sorry that I cannot be of more help! I know enough PowerBI to have muddled together the original example…

Dipro,

Will check it out.

Thank you!

jim

2 Likes

I have just done some work on this and was able to implement the List.Generate function to dynamically paginate it.

I have uploaded the queries here Link

1 Like

Thank you @ariten for sharing this!

2 Likes

Hi all,
I followed what @ariten put in its github, but I have an error with "Column1" in "ExpandRecords" =, I tested modifications with what I had in my old code (a piece of the code: #"JSON " = Json.Document(Source,65001) ), but nothing works, do you have any idea?

Hey, So “ExpandRecords” is expecting a valid return, it does this when an error is returned by the API, as it returns a ERROR object instead of a board or items object.

I would start out by testing the GetFirst and GetNext queries by invoking them separately and ensure they are returning correctly as i suspect your error will be there as the source table is relying on a uniform return and to error at the start something has gone wrong in the loop

Mine had the same problem, does anyone have a solution?

@ariten I went through the entire process, the GetFirst and GetNext queries are behaving correctly, but in the last query they are not grouping, and what’s more, it is giving an error in the Pivot the columns step : Expression.Error: Não conseguimos converter o valor null em tipo Text.
Detalhes:
Value=
Type=[Type]

Help-me

Hi Diogo,

So the pivot line of the source table query cant have null values in it, you should be able to on the right hand side of the query editor in power bi be able to see the step before you get the error, from the error it looks like you have null values or entirely null values in the columns {title} part of the query, (or the column_values (ids: xxx) part) these queries are specific to my use case hence the IDS being processed, you may not need that part, I would start out by testing your query here: monday.com
Then change the queries in the GetFirst and GetNext functions if you need to.
If it got down to pivot then it looks like the query was working and looping correctly just the collumn{title} part of the api query is possible not working for you.

Field ‘items’ doesn’t exist on type ‘Board’

 Source = Web.Contents("https://api.monday.com/",
 [
     RelativePath="v2",
     Headers=[
         #"Method"="POST",
         #"Content-Type"="application/json",
         #"Authorization"= Key
         ],
         Content=Text.ToBinary("{""query"": ""{ boards(ids: xxxxxxxxxxxxxxx) { items { id, name    group{id title}        column_values{ id value text type} } } }""}")
         ]
 ),
 #"JSON" = Json.Document(Source,65001)

in #“JSON”
error /:

Hello there @Mati and welcome to the community!

I hope you like it here :muscle:

It looks like you might be trying to use a deprecated query (boards → items) from our old API version.

You can use the items_page query instead as explained here.

You can find the full migration guide here :grin:

Let me know if you have any other questions!

Cheers,
Matias

1 Like