Using Power BI to retrieve boards using Cursor-based pagination

Is there any progress on this matter? Individuals at my company are consistently adding to their boards. If a board expands beyond 500/1000/1500 records, I want my API query to automatically recognize this so that all the data can be imported into Power BI. Unlike the old API, which didn’t pose such challenges, the new one is proving to be a bit cumbersome. I’m currently grappling with finding a solution to make the new API function in the same manner as the old one did.

Hello there @SMac,

I do not have experience with Power BI, but API wise, adding something like this adds the groups data:

{
  boards(ids: 1234567890) {
    groups {
      title
      id
    }
    items_page(limit: 100) {
      cursor
      items {
        id
        name
        column_values {
          text
          column {
            title
          }
        }
      }
    }
  }
}

Hi @Matias.Monday. Unfortunately, when I added the group to the query, the data returned couldn’t be transformed into a set of rows with the group as a new column. What I tested and found worked well was to duplicate the Get_1stPage and Get_RemainingPages. I called them Get_1stPageGroup and Get_RemainingPagesGroup. Then I modified the query to look like this → MondayQuery3 = “) {items_page(limit: 100) {cursor items {id group {title} }}}}”“}”,

Also, duplicate the Monday board query so this one calls the Get_1StPageGroup and Get_RemainingPagesGroup queries.

This will create a table with your board columns and another table with your groups for each record. Then in Power Query you can select Merge Queries (I chose to Merge as new) to merge the two tables into a single table. You can join the two tables using the ID column for the data. This will give you a nice clean table with group vales.

This API doesn’t always return data in a way that is easily digested in Power BI. The method above will work but adds complexity.

I am glad you found a workaround @hbouk!

1 Like

@hbouk I followed the steps you provided in your Word document, however, I get this error. Any idea how to fix it?

To elaborate, the steps for the first page work fine, however, upon reaching step ‘2nd_Page_Table_Updated’ I get the aforementioned error. Thanks!

1 Like

exclude_reply_from_sla

@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][boards]{0}[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[title], 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.

2 Likes

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.

1 Like

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=[
#“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 = JSON[data],
boards = data[boards],
boards1 = List.First(boards),
columnsList = boards1[columns],
items_page = boards1[items_page],
cursor = items_page[cursor],
items = items_page[items],

// 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”,
[
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 = data3[next_items_page],
nextCursor = boards3[cursor],
items = boards3[items],

// 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

2 Likes

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. :wink:

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.

1 Like

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]) )
    )),