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

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”