Forum Discussion
Help with recursive web power query (oDATA, SuccessFactors)
Here is an example on how to do this with List.Generate using a test REST API. I adapted the approach described well in this article. You can paste the code into a blank query to step through it.
List.Generate() and Looping in PowerQuery - Exceed
let fn = (pagenumber) => Json.Document(Web.Contents("https://api.instantwebtools.net/v1/passenger?page=" & pagenumber & "&size=1000")), mylist = List.Generate(()=> [Page = 1, Result = fn(Number.ToText(1))], each List.Count([Result][data])>0, each [Page = [Page] + 1, Result = fn(Number.ToText([Page]+1))]), #"Converted to Table" = Table.FromList(mylist, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Page", "Result"}, {"Page", "Result"}), #"Expanded Result" = Table.ExpandRecordColumn(#"Expanded Column1", "Result", {"data"}, {"data"}), #"Expanded data" = Table.ExpandListColumn(#"Expanded Result", "data"), #"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"_id", "name", "trips", "airline", "__v"}, {"_id", "name", "trips", "airline", "__v"}) in #"Expanded data1"Pat
Hello Pat, thank you very much for your help!
You inspired me and I was able to contruct my own solution. It seems very easy at the first sight and also works great for my scenario:
This returns a list of lists of records that I can easily expand and work with.
It works great, loads data - but maybe looks too simple when compared with other examples of recursive functions I have been going through.
Could you please have a look at my code if you have any possible concerns about it, because I can't believe this short code actually does tone of work.
Thanks, Jakub!
Hello Pat, thank you very much for your help!
You inspired me and I was able to contruct my own solution. It seems very easy at the first sight and also works great for my scenario:
This returns a list of lists of records that I can easily expand and work with.
It works great, loads data - but maybe looks too simple when compared with other examples of recursive functions I have been going through.
Could you please have a look at my code if you have any possible concerns about it, because I can't believe this short code actually does tone of work.
Thanks, Jakub!
hi, can you post the advanced editor for this code. I try to replicate the code but it remains errors
Hello,
since then I improved it, this is the function that I use:
let
Source = (SFurl as text) => let
Source = List.Generate(()=>Record.AddField(Json.Document(Web.Contents(SFurl))[d],"count",1) , each Record.HasFields(_,"results") =true and (Record.HasFields(_,"count") and _[count]<=99999), each try Record.AddField(Json.Document(Web.Contents(_[__next]))[d],"count",_[count]+1) otherwise [df=[__next="SSS"]] ),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"results"}, {"results"}),
#"Expanded results" = Table.ExpandListColumn(#"Expanded Column1", "results")
in
#"Expanded results"
in
SourceIt will return a table/list of records, that you can further expand as you need.
(it has been a while I used it for the last time, so I cannot explain it, but it works for me - pull oData from SuccessFactors)
the parameter should look like this:
"https://XXXXX/odata/v2/Position?$format=JSON..."
Warm regards,
Jakub
great thanks, i managed to get it to work in Power BI Desktop, but when I publish to power bi serivice it says that the dataset cannot be updated because there is a dynamic datasource as the source is contained in the query. how did you solve it?
here is the error:
Hello,
I did not use it for scheduled online refresh - I was getting the same message.
So I used it manually on desktop
Jakub