Skip to main content

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=b

#“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.

Thank you for sharing this @hbouk !


This is fantastic, thank you @hbouk !!


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. 😦


Thank you for the note and the help @hbouk !


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


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


Thank you @ariten for sharing this!


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=pType]


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 💪


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 😁


Let me know if you have any other questions!


Cheers,

Matias


Hello,


I’ve used the updated API mentioned above


let

Source = Web.Contents(“https://api.monday.com/ ”,

b

RelativePath=“v2”,

Headers=>

#“Method”=“POST”,

#“Content-Type”=“application/json”,

#“Authorization”=“<your_personal_token_here>”,

#“API-Version” = “2023-10”

],


But its missing tokens - how is this API expected to be closed?


Hello there @stella.monday and welcome to the community!


The data here looks good to me (of course if you change your_personal_token_here for your actual API token). It might be something related to Power BI (I do not have experience with it)


Having said that, would you be able to please fill this form so our team can take a look into this? Please add as much detail about your case as possible.


Looking forward to hearing from you via the form!


Cheers,

Matias


Reply