API 2023-10 Pagination Cursor Not working

Platform discussions
, ,
1

Hi All!

I am having issues getting the Cursor to work in my API call in PowerQuery/PowerBI.

I haven’t currently found a way to run it all in 1 function or query yet, so I’m attempting to break it down into 2 functions first (1 for the first page, then 1 for all of the following pages needing the Cursor). I wanted to get these both working before I attempted to finalize a query to bring all of the results together.

This First Page query works perfectly fine.

(Response as text) =>
let
Key = #“AuthKey”,
Board = BoardNbr_SalesPipeline,
Version = #“UseApiVersion”,
Source = Web.Contents(
“https://api.monday.com/v2”,
[
Headers=[
#“Method”=“POST”,
#“API-Version”=Version,
#“Content-Type”=“application/json”,
#“Authorization”=“Bearer " & Key
],
Content=Text.ToBinary(”{““query””: ““query itemsPageOnBoard{ boards(ids: " & Board & “) { items_page (limit: 100) { cursor, items { name, updated_at, group { title }, columns: column_values { column { title }, text } } } } }””}”)
]
),
Data = Json.Document(Source)[data][boards]{0}[items_page][items],
GetCursor = Json.Document(Source)[data][boards]{0}[items_page][cursor],

WhatToReturn = Response,
Return = if WhatToReturn = "Cursor" then GetCursor else if WhatToReturn = "Results" then Data else "Must Return Cursor or Results"

in
Return

Then this second query, which is identical, besides adding the variables for Cursor/NewCursor and working the NewCursor into the API call where the Page limit is, just like Monday’s documentation suggests it must. Once this is working, I would be deleting Lines 3, 4, and 19, and uncommenting Line 1 to be a working Function. I confirmed this works when removing the Cursor pieces. I have tried playing around with many iterations of treating or converting that Cursor as Text, in case it was attempting to present it in the API call as Text because of using the Variable.

I have also tried with items_page like this, and with next_items_page. It keeps returning a Code 500. I am inserting the exact Cursor I get back from the first function.

//(Response as text, NewCursor as text) =>
let
Cursor = “MSwxMjI1NzY1MjczLDZGYXNacEFXRW1Zb3lHMDVHVWU1RCwyMzg5LDEwMCx8MjkzNDk0Mjk3”,
Response = “Cursor”,
Key = #“AuthKey”,
Board = BoardNbr_SalesPipeline,
Version = #“UseApiVersion”,
NewCursor = Cursor,
Source = Web.Contents(
“https://api.monday.com/v2”,
[
Headers=[
#“Method”=“POST”,
#“API-Version”=Version,
#“Content-Type”=“application/json”,
#“Authorization”=“Bearer " & Key
],
Content=Text.ToBinary(”{““query””: ““query itemsPageOnBoard{ boards(ids: " & Text.From(Board) & “) { items_page (limit: 100, cursor:” & NewCursor & “) { cursor, items { name, updated_at, group { title }, columns: column_values { column { title }, text } } } } }””}”)
// Content=Text.ToBinary(“{”“query”": ““query itemsPageOnBoard{ boards(ids: " & Text.From(Board) & “) { items_page (limit: 100) { cursor, items { name, updated_at, group { title }, columns: column_values { column { title }, text } } } } }””}”)
]
),
Data = Json.Document(Source)[data][boards]{0}[items_page][items],
GetCursor = Json.Document(Source)[data][boards]{0}[items_page][cursor],

WhatToReturn = Response,
Return = if WhatToReturn = "Cursor" then GetCursor else if WhatToReturn = "Results" then Data else "Must Return Cursor or Results"

in
Return

2

Found the answer by taking a close look at @hbouk 's one post. By wrapping the Cursor with some \ \ , this must have helped establish the Text type needed that wasn’t happening with “”" alone, or if the \ \ was something missing in Monday’s documentation for identifying the Cursor value at times. Then I was also able to remove the Boards part of the Json unwrapping and configuring of the data. I’ll have to see if that impacts my final testing, but the fact that results return now is wonderful news :slight_smile:

I also noticed how the Board ID isn’t needed when running the next_items_page.

(Response as text, Cursor as text) =>
let
Key = #“AuthKey”,
//Board = BoardNbr_SalesPipeline,
Version = #“UseApiVersion”,
NewCursor = Cursor,
Source = Web.Contents(
“https://api.monday.com/v2”,
[
Headers=[
#“Method”=“POST”,
#“API-Version”=Version,
#“Content-Type”=“application/json”,
#“Authorization”=“Bearer " & Key
],
Content=Text.ToBinary(”{““query””: ““query { next_items_page (limit: 100, cursor: "“” & NewCursor & “"”) { cursor, items { name, updated_at, group { title }, columns: column_values { column { title }, text } } } }””}")
]
),
Data = Json.Document(Source)[data][next_items_page][items],
GetCursor = Json.Document(Source)[data][next_items_page][cursor],

WhatToReturn = Response,
Return = if WhatToReturn = "Cursor" then GetCursor else if WhatToReturn = "Results" then Data else "Must Return Cursor or Results"

in
Return