Reading data from a webapi with multiple pages.

Hi tobny76 ,

To fetch all data from a paginated API in Power Query, you can use a combination of looping through pages dynamically and appending the results into a single table. Here's how you can do it:

 Steps:

1. Set up the base query:
- Start by creating a query for the API endpoint with the `page` parameter as a variable.
- Replace `XXXX` with your API details.

M

let
BaseUrl = "https://XXXX/XXXX&reportid=4&page=",
PerPage = "&perpage=500",
GetPage = (PageNum) =>
let
Url = BaseUrl & Number.ToText(PageNum) & PerPage,
Source = Json.Document(Web.Contents(Url))
in
Source
in
GetPage

2. Create a function to handle pagination:
- Define a function that fetches data for each page, checks if there's more data, and repeats until no more data exists.

M

let
BaseUrl = "https://XXXX/XXXX&reportid=4&page=",
PerPage = "&perpage=500",
GetAllPages = List.Generate(
() => [Page = 0, Data = Json.Document(Web.Contents(BaseUrl & "0" & PerPage))],
each List.NonNullCount([Data]) > 0,
each [
Page = [Page] + 1,
Data = Json.Document(Web.Contents(BaseUrl & Number.ToText([Page]) & PerPage))
],
each [Data]
),
AllData = Table.FromList(GetAllPages, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
AllData

3. Transform the results:
- The `AllData` table contains all the data fetched from the API. You can expand nested fields or transform the table as required in Power Query.

4. Test and Validate:
- Refresh the query to ensure it captures all pages correctly. If the API has a limit on pages or results, confirm this behavior to avoid excessive requests.

Notes:
- API Rate Limits: If the API has rate limits, ensure to add a delay between requests using `Function.InvokeAfter`.
- Error Handling: Add error handling for cases where the API might fail for specific pages.

This dynamic approach fetches all available data in a paginated format and ensures scalability for large datasets. Let me know if you encounter any specific issues!

Please mark this as solution if it helps. Appreciate Kudos.