Power BI and API paginated data

[Anonymous]

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

[Anonymous]

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?

[v-jingzhang]

Hi @Anonymous 

I remember Dataflows have been able to support custom functions. See if below links help.

https://docs.microsoft.com/power-query/dataflows/best-practices-developing-complex-dataflows 

https://community.powerbi.com/t5/Service/dataflow-and-looping-data-with-function/m-p/1310299 

Best regards,

Jing

[Anonymous]

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.

[v-jingzhang]

Hi @Anonymous 

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.