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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
StevenHarrison
Resolver I
Resolver I

API data Source connection - how to get all pages from URL request

Hi Guys - I have been given credentials to query an API connection to return data.

 

The URL they have given me brings back 100 items from the parameter at the end of the string &page=1, so therefore just the first page.

 

https://eu-app.rantandrave.com/RapideIntegration/feedback-details?from=2021-01-06%2000:00:00%20&to=%...

 

Can you advise if it's possible through Power BI desktop to increment the page number to request all the data, i.e. page 2, page 3 etc until all the data is returned?

 

Their API documentation is as below but their support desk does not know how to connect to Power BI

API documentation.JPG

Any thoughts/feedback very welcome.

1 ACCEPTED SOLUTION

Hi @v-jingzhang  - managed to solve it using the following function:

 

= (PageStart as number)=>
let
Source = Xml.Tables(Web.Contents("https://eu-app.rantandrave.com/RapideIntegration/feedback-details?from=2021-01-06%2000:00:00%20&to=%..."&TodayPageEnd&"%2000:00:00&page="&Number.ToText(PageStart))),
Table = Source{0}[Table],
#"Changed Type" = Table.TransformColumnTypes(Table,{{"message", type text}, {"sentiment-score", Int64.Type}, {"channel", type text}, {"Attribute:id", Int64.Type}})
in
#"Changed Type"

 

 

TodayPageEnd was simply today's date in the right format for the web page string - 

= Date.ToText(DateTime.Date(DateTime.LocalNow()), "yyyy-MM-dd")

 

and the page count so that no errors were returned:

= List.Generate(()=>
[Result = try fxRRDATASync(1) otherwise null, Page=1],
each [Result]<> null,
each [Result= try fxRRDATASync([Page]+1) otherwise null, Page=[Page]+1],
each [Result])

 

this was then converted to a table.

 

So with your help and the youtube video at: 

How to do Pagination without knowing the number of pages (Part 2) in Power Query | List.Generate - Y...

 

View solution in original post

5 REPLIES 5
v-jingzhang
Community Support
Community Support

Hi @StevenHarrison 

 

Please refer to this article: Scrape Data from Multiple Web Pages with Power Query • My Online Training Hub. It provides detailed steps to solving a similar task. Currently if you don't know how many total pages it should query, you may pass a great enough page parameter to the URL to have a test.  

 

Let me know if you have any questions when applying above solution.  

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Thanks @v-jingzhang this is brilliant, let me take a look at the solution and I will report back.

Thank you for the prompt reply, much appreciated.

Hi @v-jingzhang  - getting the following error:
R&R API error.png

 

Advanced editor text looks like this:

let
Source = {1..40000},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "fxRR-DATASync", each #"fxRR-DATASync"([Column1])),
#"Expanded fxRR-DATASync" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxRR-DATASync", {"channel", "structured-fields", "categories", "insights", "notes", "Attribute:id", "message", "sentiment-score"}, {"channel", "structured-fields", "categories", "insights", "notes", "Attribute:id", "message", "sentiment-score"})
in
#"Expanded fxRR-DATASync"

 

I have increased the upper value a few times. Any thoughts appreciated and thanks for your help.

Hi @v-jingzhang  - managed to solve it using the following function:

 

= (PageStart as number)=>
let
Source = Xml.Tables(Web.Contents("https://eu-app.rantandrave.com/RapideIntegration/feedback-details?from=2021-01-06%2000:00:00%20&to=%..."&TodayPageEnd&"%2000:00:00&page="&Number.ToText(PageStart))),
Table = Source{0}[Table],
#"Changed Type" = Table.TransformColumnTypes(Table,{{"message", type text}, {"sentiment-score", Int64.Type}, {"channel", type text}, {"Attribute:id", Int64.Type}})
in
#"Changed Type"

 

 

TodayPageEnd was simply today's date in the right format for the web page string - 

= Date.ToText(DateTime.Date(DateTime.LocalNow()), "yyyy-MM-dd")

 

and the page count so that no errors were returned:

= List.Generate(()=>
[Result = try fxRRDATASync(1) otherwise null, Page=1],
each [Result]<> null,
each [Result= try fxRRDATASync([Page]+1) otherwise null, Page=[Page]+1],
each [Result])

 

this was then converted to a table.

 

So with your help and the youtube video at: 

How to do Pagination without knowing the number of pages (Part 2) in Power Query | List.Generate - Y...

 

Thank you for sharing your solution as well as the link of the video!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.