Extract All Pages and Records from an API

[markholland]

Hi,

I've been able to link Power BI Desktop to a Web API and can now see the following:

Each page has 100 records. If I click on list I'll only see the most recent 100 records. When I click on records it gives me 3 URLs - one for the first page, the second page and the last page, all with page numbers. I want to be able to extract all records from all pages. The total number of pages will continue to grow so that needs to be taken into consideration.

Can anyone tell me how to do this?

Thanks,

Mark

[v-shex-msft]

Hi @markholland,

Can you please export some sample data for test, it is hard to explain the detail operation steps without sample data.

Regards,

Xiaoxin Sheng

[markholland]

Hi @v-shex-msft

The data is a little sensative so I don't really want to share it but I don't think that's necessary anyway. I just need the Query Editor to extract all the pages in the API to show every record that's available.

I've added some images below to explain in more detail:

When I click on 'List' from the original image, it takes me to one record for each of the 100 listed on the page.

I can convert this to a table then extract all the pages as normal:

But there are 170+ pages in the API and I want to extract the records from each and for this to continually do this regardless of how many pages there are. I've seen a couple of examples of code that explain how to do this but to be honest I can't follow it.

Below I've given the current view of Advanced Editor that shows the JSON file going in to Query Editor:

let
    Source = Json.Document(Web.Contents("https://api.harvestapp.com/v2/time_entries?access_token=********&account_id=********"))
in
    Source

Any ideas on what I need to do?

Thanks,

Mark

[v-shex-msft]

Hi @markholland,

You can copy api url and open in web browser to copy/download json data, then you can share json file for analysis.

Notice: for sensitive data you can replace them with fake data and share the replaced sample.(please keep original structure)

Regards,
Xiaoxin Sheng