Get Monday Board Data to PowerBI via API ALL ITEMS through cursor

monday Apps & Developers
, , , , , , , , ,
1

You should be able to use the code below in PowerBI to get all columns and all items from a board even past the 500 item limit.

note: when connecting to the API, use anonymous and be sure to use the advanced editor when you are adding your blank power query.

let

Key = "eyJhbGciOiJIUzI1NiJ9.eyJ0aWQiOjMzMDU2NjU1MywiYWFpIjoxMSwidWlkIjozMjIwODE0MSwiaWFkIjoiMjAyNC0wMy0wOFQxOTowNjo1Ni45OTdaIiwicGVyIjoibWU6d3JpdGUiLCJhY3RpZCI6NzgxNjIyNiwicmduIjoidXNlMSJ9.ClnbaoqfF_kWiBwUyX7KDOeyJ4j5GCQK8HWq70KP8so",  // Replace with your Monday API key 
Board = "6534102022",  // Replace with your board ID 
BaseUrl = "https://api.monday.com/v2",  

// Function to fetch items based on cursor
GetItems = (Cursor as nullable text) =>
let

    Query = if Cursor = null then
                "{""query"": ""query { boards(ids: " & Board & ") { items_page(limit: 500) { items { name, updated_at, group { title }, columns: column_values { column { title }, text, ...on DependencyValue { display_value }, ...on MirrorValue { display_value }, ...on BoardRelationValue { display_value } } } } } }""}"
            else
                "{""query"": ""query { next_items_page(cursor: """ & Cursor & """) { items { name, updated_at, group { title }, columns: column_values { column { title }, text, ...on DependencyValue { display_value }, ...on MirrorValue { display_value }, ...on BoardRelationValue { display_value } } } cursor } } }""}",

    // Sending request to the API
    Source = Web.Contents(BaseUrl, [
        Headers = [
            #"Method" = "POST",
            #"Content-Type" = "application/json",
            #"Authorization" = "Bearer " & Key,
            #"API-Version" = "2024-01"
        ],
        Content = Text.ToBinary(Query)
    ]),

    // Parsing the response
    Response = Json.Document(Source),
    Items = Response[data][boards]{0}[items_page][items],
    NewCursor = try Response[data][boards]{0}[items_page][cursor] otherwise null,

    // Returning items and cursor
    Result = [Items = Items, Cursor = NewCursor]
in

    Result,

// Initialize by getting the first 500 items
FirstBatch = GetItems(null),
InitialItems = FirstBatch[Items],
InitialCursor = FirstBatch[Cursor],

// Recursively fetching additional items if a cursor is present
GetAllItems = (cursor as nullable text, allItems as list) =>

let
    // Get the next batch of items
    NextBatch = GetItems(cursor),
    NewItems = NextBatch[Items],
    NewCursor = NextBatch[Cursor],

    // Combine the new items with the existing ones
    CombinedItems = List.Combine({allItems, NewItems}),

    // If there is a new cursor, recurse; otherwise, return the combined items
    AllItems = if NewCursor = null then CombinedItems else @GetAllItems(NewCursor, CombinedItems)
in
    AllItems,

// Start the process with the initial cursor and an empty list
FinalItems = GetAllItems(InitialCursor, InitialItems),

// Convert the final list of items into a table
Data = Table.FromList(FinalItems, Record.FieldValues, {"Title", "UpdatedAt", "Group", "Columns"}),

// Expanding columns using your original transformation logic
#"ExpandedColumns" = Table.FromRecords(Table.TransformRows(Data, each

    List.Accumulate([Columns], [
        Title = [Title],
        UpdateDate = [UpdatedAt],
        Group = [Group][title]
    ], (state, current) => Record.AddField(
        state, current[column][title],
        if Record.HasFields(current, "display_value") then current[display_value] ?? current[text] else current[text]

    ))

))

in
#“ExpandedColumns”