Paginate in Power Query from an API JSON file

[all04f]

I have been researching and digging around trying to figure out how to get pagination to work on an API dataflow in JSON file format. All of the articles and videos I have seen all tell the user to do the same thing:

• Start from Power BI Desktop > Get data > Web page > enter the API URL

However, the way my company wants things to work is by pulling from a dataflow:

• app.powerbi.com > Workspaces > Dataflow > Add new tables > JSON file > and then enter in the API URL

All of the videos and blog posts that I see keep referencing "total pages". I don't have a page count on the API, just about 1,200 records. How do I get it to paginate so that when I pull it in to Power BI I have more than just 200 rows?

[all04f]

Thanks again, Pete. I am able to get the number of records where total_count=true like this:

But then I think I need to divide this totalCount by the number of results per page (which I am setting at 100). But I am not entirely sure how I do this 😞

[BA_Pete]

No problem.

Check out this recent thread where I explained it for someone else:

https://community.powerbi.com/t5/Power-Query/How-to-deal-with-paginated-API/m-p/3222229 

Don't forget to be generous with the kudoes! 😉

Pete

[BA_Pete]

Hi @all04f ,

I think the two methods you're describing here are essentially the same thing.

As far as I know, using the JSON File connector in Dataflows just sets you up with the appropriate connection strings, there's no special wizardry happening that doesn't happen using other methods.

To get a JSON File into a Dataflow via API, just connect and transform via Desktop then open Advanced Editor, copy all the M code, open a blank Dataflow query > Advanced Editor and paste all the M code into there.

The 'total pages' thing IS a problem. In order to create a paginated query, you pretty much have to have this information.

I suggest going to your API documentation and finding the endpoint for this information, as it will be there somewhere.

Pete

[all04f]

Thanks, Pete. I was able to get some sort of data flow going in Power BI Desktop, although the query just runs and runs since the Page thing is not there. The API documentation doesn't have the endpoint information for pages, so I will reach out to the vendor for assistance. 

Do you know if I can create this dataflow (advanced editor, blank query, M code, etc) in apps.powerbi.com? I attempted to do the exact same steps that in did in Desktop Power BI, but then I get an error that says  "Can't save dataflow. One or more tables references a dynamic data source."

[BA_Pete]

For the dynamic data source error, you need to use relative paths.

For example:

Json.Document(
    Web.Contents(
        "https://root.url.com/",
        [
            RelativePath="relative/path.api",
            Query=
            [
                api_key=apiKey
            ]
        ]
    )
)

For reference, although I'm sure you've already tried, the total records value will usually be at the full endpoint URL. Using the example above, it would be at:

https://root.url.com/relative/path.api

...and should give you a record something like this:

Pete