Power BI: Migrating from the Old API to the New API - How to Overcome the 500 row Limit?

monday Apps & Developers
, ,
1

I’m in the process of migrating my old Power BI API code to the new version. However, a significant challenge arises as Monday.com has imposed an API limit allowing the retrieval of no more than 500 rows at a time. This limitation is frustrating because I aim for the new API to dynamically capture all rows, regardless of whether there are 500 or 2000 rows in my board. Is there a dynamic solution to overcome this restriction?

2

Well that is what the cursor on items_page is for, the value of the cursor is used with a next_items_page query fetch the next page of items.

Just an FYI the old version had a limit of 1000 items in a single request - if you were lucky enough to fetch that many before the request timed out and failed. Thats why pagination is utilized, so that you can fetch small enough batches to stay under 60s per request.

1 Like
3

Thank you for your response. The issue is that, up to this point, I haven’t come across any instructions on how to implement dynamic cursor pagination in Power BI, given its restriction on loops. Here’s my straightforward API query. Do you have any suggestions on where to begin tweaking it to ensure the dynamic retrieval of all my rows? Your assistance is greatly appreciated! Thank you!

let
MondayQuery1 = “{”“query”“: “”{boards(ids: “,
MondayQuery2 = “BOARD ID”,
MondayQuery3 = “) {items_page(limit: 100) {cursor items {id name column_values {text column {title}}}}}}””}”,
MondayFullQuery = MondayQuery1 & MondayQuery2 & MondayQuery3,
Source = Web.Contents(“https://api.monday.com/”,
[
RelativePath=“v2”,
Headers=
[
#“Method”=“POST”,
#“Content-Type”=“application/json”,
#“Authorization”=“MY API TOKEN”,
#“API-Version”=“2023-10”
],
Content=Text.ToBinary(MondayFullQuery)
]
),
#“JSON” = Json.Document(Source, 65001),
data = JSON[data],
boards = data[boards],
#“Converted to Table” = Table.FromList(boards, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#“Converted to Table”

1 Like
4

I admit I am not a Power BI guru. To be honest, you may want to experiment with using an azure function to do all the monday work and then push the data into power BI from that.

Simplest case is an azure function that Power BI makes a call to, the function does all the work, then returns the results to power BI.

Having access to a more robust programming language than Power BI may be useful so you can do post processing of the data in azure where you copy mirror column display values to text for example.

1 Like
5

Can you tell me more about the Azure function please, is there any web link where I could do some reading? Thanks.

6

Depends on your programming experience though.

I just remembered though monday code is now a thing, and since that’s monday based you can get support from monday.com on it.

But with time short you may be limited… if you’re using a personal token for the API go to monday.labs (in your accounts avatar icon menu) and activate the one month API extension for your account. Then your token will still support the old API as the default for an extra month until you sort stuff out.

1 Like