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”,
[
Headers=[
#“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}[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",
[
Headers=[
#"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][boards]{0}[items_page][cursor]
else (Source)[data][next_items_page][cursor],
currentData =
if page = 1
then (Source)[data][boards]{0}[items_page][items] //this only works on page 1 which has [boards] as part of the results response
else (Source)[data][next_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}),
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([StartingPage], 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”
