Paginated results from an API in Power Query

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"