When you use an API with a paging mechanism (like the example from this blogpost), you’ll might work with a field that contains the address for the next page. You can use this to walk through the available chunks until you reach the last element. That last element in the pagination will not contain a next-field or that field will be null.
Paging in Power Query
In Power Query you can use the function List.Generate for it. According the latest function documentation it:
Generates a list of values given four functions that generate the initial value initial, test against a condition condition, and if successful select the result and generate the next value next.
So an intuitive implementation would look like so:
Initial code for paging: Will miss the last element
In the initial step (row 2) the API will be called and returns this record:
Examining the result of the first call
So for the upcoming iterations (next in row 4), a reference to the field next will be made and this URL will be called.
In the condition (row 3) I say that this process shall be repeated until the next-field of my previous result ([Result]) is empty.
However, this will only return 14 list with 20 elements each, missing the last element with 13 items to retrieve the full 293 items.
Let’s check it out:
Last Element (13 rows) is missing
Solution
Honestly, I still find it difficult to understand, why this last element is missing. But fortunately there is an easy solution:
Split into 2 steps and reference previous URL instead
The trick lies in the adjusted condition (row 4): Instead of checking if there is a next-field in the previous record, I check if the previous record had a URL to call. That basically reaches 1 level further back and will deliver the full results.
Alternative
Actually, you can also use some “brute force” using a try – otherwise – statement like so:
Simple alternative
But this will not deliver any items for debugging if something in the calls goes wrong. So I prefer not to use try statements for looping or pagination.
Enjoy and stay queryious 😉
The post How not to miss the last page when paging with Power BI and Power Query appeared first on The BIccountant.