cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
markholland Regular Visitor
Regular Visitor

Extract All Pages and Records from an API

Hi,

 

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

 

01.PNG

 

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

3 REPLIES 3
Community Support Team
Community Support Team

Re: Extract All Pages and Records from an API

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
markholland Regular Visitor
Regular Visitor

Re: Extract All Pages and Records from an API

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:

 

01.PNG

 

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:

 

02.PNG

 

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

Community Support Team
Community Support Team

Re: Extract All Pages and Records from an API

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |