Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
markholland
Helper I
Helper I

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
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

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

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
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.