Paginate Through an API with List.Generate in Power Query M

Repeatedly calls a paged REST API until it stops returning results, combining every page into one table.

Advanced M FunctionsAdvancedapipaginationlist.generateweb.contents

Power Query M code

let
    GetPage = (pageNumber as number) =>
        Json.Document(Web.Contents("https://api.example.com/v1/orders", [Query = [page = Text.From(pageNumber)]])),
    Pages = List.Generate(
        () => [Page = GetPage(1), PageNumber = 1],
        each List.Count(Record.Field(_[Page], "data")) > 0,
        each [Page = GetPage([PageNumber] + 1), PageNumber = [PageNumber] + 1]
    ),
    AllRecords = List.Combine(List.Transform(Pages, each Record.Field(_[Page], "data"))),
    ConvertedToTable = Table.FromRecords(AllRecords)
in
    ConvertedToTable

Notes

Replace the URL, query parameter name and the "data" field with your API's actual pagination scheme and response shape.

Requirements

A REST API that returns results in pages and stops (or returns an empty array) after the last page.

How to use this snippet

In Power BI Desktop, choose Transform data to open Power Query, then open the Advanced Editor on a query (or start from a blank query).
Paste the snippet and replace the placeholder names (tables, columns, file paths) with your own. Check the requirements above first.
Run it through the Power Query M Formatter to tidy the layout.

Related Power Query M snippets

More Power Query M resources

Browse all 145 snippets in the Power Query M Code Snippets, or search the library for something similar to Paginate Through an API with List.Generate.