Power Community

Power Community

Paginated REST API calls in Power BI

Last week I was asked about handling paginated REST API calls in Power BI. The REST API end point would only return 200 records while a lot more records should be returned.

Example Paginated REST API calls

In this case we were using the signinapp found at signinapp.com and looking at the API documentation we could find records at the following end point

https://backend.signinapp.com/client-api/v1/sites/47892/history?date_from=2023-11-23&date_to=2023-11-25

Using the above end point we will get records back between the set dates.

If we take this a step further we can get a page with a maximum of 200 records.

https://backend.signinapp.com/client-api/v1/sites/47892/history?date_from=2023-11-23&date_to=2023-11-25&page=1

If we want to get the 2nd page back we simply replace the page=1 with page=2. We can continue to collect all the pages as we need more pages.

Collect a page using Power BI

Within Power BI we can now get the first page returned using the following code:

= Json.Document(Web.Contents("https://backend.signinapp.com/client-api/v1/sites/47892/history?date_from=2023-11-23&date_to=2023-11-25&page=1"))
Paginated REST API calls in Power BI Microsoft Power BI image 30

Once we have done a bit of reshaping and formatting we end up with he following Query:

let
    Source = Json.Document(Web.Contents("https://backend.signinapp.com/client-api/v1/sites/47892/history?date_from=2023-11-23&date_to=2023-11-25&page=1")),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded data" = Table.ExpandListColumn(#"Converted to Table", "data"),
    #"Expanded links" = Table.ExpandRecordColumn(#"Expanded data", "links", {"first", "last", "prev", "next"}, {"links.first", "links.last", "links.prev", "links.next"}),
    #"Expanded meta" = Table.ExpandRecordColumn(#"Expanded links", "meta", {"current_page", "from", "path", "per_page", "to"}, {"meta.current_page", "meta.from", "meta.path", "meta.per_page", "meta.to"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded meta",{{"data", type any}, {"links.first", type text}, {"links.last", type any}, {"links.prev", type any}, {"links.next", type any}, {"meta.current_page", Int64.Type}, {"meta.from", type any}, {"meta.path", type text}, {"meta.per_page", Int64.Type}, {"meta.to", type any}})
in
    #"Changed Type"

And we have the first page of our data ready.

Paginated REST API calls in Power BI Microsoft Power BI image 31

We could now of course create many queries, each covering a page but that is not what we want to do.

How do we create a single query returning us multiple pages?

Create a function in Power BI

All we have to do is add

= (page as text) =>

and then add page to the end of the end point that we are calling (replacing the 1). This should then result in something like this:

= (page as text) =>
let
    Source = Json.Document(Web.Contents("https://backend.signinapp.com/client-api/v1/sites/47892/history?date_from=2023-11-23&date_to=2023-11-25&page=" & page)),
    #"Converted to Table" = ...,
    #"Expanded data" = ...,
    #"Expanded links" = ...,
    #"Expanded meta" = ...,
    #"Changed Type" = ...
in
    #"Changed Type"
Power BI calling paginated REST API Calls

Now we can test this function and enter the page number that we want to receive

So, the next question. How can we run this function for each page that is available. In our case there isn’t a way to get the maximum number of pages available.

Creating a PageQuery

In my case I want to get up to 10 pages returned. So I’m going to create a List with 10 rows in it.

Paginated REST API calls in Power BI Microsoft Power BI image 34

This list I will convert to a table either by using the Power BI UI …

Paginated REST API calls in Power BI Microsoft Power BI image 35

… or I use the by m me preferred code editor. Also don’t forget to set the format of the Column containing the page numbers to Text.

let
    PagesList = {1..10},
    #"Converted to Table" = Table.FromList(PagesList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}})
in
    #"Changed Type"

Invoking our function

We now have our helper table ready. And we can now invoke our function.

Paginated REST API calls in Power BI Microsoft Power BI image 36

And then we just select the Column containing the page numbers and we will get 10 tables back.

Paginated REST API calls in Power BI Microsoft Power BI image 37

In the below result just click on the expanding icon in the top right.

Paginated REST API calls in Power BI Microsoft Power BI image 38

And now you can start using the table within Power BI.

This post was originally published on this site

- Advertisement -spot_img

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisement - Advertisement

Latest News

Clone Dynamics 365 CRM Sales Order Records along with their Related Invoice Records!

Sales teams using Dynamics 365 CRM have to frequently create new sales orders and invoices based on existing ones....

More Articles Like This

- Advertisement -spot_img