Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

 

 

 

Need Help

4 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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.