cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
elassalle Frequent Visitor
Frequent Visitor

Creating a loop for fetching paginated data from a REST API

Dear all,

 

I am currently confronted with a pagination issue when getting data from a REST API.

 

When querying the REST API in basic manner only the first 100 items are returned.

The issue is that the table contains 197 items and I would like to get them all of course.

 

The Power Query created by default in the first "Source" step is:

= Json.Document(Web.Contents("https://mysite.com/api/targettable?id=X"))

It returns the following:

data List

total 197

page_size 100

skip 0

 

I made some tests and if I want to get the next 97 items I need to call the API again: https://mysite.com/api/targettable?parameter1=X&skip=100  

 

As the number of items will constantly grow, I would like to implement a loop in order to get all the time all items from the table.

 

I'm a newbie in Power Query / DAX language so kindly ask your support & suggestions on how to implement this loop. 

 

Thanks a lot

7 REPLIES 7
Moderator v-yuezhe-msft
Moderator

Re: Creating a loop for fetching paginated data from a REST API

@elassalle

Please review the following blog about how to implement REST API pagination in Power Query.

https://medium.com/@marktiedemann/how-to-do-pagination-in-power-query-430460c17c78

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
elassalle Frequent Visitor
Frequent Visitor

Re: Creating a loop for fetching paginated data from a REST API

Dear Lydia,

 

Thanks a lot for sending me this useful link.

 

I've used it and adapted it with a colleague and I'm now able to import all row items from the REST API.

 

The issue I'm now confronted with is that eventhough the report displays right through the Power BI Desktop and also the Power BI Service, I get an error message in the Power BI Service dataset section saying: "You can't schedule refresh for this dataset because one or more sources currently don't support refresh." Which is an issue as this report needs indeed to be refreshed on a regular base.

 

Are you aware whether some instructions from this script could create an issue for refreshing data?

Also, would you advise me to create a new support ticket or do you think that the community members can help

for such script troubleshooting?

 

Original script:

let
    Source = Json.Document(Web.Contents("https://mysite.com/api/v1/scopes?subproject_id=12")),
    data = Source[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "scope_status_id", "request_id"}, {"id", "scope_status_id", "request_id"})
in
    #"Expanded Column1"

 

Updated script:

let
    Source = Json.Document(Web.Contents("https://mysite.com/api/v1/scopes?subproject_id=12")),
    BaseUrl = "https://mysite.com/api/v1/scopes?subproject_id=12",
    EntitiesPerPage = 100,
 
    GetJson = (Url) =>
        let RawData = Web.Contents(Url),
            Json = Json.Document(RawData)
        in  Json,
    
    GetTotalEntities = () =>
        let Json = GetJson(BaseUrl),
            Total = Json[total]
        in  Total,

    GetPage = (Index) =>
        let Skip  = "skip=" & Text.From(Index * EntitiesPerPage),
            Url   = BaseUrl & "&" & Skip,
            Json  = GetJson(Url),
            Value = Json[data]
        in  Value,

    GetUrl = (Index) =>
        let Skip  = "skip=" & Text.From(Index * EntitiesPerPage),
            Url   = BaseUrl & "&" & Skip
        in  Url,
  
    EntityCount = List.Max({ EntitiesPerPage, GetTotalEntities() }),
    PageCount   = Number.RoundUp(EntityCount / EntitiesPerPage),
    PageIndices = { 0 .. PageCount - 1 },
   
 URLs  = List.Transform(PageIndices, each GetUrl(_)),
 Pages       = List.Transform(PageIndices, each GetPage(_)),
    Entities    = List.Union(Pages),
    #"Converted to Table" = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "scope_status_id", "request_id}, {"id", "scope_status_id", "request_id"})
in
    #"Expanded Column1"

 

Moderator v-yuezhe-msft
Moderator

Re: Creating a loop for fetching paginated data from a REST API

@elassalle,

When using function to obtain data from  the source, refresh in Power BI Service is not supported. You can vote the following idea that make functions refreshable in Service.

https://ideas.powerbi.com/forums/265200-power-bi/suggestions/9312540-make-functions-refreshable-when...

Currently, you would need to manually publish PBIX file after updating data in Power BI Desktop.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
elassalle Frequent Visitor
Frequent Visitor

Re: Creating a loop for fetching paginated data from a REST API

Thank you for this feedback Lydia.

I have just voted for that idea insisting on the urgent aspect of having this implemented in the Power BI service (I can not afford to have all source tables limited to 100 items or to be forced to perform manual PBI Desktop updates for all the reports based on this data !).

Still, looking into the history of the idea I see that other users are also in urgent need of this feature to be developed since months.

And I am not able to see whether the development work has started and whether it will be implemented sometime soon.

Is there any other way to perform the REST API paginated query through the Power BI service in a way that works?

Thanks a lot for your comprehension and efficient support,

Emmanuel

Moderator v-yuezhe-msft
Moderator

Re: Creating a loop for fetching paginated data from a REST API

@elassalle,

The workaround that I can think of is to use Power Update to automatically refresh and publish your PBIX file to Power BI Service.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
elassalle Frequent Visitor
Frequent Visitor

Re: Creating a loop for fetching paginated data from a REST API

Thank you for the hint Lydia,

 

I have tested Power Update FREE and it does the trick.

Eventhough it has its down sides as it requires the Reports to be opened and running permanently through Power BI Desktop on one PC/server.

 

I can't wait to have this features supported in a Power BI Service update...

 

Besides, reading into the latest Power BI desktop updates (preview features) I saw that it is now possible to buildup custom data connectors. 

Do we have the perspective through that custom data connector approach to query the REST API with pagination and still be supported once published to the Power BI Service?

 

Regards,

Emmanuel

Moderator v-yuezhe-msft
Moderator

Re: Creating a loop for fetching paginated data from a REST API

@elassalle,

I am afraid that custom data connectors created in Power BI Desktop are  not supported in Power BI Service.

However, you can directly create custom connectors for Power BI Service, there is a similar thread for your reference.

https://community.powerbi.com/t5/Integrations-with-Files-and/Custom-Data-Connectors-on-powerbi-com/t...



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.