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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors