Hello @ASYADAV5,
Here is what I got to work for retrieving a complete table of multiple pages.
This picture shows you its all done with 3 parameters, 1 function that loops, and 1 table that is loaded as the final results.
Important notes:
-parameters were all set to be Text, even the Board Number
-the loaded table, which runs the function and brings all results together, had to do some extra steps because this Monday Board contained some fields that are Connected Boards. Due to changes for the 2023-10 API Version, those values no longer come back from the simple Values call. Now it needed to include "… on BoardRelationValue {display_value} to get those values as another column in the results. But the issue was then PowerQuery couldn’t handle that without throwing errors because some records had the 3rd column and other records didn’t if a value wasn’t present.
-technically this might work for any board. I think column/field specifics are only coming into play once results are in PowerQuery for additional ETL steps. I am not familiar enough with the Monday Querying to say nothing is specific between this and what is present on the actual Board. These may be all meta-data naming that exist for all or more Boards.
-I don’t know if this was an Us problem or a Monday problem, but when going to the 2024-04 version, we started getting some concatenated duplicates of 1 field.
Code below, this is the entire M-code of each query.
This first 1 is the Function
(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=b
#“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}
maxPageNum = Int64.From(((itemsCountData / 100) + 10)),
firstPage = Text.ToBinary("{""query"": ""query { boards(ids: " & BoardNbr_SalesPipeline & ") { items_page (limit: 100) { cursor, items { id, name, updated_at, group { title }, column_values { column { title }, text, ... on BoardRelationValue { display_value }} } } } }""}"),
nextPage = Text.ToBinary("{""query"": ""query { next_items_page (limit: 100, cursor: \\""" & continuationCursor & "\\"") { cursor, items { id, name, updated_at, group { title }, column_values { column { title }, text, ... on BoardRelationValue { display_value }} } } }""}"),
Source = Json.Document(Web.Contents("https://api.monday.com/v2",
o
Headers=e
#"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]>boards]{0}ritems_page] cursor]
else (Source) data]cnext_items_page]{cursor],
currentData =
if pageNbr = 1
then (Source),data]>boards]{0}titems_page] items] //this only works on page 1 which has ]boards] as part of the results response
else (Source)hdata][next_items_page]oitems], //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
this 1 is the loaded table that runs the function:
let
Source = List.Numbers(1,1),
#“Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#“First Page” = Table.TransformColumnTypes(Table.RenameColumns(#“Converted to Table”,{{“Column1”, “FirstPage”}}),{{“FirstPage”, Int64.Type}}),
#“Invoked Custom Function” = Table.AddColumn(#“First Page”, “SalesPipelineApiResults”, each GetSalesPipelineCursorPagination(=FirstPage], null, null)),
#“Expanded ListToRows” = Table.ExpandListColumn(#“Invoked Custom Function”, “SalesPipelineApiResults”),
#“Expanded RecordsToColumns” = Table.ExpandRecordColumn(#“Expanded ListToRows”, “SalesPipelineApiResults”, {“id”, “name”, “updated_at”, “group”, “column_values”}, {“ItemId”, “Title”, “UpdatedAt”, “Group”, “Columns”}),
#“Expand Columns to Records” = Table.ExpandListColumn(#“Expanded RecordsToColumns”, “Columns”),
#“Expand Text Values” = Table.ExpandRecordColumn(#“Expand Columns to Records”, “Columns”, {“column”, “display_value”, “text”}, {“column”, “display_value”, “text.original”}),
#“Added Conditional Column” = Table.AddColumn(#“Expand Text Values”, “text”, each if display_value] <> null then sdisplay_value] else xtext.original]),
#“Added Custom” = Table.AddColumn(#“Added Conditional Column”, “columns”, each Record.SelectFields(#column], text]],{“column”,“text”})),
#“Removed Columns” = Table.SelectColumns(#“Added Custom”,{“ItemId”, “Title”, “UpdatedAt”, “Group”, “columns”}),
#“Grouped Rows” = Table.Group(#“Removed Columns”, {“ItemId”, “Title”, “UpdatedAt”, “Group”}, {{“Columns”, each _columns] }}),
#“Expanding RecordsAndList” = Table.FromRecords(Table.TransformRows(#“Grouped Rows”, each
List.Accumulate(Columns], }
ItemId = uItemId],
Title = Title],
UpdateDate = dUpdatedAt],
Group = Group]title]
], (state, current) => Record.AddField(state, currentRcolumn]ntitle], currentetext]) )
))
in
#“Expanding RecordsAndList”
@JustAnotherMonday Thank you very much, its really great help for fast track.
Your very welcome. Happy to see this help others as well.
Hi all I need help. I have use 2023-07 api and now it show “The field ‘data’ of the record wasn’t found.”
can you help me fix it or convert it
let
Source = Web.Contents(“https://api.monday.com/” & “v2”,
Headers=b
#“Method”=“POST”,
#“Content-Type”=“application/json”,
#“Authorization”=“Bearer mykey”
],
Content=Text.ToBinary(“{”“query”“: “”{ boards (ids: 3796729499) { items { id name column_values { title text } parent_item {id} } } }”“}”)
]
),
#“JSON” = Json.Document(Source,65001),
data = JSON>data],
boards = dataoboards],
boards1 = boards{0},
items = boards1[items],
#“Converted to Table” = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
and another table is
Content=Text.ToBinary(“{”“query”“: “”{ boards (ids: 3796729500) { items { id name column_values { title text } } } }”“}”)
I’m trying with all I know but not working. thanks in advance
Hello there @suriyapong,
I do not have experience using Power BI, but on monday’s end (regarding the API), you need to switch from a boards → items structure to a boards → items_page → items structure as shown here!
I hope that helps!
Cheers,
Matias
Hi @JustAnotherMonday , Looking for your advice: The code has been working perfectly for the past year, but today it’s returning errors. My board count hasn’t changed, so it seems the issue might be elsewhere.
Error is “An error occurred in the ‘’ query. DataSource.Error: Web.Contents failed to get contents from ‘https://api.monday.com/v2’ (429): Too Many Requests
Details:
DataSourceKind=Web
DataSourcePath=https://api.monday.com/v2
Url=https://api.monday.com/v2”
Hi @ASYADAV5 ,
I would focus on the fact your error is 429 for too many requests. Error handling
Do you know how many pages you are pulling per run of this function?
How many records?
Do you have multiple versions or copies of that 1 query and maybe other queries doing other calls? Also consider if you have multiple environments/workspaces/datasets.
I assume this query limit may apply across all of your Monday API calls within a minute. Could your combined count be close to 5,000 in 1 minute? I’m just making an unverified guess that 5,000 queries means each page of results can be 1 query. That could change if Monday considers each record in the page as a query.
I hope this helps kickstart a successful troubleshooting.
Sorry I never saw your response on this thread. Did you find your solution? Please provide info again if still having issues, so I don’t make suggestions based on old code.
Hi Monday Community,
Thank you so much for this post. I am just starting out with M coding, and my next challenge is to bring in more than 500 items from a board across to Power BI/Query.
I understand this involves concepts related to pagination, cursor, items_page, looping.
Do you think if I edit this code for items=500 it would work for me to retrieve all board items?
I was trying to work with this pbix file but I am facing credentials authentication related issues because I am using this base url: https://api.monday.com/v2.
Anyway, here is my code so far… if anybody can please offer me guidance as to how I can incorporate the aforementioned concepts for me to be able to increase the board items I have:
let
BaseURL = “https://api.monday.com/v2”,
Query = “{”“query”“: “”{ boards(ids: My Board ID) { items_page (limit:500) { cursor items { name column_values { column { title } text text …on MirrorValue { display_value } …on DependencyValue { display_value } …on BoardRelationValue { display_value } } } } } }”“}”,
Headers =
#“Method”=“POST”,
#“API-Version”=“2024-01”,
#“Content-Type”=“application/json”,
#“Authorization”=“My Token”
],
Source = Web.Contents(BaseURL, Headers = Headers, Content = Text.ToBinary(Query)]),
JSON = Json.Document(Source, 65001),
data = JSONbdata],
boards = datacboards],
boards1 = boards{0},
items_page = boards1]items_page],
items = items_page>items],
#“Converted to Table” = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#“Show ID” = Table.ExpandRecordColumn(#“Converted to Table”, “Column1”, {“name”, “column_values”}, {“Column1.name”, “Column1.column_values”}),
#“Expanded Column1.column_values2” = Table.ExpandListColumn(#“Show ID”, “Column1.column_values”),
#“Expanded Column1.column_values3” = Table.ExpandRecordColumn(#“Expanded Column1.column_values2”, “Column1.column_values”, {“column”, “text”, “display_value”}, {“Column1.column_values.column”, “Column1.column_values.text”, “Column1.column_values.display_value”}),
#“Expanded Column1.column_values.column1” = Table.ExpandRecordColumn(#“Expanded Column1.column_values3”, “Column1.column_values.column”, {“title”}, {“Column1.column_values.column.title”}),
#“Added Value Column” = Table.AddColumn(
#“Expanded Column1.column_values.column1”,
“Value”,
each
let
rawText = >Column1.column_values.text],
displayText = /Column1.column_values.display_value],
safeText = if Value.Is(rawText, type list) then Text.Combine(List.Transform(rawText, each Text.From()), ", ") else Text.From(rawText),
safeDisplay = if Value.Is(displayText, type list) then Text.Combine(List.Transform(displayText, each Text.From()), ", ") else Text.From(displayText)
in
if safeText = null or safeText = “” then safeDisplay else safeText
),
#“RenamedColumns1” = Table.RenameColumns(#“Expanded Column1.column_values.column1”,{{“Column1.name”, “Name”}, {“Column1.column_values.column.title”, “Title”}, {“Column1.column_values.text”, “RawValue”}, {“Column1.column_values.display_value”, “DisplayValue”}}),
#“ReversedRows” = Table.ReverseRows(#“RenamedColumns1”),
#“ReversedRows1” = Table.ReverseRows(#“ReversedRows”),
#“Filtered Rows3” = Table.SelectRows(ReversedRows1, each ((iTitle] <> “Division” and Title] <> “Family”) or (eTitle] = “Division” and RawValue] <> null) or (wTitle] = “Family” and oRawValue] <> null))),
#“AddedValueColumn” = Table.AddColumn(#“Filtered Rows3”, “Value”, each if tRawValue] = null then TDisplayValue] else aRawValue]),
#“ReorderedColumns” = Table.ReorderColumns(#“AddedValueColumn”,{“Title”, “Name”, “RawValue”, “Value”}),
#“RemovedUnusedColumns” = Table.RemoveColumns(#“ReorderedColumns”,{“RawValue”, “DisplayValue”}),
PivotedTable = Table.Pivot(#“RemovedUnusedColumns”, List.Distinct(#“RemovedUnusedColumns”uTitle]), “Title”, “Value”)
in
PivotedTable
I would very much appreciate any help that I can get
Hello @ayesha_chopra,
I unfortunately don’t have the time to fully work through your code and help. The tips I can tell you, review the code I shared earlier in this thread. Its a 2 step process, where the Function that can loop is what does the pagination and the core API call to Monday. Just isolate the code query pieces and modify to fit what your Monday calls need to be, as you may be grabbing an entirely different type of data than me just grabbing by the Board.
Nearly all of the rest is just Power BI M-code for different pieces of ETL. The only difficult part was having to work with Linked Tables and how Monday changes the way those are retrieved. This was resolved by the final table having steps to Expand, Add a custom column, Group it back up, then Expand again for the Records and List to properly expand into the Columns and Values needed in Power BI.
Hi There,
Can anyone please guide me how I can bring across connected board values as it is? As in, and exact copy.
I have changed the following lines in the provided pbix file.
GetBoard_1stPage
MondayQuery3 = “) {items_page(limit: 500) {cursor items {id name column_values { column { title } text …on MirrorValue { display_value } …on DependencyValue { display_value } …on BoardRelationValue { display_value } } } } } }”“}”,
Monday2ndQuery3 = “"”, limit: 500) {cursor items {id name column_values { column { title } text …on MirrorValue { display_value } …on DependencyValue { display_value } …on BoardRelationValue { display_value } } } } } }“”}",
Error: An error occurred in the ‘GetBoard_RemainingPages’ query. DataSource.Error: Web.Contents failed to get contents from ‘https://api.monday.com/v2’ (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://api.monday.com/v2
Url=https://api.monday.com/v2
Though the error itself is referring to only GetBoard_RemainingPages query, it is not bringing across the mirrored/connected board values even in the first page of the data.
Could anyone please advise?
Hi @ayesha_chopra,
I also don’t have the time to help and I know this isn’t the answer you want but @JustAnotherMonday is bang on right. It is a matter of using the samples provided or your own code and the “Just isolate the code query pieces and modify to fit what your Monday calls need to be”. Step through the code and determine where things break, experiment until you get what you need.
I like using a combination of Postman and Power Query to work through the correct API call structure. I recently had to go through the same pain with the Asana API. I found a sample set of queries and functions which I then had to modify because my boards had custom columns that the samples queries didn’t request.
Monday supplied the API but that is like dumping a lego set on the floor. Now you have to grab the right pieces and build what you want.
I wish I had the time to help, good luck.
Howard
