Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Power BI and API paginated data

I am trying to pull in all data from an API. Unfortunately, the API only allows 500 records per page. I followed the steps from the post here - How to get paginated data from API in Power BI. Instead of selecting the "total_pages" I selected "total_rows." I had this working, where my result was all 4675 rows. Something happened and now I get over 1 millions rows and the query is still pulling in rows. I have looked at this for so long and cannot see what is wrong. Can anyone help?


The parameters in the API are businessObjectTypeId, pageSize, and pageNum






Frequent Visitor

Thank you @v-jingzhang! This worked except when I uploaded to the service, other workspaces cannot use the dataflow because pulling in the function query is not an option. Is there another way to do this?

The error I am receiving is: "Can't save dataflow. One or more tables references a dynamic data source." After researching this error, and doing some testing, I have figured out it is the function PBI Service does not like. Everything loads correctly and works as it is supposed to in PBI Desktop. Once I add the (pageNum as text) =>, Power BI Service says "it is a type that cannot be loaded." Any help with this?




Source = Json.Document(Web.Contents("https://APIURL/APINAME/GetAllRecordsForBusinessObject?businessObjectTypeId=ID&pageSize=500&pageNum=" & pageNum,[Headers=[#"HIDDEN"=HIDDEN]]))




I believe the PBI Service does not like the part "...pageNum=" & pageNum,[...]" When I tried to use "RelativePath" I received an expression error: "Expression.Error 3 arguments were passed to a function which expects between 1 and 2."

Any ideas how to fix or get around this?


Also, everything works as it should in Desktop, just does not want to save in PBI Service.

Community Support
Community Support

Hi @Rhianna_11 


Try using this 

List = {1..Number.RoundUp(Source[totalRows]/500)},

to replace

List = {1..Source[totalRows]},

in TESTtotalRows query


Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Helpful resources


2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors