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

[StevenHarrison]

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

Any thoughts/feedback very welcome.

[v-jingzhang]

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.

[StevenHarrison]

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.

[StevenHarrison]

Hi @v-jingzhang  - getting the following error:

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.

[StevenHarrison · 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...

[v-jingzhang]

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