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:
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:
Thank you for sharing your solution as well as the link of the video!
