Power Query Pagination Example

power-query-pagination.m

let 
    BaseUrl         = "https://fake-odata-api.com/v1/Entities?",
    Token           = "F4K3-T0K3N-D0NT-U5E-L0L",
    EntitiesPerPage = 1000,
 
    GetJson = (Url) =>
        let Options = [Headers=[ #"Authorization" = "Bearer " & Token ]],
            RawData = Web.Contents(Url, Options),
            Json    = Json.Document(RawData)
        in  Json,
 
    GetEntityCount = () =>
        let Url   = BaseUrl & "$count=true&$top=0",
            Json  = GetJson(Url),
            Count = Json[#"@odata.count"]
        in  Count,
 
    GetPage = (Index) =>
        let Skip  = "$skip=" & Text.From(Index * EntitiesPerPage),
            Top   = "$top=" & Text.From(EntitiesPerPage),
            Url   = BaseUrl & Skip & "&" & Top,
            Json  = GetJson(Url),
            Value = Json[#"value"]
        in  Value,
 
    EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
    PageCount   = Number.RoundUp(EntityCount / EntitiesPerPage),
    PageIndices = { 0 .. PageCount - 1 },
    Pages       = List.Transform(PageIndices, each GetPage(_)),
    Entities    = List.Union(Pages),
    Table       = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    Table

Hi Mark,
This is great, thank you so much for posting this. Was wondering if you might have any advice for how I could adapt this for a particular use case.

The data I'm specifically trying to grab are returned as records from the API I'm calling. The JSON that gets return looks like this in Power BI

I've managed to factor in the count correctly, but the actual data I need to return are the time_entries records and I can't seem to get it to pull that list.

I've tried replacing Value = Json[#"value"] with Value = Json[time_entries] but get an error that it can't convert records to a list.

Any thoughts you might have would be greatly appreciated.

Dave

Thank you. This code helped me out a lot. Called a Jira API and looped through some records.

I keep getting the message "The import option matches no exports. Did you miss a module reference?". Anyone know what this might mean? I'm trying to use this on the Insightly API.

Please, I Need a Help,

How I can will create an pagination for a top and skip only? Because, don't return the others pages, and the request from Postman, don't show the pages numbers. The code return only 1000 register per page.

This is my code in Power BI:

(top as text) =>
let
request = Json.Document(
Web.Contents(#"URL - Tickets" &
"&$top=" & top,
[Headers=[#"Content-Type"="application/json"]
]
)
)
in request

Observation: I don't have experience in programation.

Best Resgards