Need help migrating to API 2023-10 changes - using PowerQuery

Platform discussions
, , ,
1

Hello All!

I am new to Monday.com API and new to this client. We are using PowerQuery in Power BI to query the API for the information we need. The previous consultants never migrated fully, or maybe at all, to changes for the 2023-10 version. It is still setup and using the 2023-07 version and I have 3 weeks to get this changed over before the extension ends.

I could use any urgent help possible on understanding what this current API query is doing 100% and what I need to adjust to work with the major changes that appear to have come along with 2023-10.

I don’t believe this is 100% reflective of Monday’s documentation due to it being handle a little differently to load into PowerQuery. To start this conversation, here is 1 of the queries we have running. I would assume our focus will be on the Query element itself since everything has functioned until recently (before we did an extension for 2023-07).

The final line for "Data = " is when PowerQuery takes that result to start working further with it. I included because I don’t know if those fields being named will remain the same or what impact may occur to them.

Source = Web.Contents(
"https://api.monday.com/v2",
[
Headers=[
#"Method"="POST",
#"API-Version"="2023-10",
#"Content-Type"="application/json",
#"Authorization"="Bearer xxxxxxxxx
],
Content=Text.ToBinary("{""query"": ""query { boards(ids: board_number_here) { items { name, updated_at, group { title }, columns: column_values { title, text } } } }""}")
]
),
Data = Table.FromList(Json.Document(Source)[data][boards]{0}[items], Record.FieldValues, {"Title", "UpdatedAt", "Group", "Columns"}),
2

Has anyone found a better way of paginating with the cursor in Power BI for 20+ pages without having to add code for each possible page and doing a union/append method?

I was able to get the existing query working, but my next concern is the pagination.
Given the changes in 2023-10 about pagination with the cursor, I’m wondering if it’s still accurate results to be using the current method of Page Numbers instead of the cursor.

The current method is building a table that gets an item count, then divides by 100, and produces a list (turned into a table) of how many pages there are, from 1 to 24 or whatever the max was when dividing the item count by 100. Then this main query is run as a function over that table and filling in the Page number as the function variable to run against.

I included more of the m-code prior and after the core API call, to properly reflect a few changes, as well as show any Variable referencing.

let
Source = (paging as text) as table=>
let
Key = #“AuthKey”,
Board = #“BoardNbr_Boards”,
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) { items { name, updated_at, group { title }, columns: column_values { column { title }, text } } } } }””}”)
]
),
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]) )
)),