PowerBI Solution for recursively loading data from Monday.com with cursor pagination for API v2024-01.
Is it possible to pass null values into PowerQuery functions? Not a practical problem but not an elegant solution since we can simply pass "new" instead. If you know how to do this, please let me know/merge in your changes.
Monday API version 2023-10 introduced pagination to items (limite 500), breaking simple 'pull all data' queries. Most of the current solutions today solve this problem by manually writing in query to pull the first 500, second 500, etc. This is problematic if your data is growing as it won't continue to add items as your data grows. This example/solution will dynamically pull all the data.
When getting data from Monday's API, it will return the data as well as a cursor which is the next subset of data. The function grabs data either starting with a fresh query or using a cursor. If there is no additional cursor, it will return the data it received. If there is a cursor, it will call itself again (with the new cursor) and append the returned data together. This continues forever until the end of data is found, your computer runs out of memory, or a recursion limit is hit (never seen it, even using tiny batch sizes).
- Create 'APIKey' function with your Monday API Key
- Other options: Write directly into function, query from database or other secure source, basic query (If as query, do not let it load or you could leak the key!)
- Create the function "Fn_RecursiveMondayItems"
- Name it whatever you want
-
Invoke the function using the parameters below
- Board = The BoardID you're getting items for, this is numeric.
- InitialCursor = "new" will indicate the start of data
-
Transform data, you'll probably want to pivot it - example is included in this repo