@SMac sorry for the slow reply to this. Have you tried clicking on each Applied Step at the right hand side of the screenshot and stepping through each step in the query? It would be helpful to know where the query breaks.

Hi @hbouk ,

thanks for your cursor pagination code, i have many schedular so it would be little bit difficult to update all the extractor code,

below mentioned code i was using since last 8 months and it was working fine but 25th Jan onwards its not working, can you help me to fix the same code , with this code i was able to extract complete data from board.

(Page as number)=>

let

Key = “my token key”,

Board = “my board id”,

Source = Web.Contents(

“https://api.monday.com/v2”,

,

Headers=

#“Method”=“POST”,

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

#“Authorization”=“Bearer " & Key

],

Content=Text.ToBinary(”{““query””: ““query { boards(ids: " & Board & “) { items(limit:700,page:” & Number.ToText(Page) & “) { name, updated_at, group { title }, columns: column_values { title, text } } } }””}”)

]

),

Data = Table.FromList(Json.Document(Source) data]
#“Monday” = Table.FromRecords(Table.TransformRows(Data, each

List.Accumulate(�Columns], F

Title = aTitle],

UpdateDate = UpdatedAt],

Group = rGroup]>title]

], (state, current) => Record.AddField(state, currentGtitle], current>text]) )

)),

#“Changed Type” = Table.TransformColumnTypes(Monday,{{“Request Date”, type date}, {“Completed Date”, type date}, {“Target Go Live”, type date}}),

in

#“Changed Type”

Sorry @ASYADAV5. No good news for you about your current query.

If you have a look at the API documentation you will see that Monday.com started warning people in October of last year about the changes that were going to take place in January.

You now have to use a query that looks like this:

query {

  boards (ids: 4579863192) {

    items_page (limit: 5) {

      cursor

      items {

        id

      }

    }

  }

}

which retrieves a cursor that is then used in this query to bring the next page:

query {

  next_items_page (limit: 5, cursor:"MSw0NTc5ODYzMTkyLFRWX2ljOWt2MVpnTjFjelRadUR3Vyw3LDV8MTIyMTY3OTk4OA") {

    cursor

    items {

      id

    }

  }

}

My solution isn’t ideal and I have been working on the next version which uses looping to bring back all pages. If/once I can get it working it will be eaasier to manage that what I currently posted.

I’ll post again once I get it working fully.

First of all, I would like to thank you for starting to share this very useful function code.

I modified the code created by “Guilherme Chaguri” by adding a recursive function to use cursor to get next page data. It worked and I posted my sample function file back to Guilherme’s Github.

You can download my file to use it, Or feel free to use it to improve your function in next versions.

Get Monday Data API-2024-01 v1.0.pbix

Click here to go to my post in Guilherme’s Github.

Thanks @Wora2024. I had been working on a recursive function too but haven’t had much time to spend on it. I downloaded your sample and gave it a try. It does the job nicely.

Hi all, thanks for the tips in this page. I managed to develop my query thanks to this forum.

This is my main query, which reads the first page them calls the recursive function for more pages:

let

// Fetch the first page of items

Source = Web.Contents(

“https://api.monday.com/v2”,



Headers=e

#“Method”=“POST”,

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

#“Authorization”=“YOUR AUTHORIZATION CODE HERE”

],

	Content=Text.ToBinary("{""query"": ""{ boards (ids: "YOUR BOARD ID HERE]) { columns { id title } items_page (limit: 50) {cursor items { id name column_values { text id column {title} } } } } }""}")

]

),

#"JSON" = Json.Document(Source),



data = JSONedata],

boards = dataNboards],

boards1 = List.First(boards),

columnsList = boards1
items_page = boards1,items_page],

cursor = items_page]cursor],

items = items_pageritems],



// Call the recursive function to fetch items from subsequent pages

otherItems = otherItems = if cursor <> null then ReadNextItemPage(cursor) else {},

AllItems = List.Combine({items, otherItems}),



// Transform the column names into a table.

//transformToTableColumns = Table.FromList(columnsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

//#"Expanded Column2" = Table.ExpandRecordColumn(transformToTableColumns, "Column1", {"id", "title"}, {"id", "title"}),



// Transform the data into a table.

transformToTable = Table.FromList(AllItems, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#"Expanded Column1" = Table.ExpandRecordColumn(transformToTable, "Column1", {"id", "name", "column_values"}, {"id", "name", "column_values"}),

#"Expanded column_values" = Table.ExpandListColumn(#"Expanded Column1", "column_values"),

#"Expanded column_values1" = Table.ExpandRecordColumn(#"Expanded column_values", "column_values", {"column", "text", "id"}, {"column", "text", "id.1"}),

#"Expanded column" = Table.ExpandRecordColumn(#"Expanded column_values1", "column", {"title"}, {"title"}),

#"Removed Columns" = Table.RemoveColumns(#"Expanded column",{"id.1"}),

#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns".title]), "title", "text"),

#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"id", "ID"}})

in

#“Renamed Columns”

This is the recursive function that reads the remaining pages:

let

ReadNextItemPage = (cursor) =>

let

// Make the API call to fetch the next item page

Source2 = Web.Contents(

“https://api.monday.com/v2”,

a

Headers=>

#“Method”=“POST”,

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

#“Authorization”=“YOUR AUTHORIZATION CODE HERE”

],

Content=Text.ToBinary(“{”“query”“: “”{ next_items_page (cursor:"”“&cursor&”"“) {cursor items { id name column_values {column {title} text id } } } }”“}”)

]

),

#"JSON2" = Json.Document(Source2),



data3 = #"JSON2")data],

boards3 = data3Nnext_items_page],

nextCursor = boards3tcursor],

items = boards3ritems],



// If there are more items to fetch, recursively call the function

// Otherwise, return an empty list to terminate recursion

otherItems = if nextCursor <> null then ReadNextItemPage(nextCursor) else {},



// Return the current page items appended with items from the next pages

AllItems = List.Combine({items, otherItems})

in

AllItems

in

ReadNextItemPage

Hi @albericipeira. It makes me happy to see another solution to recursively retrieve data from Monday.com using paginated calls. I’m jealous that I didn’t have the time to solve this myself. 😉

Thank you all for sharing!!!

This didn’t work for me. Can anybody help me?

Welcome @Fau. You have a couple of formatting issues with your query string. You can’t provide a cursor value to the items_page query, this can only be supplied to the next_items_page query.

When I reproduced your query in Postman and eliminated the two things crossed out in the image I was able to retrieve data from one of my boards.

I would have a look at the post from @Wora2024 seven posts above. He provides a PBIX file that you can download and quickly update to pull data from Monday once you provide your board # and authorization key.

Thank you so much, is there any way to search for historical data?

that is, the activity logs of each element.

One more time. Thank you so much.

@Fau I’m not sure what you mean by activity logs of each element but this might get you started in the direction you are hoping for.

Hi @hbouk thanks for support, i am using this code and working fine, but if next page data is not coming and its giving error, means manually we have create page as per rows count, or is there any other solution

Hi @hbouk Maybe this could help with grouping.

This was code I inherited and it works for producing Groups and getting things turned into a table properly.

Data = Table.FromList(Json.Document(Source)[data][boards]{0}[items_page][items], Record.FieldValues, {"Title", "UpdatedAt", "Group", "Columns"}),

Monday = Table.FromRecords(Table.TransformRows(Data, each

    List.Accumulate([Columns], [

        Title = [Title],

        UpdateDate = [UpdatedAt],

        Group = [Group][title]

    ], (state, current) => Record.AddField(state, current[column][title], current[text]) )

    )),

Found the proper way to look a single query dynamically. With the help of Parker from BI Elite.

NJM Banana (:15) (youtube.com)

This is my primary function doing the loop, based on Parker’s video.

(page as number, optional continuationCursor as text, optional data as list) =>

let

//need a dynamic pageNum MAX in case data grows substantially in the future

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

b

Headers=a

#“Method”=“POST”,

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

#“API-Version”=#“UseApiVersion”,

#“Authorization”=“Bearer " & #“AuthKey”

],

Content=Text.ToBinary(”{““query””: ““query { boards(ids: " & BoardNbr_SalesPipeline & “) { items_count } }””}”)

]),

itemsCountData = Json.Document(itemsCount)bdata]]boards]{0}/items_count],

maxPageNum = Int64.From(((itemsCountData / 100) + 10)),

firstPage = Text.ToBinary("{""query"": ""query { boards(ids: " & BoardNbr_SalesPipeline & ") { items_page (limit: 100) { cursor, items { name, updated_at, group { title }, columns: column_values { column { title }, text } } } } }""}"),

nextPage = Text.ToBinary("{""query"": ""query { next_items_page (limit: 100, cursor: \\""" & continuationCursor & "\\"") { cursor, items { name, updated_at, group { title }, columns: column_values { column { title }, text } } } }""}"),



Source = Json.Document(Web.Contents("https://api.monday.com/v2",

J   

    Headers=n

        #"Method"="POST",

        #"Content-Type"="application/json",

        #"API-Version"=#"UseApiVersion",

        #"Authorization"="Bearer " & #"AuthKey"

    ],

Content=

    if page = 1

    then firstPage

    else nextPage

])),

nextCursor = 

    if page = 1

    then (Source)/data]tboards]{0}bitems_page] cursor]

    else (Source))data]]next_items_page]scursor],

currentData =

    if page = 1

    then (Source)/data]rboards]{0}bitems_page] items] //this only works on page 1 which has ]boards] as part of the results response

    else (Source)odata] next_items_page]eitems], //this only works on followup pages using the cursor and the  next_items_page]

                                                //but once these results reach this state, the file list of records are identical and can be appended without an issue

appendedData =

    if currentData is null and data is null then {}

    else if data is null then List.Combine({{}, currentData})   //I am not 100% certain the purpose of having this line and the next. I would assume if a null result returned in the data, this 1st line would take effect and the next line would never occur

    else if data is null then List.Combine({data, {}})

    else List.Combine({data, currentData}),

pageNum = page + 1,

output =

    if nextCursor is null or maxPageNum > 100   //the cursor should come back null when the final page is reached and there are no further pages of results to return. This will still work on the FirstPage call also because that will return the Cursor if there is a Page 2 available.

                                                //a Max Page needs used to make sure it doesn't get stuck in an endless loop

    then appendedData //I don't believe the next line was needed for our purpose, instead, I'm just doing "then appendedData" here

    //if appendedData is null then {1,2,3} else appendedData    //this line came from the example of this API Loop found on BI Elite YouTube channel

    else @GetSalesPipelineCursorPagination(pageNum,nextCursor,appendedData)

in

output

Then this is my actual table running that function on it. It isn’t 100% complete yet because I need to expand the Records and Lists yet, but its 99% there by being successful so far.

let

Source = List.Numbers(1,1),

#“Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#“Starting Page” = Table.TransformColumnTypes(Table.RenameColumns(#“Converted to Table”,{{“Column1”, “StartingPage”}}),{{“StartingPage”, Int64.Type}}),

#“Invoked Custom Function” = Table.AddColumn(#“Starting Page”, “SalesPipelineApiResults”, each GetSalesPipelineCursorPagination(dStartingPage], null, null)),

#“Expanded ListToRows” = Table.ExpandListColumn(#“Invoked Custom Function”, “SalesPipelineApiResults”),

#“Expanded RecordsToColumns” = Table.ExpandRecordColumn(#“Expanded ListToRows”, “SalesPipelineApiResults”, {“name”, “updated_at”, “group”, “columns”}, {“name”, “updated_at”, “group”, “columns”})

in

#“Expanded RecordsToColumns”

There was only 1 final step to open up all of the records.

let

Source = List.Numbers(1,1),

#“Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#“Starting Page” = Table.TransformColumnTypes(Table.RenameColumns(#“Converted to Table”,{{“Column1”, “StartingPage”}}),{{“StartingPage”, Int64.Type}}),

#“Invoked Custom Function” = Table.AddColumn(#“Starting Page”, “SalesPipelineApiResults”, each GetSalesPipelineCursorPagination(cStartingPage], null, null)),

#“Expanded ListToRows” = Table.ExpandListColumn(#“Invoked Custom Function”, “SalesPipelineApiResults”),

#“Expanded RecordsToColumns” = Table.ExpandRecordColumn(#“Expanded ListToRows”, “SalesPipelineApiResults”, {“name”, “updated_at”, “group”, “columns”}, {“Title”, “UpdatedAt”, “Group”, “Columns”}),

#“Expanding RecordsAndList” = Table.FromRecords(Table.TransformRows(#“Expanded RecordsToColumns”, each

List.Accumulate(lColumns], r

Title = oTitle],

UpdateDate = sUpdatedAt],

Group = uGroup]Ctitle]

], (state, current) => Record.AddField(state, current
)),

Here is my completed looping function, I added in the dynamic max page number to use in the loop exit options of the “output” variable at the end.

(pageNbr as number, optional continuationCursor as text, optional data as list) =>

let

//need a dynamic pageNum MAX in case data grows substantially in the future

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

>

Headers=H

#“Method”=“POST”,

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

#“API-Version”=#“UseApiVersion”,

#“Authorization”=“Bearer " & #“AuthKey”

],

Content=Text.ToBinary(”{““query””: ““query { boards(ids: " & BoardNbr_SalesPipeline & “) { items_count } }””}”)

]),

itemsCountData = Json.Document(itemsCount)>data]>boards]{0}ritems_count],

maxPageNum = Int64.From(((itemsCountData / 100) + 10)),

firstPage = Text.ToBinary("{""query"": ""query { boards(ids: " & BoardNbr_SalesPipeline & ") { items_page (limit: 100) { cursor, items { name, updated_at, group { title }, columns: column_values { column { title }, text } } } } }""}"),

nextPage = Text.ToBinary("{""query"": ""query { next_items_page (limit: 100, cursor: \\""" & continuationCursor & "\\"") { cursor, items { name, updated_at, group { title }, columns: column_values { column { title }, text } } } }""}"),



Source = Json.Document(Web.Contents("https://api.monday.com/v2",

=   

    Headers=t

        #"Method"="POST",

        #"Content-Type"="application/json",

        #"API-Version"=#"UseApiVersion",

        #"Authorization"="Bearer " & #"AuthKey"

    ],

Content=

    if pageNbr = 1

    then firstPage

    else nextPage

])),

nextCursor = 

    if pageNbr = 1

    then (Source)>data]Cboards]{0}ritems_page]pcursor]

    else (Source)cdata]tnext_items_page]ecursor],

currentData =

    if pageNbr = 1

    then (Source)>data]eboards]{0}ritems_page]pitems] //this only works on page 1 which has dboards] as part of the results response

    else (Source)[data]snext_items_page] items], //this only works on followup pages using the cursor and the ]next_items_page]

                                                //but once these results reach this state, the file list of records are identical and can be appended without an issue

appendedData =

    if currentData is null and data is null then {}

    else if data is null then List.Combine({{}, currentData})   //I am not 100% certain the purpose of having this line and the next. I would assume if a null result returned in the data, this 1st line would take effect and the next line would never occur

    else if data is null then List.Combine({data, {}})

    else List.Combine({data, currentData}),

nextPageNbr = pageNbr + 1,

output =

    if nextCursor is null or nextPageNbr > maxPageNum   //the cursor should come back null when the final page is reached and there are no further pages of results to return. This will still work on the FirstPage call also because that will return the Cursor if there is a Page 2 available.

                                                //a Max Page needs used to make sure it doesn't get stuck in an endless loop

    then appendedData //I don't believe the next line was needed for our purpose, instead, I'm just doing "then appendedData" here

    //if appendedData is null then {1,2,3} else appendedData    //this line came from the example of this API Loop found on BI Elite YouTube channel

    else @GetSalesPipelineCursorPagination(nextPageNbr,nextCursor,appendedData)

in

output

Hi, Howard

Changing the privacy level really solves it for the desktop version.

But when I publish it to the server (on-premise) it doesn’t work.

Is there a parameter I need to change on the server too?

Hi @davidalkoby

Are you using a Power BI Report Server on-premise? I don’t have any experience publishing to a Power BI Report Server.

Have you or do you have the ability to publish to the Power BI Service (in the Microsoft cloud)? I know that the privacy level is maintained when publishing to the Power BI Service.

Have you tried posting in the Power BI Forums/Microsoft Fabric Community? It seems like someone else is having a problem with the Reporting Server and privacy levels.

Good luck!

Hi, Howard

Thanks for your quick response.

We are using On-premise report server for various reasons.

I did see that working in with cloud i.e. report service has the ability to keep the privacy levels but as I searched through the Microsoft boards there’s no such tool for the report server.

I hoped that someone here has some experience or tweak to make it possible.

Hi @davidalkoby

I found this indicating that data privacy levels are not supported on Power BI Report Server

Unfortunately, I don’t have any experience with this or the environment to test.

Hi, Howard

Thanks for all the code you uploaded and help.

Just to let anyone who has the same problem - I solved it!!!

You should change the privacy level for the whole desktop and not just for this specific file.

Hello, Howard! How are you?

I’ve been using your query to integrate Power BI with Monday.comand it was working perfectly. However, today there was an error in updating the data and when I checked, the query had the following error:

GetBoard_1stPage’. Expression.Error: We couldn’t convert the null value to List type.

Can you help me?

Hi @arianetiffani. My guess is that you have a blank value in the first column of one of the rows of data in your board.

Hi Andrew, thank you for providing the code. I’ve been attempting to retrieve data, but it’s not functioning as anticipated. Do you happen to have the complete code where we can dynamically fetch all the data from any board on Monday.com? I have approximately 3000 records, which are gradually increasing, so I’m seeking a dynamic query to manage this. Your guidance would be greatly appreciated.