Reading data from a webapi with multiple pages.

[tobny76]

I have a question about how I can solve this.
I need to fetch all data from an api with multiple pages in powerquery in Power BI.

https://XXXX/XXXX&reportid=4&page=0&perpage=500

I need to increase the page# and fetch all pages.

Regards
Tobias

[tobny76]

Thanks for your help. I have now solved it.

[saud968]

Here's a step-by-step guide to help you achieve this:

Create a function to fetch data from a single page:
Go to the "Home" tab in Power BI Desktop.
Click on "Advanced Editor" and create a new query.
let
FetchPage = (PageNumber as number) =>
let
Source = Json.Document(Web.Contents("https://XXXX/XXXX&reportid=4&page=" & Number.ToText(PageNumber) & "&perpage=500"))
in
Source
in
FetchPage
Create a list of page numbers:
Determine the total number of pages you need to fetch. For example, if you have 10 pages, create a list from 0 to 9.
let
PageNumbers = {0..9}
in
PageNumbers
Fetch data from all pages and combine them:
Use the List.Transform function to apply the FetchPage function to each page number and combine the results using Table.Combine.
let
PageNumbers = {0..9},
FetchPage = (PageNumber as number) =>
let
Source = Json.Document(Web.Contents("https://XXXX/XXXX&reportid=4&page=" & Number.ToText(PageNumber) & "&perpage=500"))
in
Source,
AllPages = List.Transform(PageNumbers, each FetchPage(_)),
CombinedData = Table.Combine(AllPages)
in
CombinedData
Load the combined data into Power BI:
After creating the query, load the data into Power BI by clicking "Close & Load".
This approach will help you fetch and combine data from multiple pages of the API. If the number of pages is dynamic, you might need to adjust the logic to determine the total number of pages programmatically.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[FarhanJeelani · Accepted solution]

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.

[tobny76]

Thanks for quick answer.
I'm trying to get this to work.
My base query is working.

let
  BaseUrl = P_BaseUrl, 
  PerPage = "&perpage="&P_PerPage, 
  GetPage = (PageNum) =>
    let
      Url    = BaseUrl & Number.ToText(PageNum) & PerPage, 

      Source = Xml.Tables(Web.Contents(Url, [Timeout=#duration(0,0,30,0)])),
      Table0 = Source{0}[Table]
    in
      Table0
in
  GetPage

But I don't get the second step to work when not using Json.document.
As you see in my base query I'm using Xml.Tables.
Do you have an example how to solve the function for pagination then?