5.3 Many Pages: Pagination with a Parameter and a Custom Function

Steps in Power BI

1. Build a query for page 1 and clean it fully (for example a fictional partner site https://example.com/darkstores?page=1).
2. Home › Manage Parameters › New Parameter › Name PageNo › Type Decimal Number › Current Value 1.
3. Open Home › Advanced Editor and replace the hard-coded 1 in the URL with the parameter (see the code below).
4. Right-click the query in the Queries pane › Create Function… › name it fnGetStoresPage. Power Query links the function to the query, so editing the query updates the function.
5. Home › New Source › Blank Query and type = {1..5} (a list of pages) › To Table (List Tools › Transform) › rename the column to Page › set type to Whole Number.
6. Add Column › Invoke Custom Function › Function query fnGetStoresPage › PageNo = column Page › OK.
7. Click the expand icon on the new column to expand all columns. You now have all pages in one table.

// fnGetStoresPage
(PageNo as number) as table =>
let
    Source = Web.Contents("https://example.com",
                [RelativePath = "darkstores", Query = [page = Text.From(PageNo)]]),
    Page   = Html.Table(Text.FromBinary(Source), {{"Store", ".store-name"}, {"City", ".store-city"}},
                [RowSelector = ".store-card"])
in
    Page