Paginated results from an API in Power Query

[tigertedd]

A friendly developer has helped me create an API for a system he's built for me, so I can export the data into Power BI.

I've managed to import it, but  I'm running into a problem that the bigger data sets result in a line in the JsonResponse applied step that ends with some code: ?top=200&skip=200.

I've learned that this is to do with pagination, and that it's important to efficiently retrieve the data. But my tables are all limited to 200 rows.

How do I get the rest oft he data?

This is the code in the advanced editor that I'm currently using (this was generated with the help of ChatGPT, I'm quite skilled with the copy and paste buttons, but don't really know what I'm looking at):

let
url = "https://finder.bloodsandbeyond.co.uk/myurl",
headers = [
#"x-api-token-name" = "x-api-token", // Add this line if it is required
#"x-api-token" = "????????????????????????????" // Replace with your actual API key
],
source = Web.Contents(url, [Headers=headers]),
jsonResponse = Json.Document(source),
value = jsonResponse[value],
#"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "lab_id", "assigned_user_id", "lab_invoice_id", "user_invoice_id", "appointment_at", "matched_at", "sample_posted_at", "is_under_18", "is_urgent", "patient_fee", "patient_cost", "collections_fee", "collections_cost", "reference", "status", "status_reason", "name", "address_line_1", "address_line_2", "city", "post_code", "lat", "lng", "phone", "email", "patients", "preferred_datetimes", "notes", "admin_notes", "deleted_at", "created_at", "updated_at", "confirmed_at", "is_issue", "invoice_notes", "issue_type"}, {"id", "lab_id", "assigned_user_id", "lab_invoice_id", "user_invoice_id", "appointment_at", "matched_at", "sample_posted_at", "is_under_18", "is_urgent", "patient_fee", "patient_cost", "collections_fee", "collections_cost", "reference", "status", "status_reason", "name", "address_line_1", "address_line_2", "city", "post_code", "lat", "lng", "phone", "email", "patients", "preferred_datetimes", "notes", "admin_notes", "deleted_at", "created_at", "updated_at", "confirmed_at", "is_issue", "invoice_notes", "issue_type"})
in
#"Expanded Column1"

Chat GPT has come up with a few other ideas, but at best it just ends up resulting in 200 rows again:

let
    // Initial URL
    initialUrl = "https://finder.bloodsandbeyond.co.uk/myURL",

    // Function to fetch each page of data
    GetAppointments = (url as text) as table =>
    let
        // Fetch the data from the API
        source = Json.Document(Web.Contents(url, [Headers = [#"x-api-token" = "?????????????????????????"]])),
        // Extract the value part of the response
        value = source[value],
        // Check if there's a next link for pagination
        nextLink = try source[#"@odata.nextLink"] otherwise null,
        // Convert the result to a table
        result = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        // Expand the columns in the result
        expanded = Table.ExpandRecordColumn(result, "Column1", {"id", "lab_id", "assigned_user_id", "lab_invoice_id", "user_invoice_id", "appointment_at", "matched_at", "sample_posted_at", "is_under_18", "is_urgent", "patient_fee", "patient_cost", "collections_fee", "collections_cost", "reference", "status", "status_reason", "name", "address_line_1", "address_line_2", "city", "post_code", "lat", "lng", "phone", "email", "patients", "preferred_datetimes", "notes", "admin_notes", "deleted_at", "created_at", "updated_at", "confirmed_at", "is_issue", "invoice_notes", "issue_type"}),
        // If there's more data, recursively fetch the next page
        finalResult = if nextLink <> null then
            Table.Combine({expanded, @GetAppointments(nextLink)})
        else
            expanded
    in
        finalResult,

    // Call the function to get all the data automatically
    allData = GetAppointments(initialUrl)
in
    allData

Any help will be greatly appreciated.

[lbendlin]

Does your friendly developer have any kind of documentation for the API they created?  How is the API indicating that the last of the pages is reached?

[tigertedd]

No specific documentation. But his response to the question is: "Just the standard OData way, the next link would end up null/empty."

[lbendlin]

Aha! So the call response includes the data and the pointer? There's a convenience function for that. Search for "Power BI API paging".

[tigertedd]

Are you referrring to: Table.GenerateByPage?

I've tried using this, but power query says the expression is unrecognised.

I looked at this page: Helper functions for M extensions for Power Query connectors - Power Query | Microsoft Learn - which says at the top that it's not part of the laibrary yet and needs to be copied.

I managed to get as far as createing this code, which resulted in a row 201 appearing, but with an error, which was progress of a sort: 

let
    // Define the Table.GenerateByPage function for pagination
    Table.GenerateByPage = (getNextPage as function) as table =>
    let        
        listOfPages = List.Generate(
            () => getNextPage(null),            // Get the first page of data
            (lastPage) => lastPage <> null,     // Stop when the function returns null
            (lastPage) => getNextPage(lastPage) // Pass the previous page to the next function call
        ),
        // Concatenate the pages together
        tableOfPages = Table.FromList(listOfPages, Splitter.SplitByNothing(), {"Column1"}),
        firstRow = tableOfPages{0}?
    in
        // If we didn't get back any pages of data, return an empty table
        // Otherwise set the table type based on the columns of the first page
        if (firstRow = null) then
            Table.FromRows({})
        else if (Table.IsEmpty(firstRow[Column1])) then
            firstRow[Column1]
        else
            Value.ReplaceType(
                Table.ExpandTableColumn(tableOfPages, "Column1", Table.ColumnNames(firstRow[Column1])),
                Value.Type(firstRow[Column1])
            ),

    // Define the getNextPage function for your API
    getNextPage = (lastPage as nullable any) =>
    let
        // Construct the API URL with pagination logic
        url = "https://finder.bloodsandbeyond.co.uk/myURL" & 
              (if lastPage = null then "" else "?$skip=" & Number.ToText(lastPage)),
        headers = [
            #"x-api-token" = "?????????????????"
        ],
        source = Web.Contents(url, [Headers=headers]),
        jsonResponse = Json.Document(source),
        value = jsonResponse[value],
        // Check if the value is null or empty
        nextPage = if value = null or List.IsEmpty(value) then null else value
    in
        // Return the result as a table or null
        if nextPage = null then null else Table.FromList(nextPage, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    // Use Table.GenerateByPage to get all data
    allData = Table.GenerateByPage(getNextPage)
in
    allData

When I expand the error, it was:
Expression.Error: We cannot convert a value of type Table to type Number.
Details:
Value=[Table]
Type=[Type]#

I'm guessing some part of this code tries to covert a table to a number, but I don't know enough about it to decipher it. Chat GPT disappeared up it's own tail trying to work out a fix. I'm optimistically hoping that someone smart enough can see the code and it's a simple fix. I feel like I'm really close.

[lbendlin]

As you can probably appreciate it is nearly impossible to help with API queries without access to said API  (which you may not be willing to provide for understandable reasons)

[tigertedd]

I got there in the end. Well, me and Chat GPT with a bit of team work. In the end the code needed to be modified to have some error handling. Just in case this is useful for anyone else.

    let
    // Define the Table.GenerateByPage function for pagination
    Table.GenerateByPage = (getNextPage as function) as table =>
    let        
        listOfPages = List.Generate(
            () => getNextPage(0),            // Start with the first page (skip=0)
            (lastPage) => lastPage <> null,  // Stop when null is returned (no more data)
            (lastPage) => getNextPage(Table.RowCount(lastPage)) // Pass the row count as the next skip value
        ),
        // Concatenate the pages together
        tableOfPages = Table.FromList(listOfPages, Splitter.SplitByNothing(), {"Column1"}),
        firstRow = tableOfPages{0}?
    in
        // If no pages are returned, return an empty table
        if (firstRow = null) then
            Table.FromRows({})
        else if (Table.IsEmpty(firstRow[Column1])) then
            firstRow[Column1]
        else
            Value.ReplaceType(
                Table.ExpandTableColumn(tableOfPages, "Column1", Table.ColumnNames(firstRow[Column1])),
                Value.Type(firstRow[Column1])
            ),

    // Define the getNextPage function with enhanced error handling
    getNextPage = (skipCount as number) =>
    let
        // Construct the API URL with pagination logic
        url = "https://finder.bloodsandbeyond.co.uk/myURL?$skip=" & Number.ToText(skipCount),
        headers = [
            #"x-api-token" = "?????????????????????????"
        ],
        source = try Web.Contents(url, [Headers=headers]) otherwise null,
        // Convert the response to text and parse it as JSON
        rawResponse = if source = null then error "No data from API!" else Text.FromBinary(source),
        jsonResponse = try Json.Document(rawResponse) otherwise null,
        // Check if the "value" field exists and is valid
        value = if jsonResponse = null or not Record.HasFields(jsonResponse, "value") then null else jsonResponse[value],
        // If value is null or empty, stop fetching further pages
        nextPage = if value = null or List.IsEmpty(value) then null else value
    in
        // Return the result as a table or null
        if nextPage = null then null else Table.FromList(nextPage, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    // Use Table.GenerateByPage to get all data
    allData = Table.GenerateByPage(getNextPage)
in
    allData

[tigertedd]

What is particuallry annoying is that there is an option to get data > odata feed. You just pop in the url, and it should be that easy. Then you have to authenticate, and there is a section for Web Api. All very easy, no code required. But then you get an error message saying you can't do that without a Web API key name provided. 

I've googled this error, and the only way to fix it is with code. Making that whole interface unusable. That's how I've ended up with this custom code, so now I can't use any of the interface based fixes that are abundant on the web, and the only option I've got is to find the right code.

If I could fix the initial API key name issue, and do everything through the inteface, then mabybe I wouldn't be having this issue at all.

[tigertedd]

That owuld be a pretty big data protection no no, to be fair. I was hoping there might be some obvious bit of code that is converting it to a number when it shouldn't be that might magically fix it.

[tigertedd]

Forget that. Turned out that was just repeating the same 200 rows over and over again. 😞

Back to the drawing board, but we came up with this result instead:

let
// Function to get all pages
GetAllPages = () =>
let
// Function to get the next page
GetNextPage = (skipCount as number) =>
let
url = "https://finder.bloodsandbeyond.co.uk/odata/myURL?$skip=" & Number.ToText(skipCount),
headers = [
#"x-api-token" = "????????????????"
],
source = try Web.Contents(url, [Headers=headers]) otherwise null,
// Convert response to JSON
jsonResponse = if source = null then error "No data from API!" else try Json.Document(source) otherwise null,
// Check if jsonResponse is valid
value = if jsonResponse <> null and Record.HasFields(jsonResponse, "value") then jsonResponse[value] else null,

// Debugging: Log the URL and the number of records fetched
_ = if value <> null then
let
recordCount = List.Count(value),
debugMessage = "Fetched " & Number.ToText(recordCount) & " records from: " & url
in
// Uncomment the next line to display debug messages in the console
// Debug.WriteLine(debugMessage)
null
else
null

in
// Return the records as a table or null if no records
if value = null or List.IsEmpty(value) then null else Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

// Initialize variables
pages = {}, // List to hold pages
currentPage = GetNextPage(0), // Start with the first page
skipCount = 0, // Initialize skip count

// Loop to fetch pages until there are no more records
Result = List.Generate(
() => [Page = currentPage, Skip = skipCount], // Start with the first page
each [Page] <> null, // Continue while there's a valid page
each [Page = GetNextPage([Skip] + 200), Skip = [Skip] + 200], // Get the next page and update skip count
each [Page] // Return the current page
),

// Combine all pages into a single table
allData = Table.Combine(List.RemoveNulls(Result))
in
allData,

// Call the function to get all the data
Result = GetAllPages(),
#"Expanded Column1" = Table.ExpandRecordColumn(Result, "Column1", {"id", "invoice_id", "source", "state", "transaction_id", "amount", "created_at", "updated_at", "deleted_at"}, {"id", "invoice_id", "source", "state", "transaction_id", "amount", "created_at", "updated_at", "deleted_at"})
in
#"Expanded Column1"

[tigertedd]

His response is: "Just the standard OData way, the next link would end up null/empty."