Forum Discussion
API data Source connection - how to get all pages from URL request
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=%20%20"&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:
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:
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=%20%20"&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: