Help with recursive web power query (oDATA, SuccessFactors)

[jdusek92]

Hello,

I am connecting to oDATA SuccessFactors and I have encountered the following challenge:

I can get only 1000 rows in one JSON file (default limitation). With these 1000 records, I also get a URL with a skip token to get the second 1000, and so on up to the last thousand of records.

Step 1:

Step 2:

Step 3:

The last Skiptoken URL return only a List of records without "__Next" with Skiptoken URL:

I would like to ask for help with a recursive function - this function will take the original URL and iterate through the original URL and all subsequent URLs with SkipTokens.

The result should be a list of lists of records that I can easily work with in the next steps.

My previous attempt used List.Generate to firstly get the list of all SkipToken URLS. Then I opened all these URLS - this means that I had to download all the JSON files twice = twice the download time. Also, I am not sure if the SkipToken URLs are still reliable when loaded for the second time.

Thank you

Jakub

[mahoneypat · Accepted solution]

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

[mahoneypat · Accepted solution]

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

[jdusek92 · Accepted solution]

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!

[marcosimeone]

hi, can you post the advanced editor for this code. I try to replicate the code but it remains errors

[jdusek92]

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
Source

It 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

[marcosimeone]

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:

[afloresb1]

Hi,

How did you make it work? I keep getting, identifier errors, commas missing, right paren error....

I just copied and pasted changing "SFurl as text" for the odata api url maybe im doing something wrong.

Best regards,

[jdusek92]

Hello,

I did not use it for scheduled online refresh - I was getting the same message.

So I used it manually on desktop

Jakub

[mahoneypat]

If it works and is performant, that's the real test.  Does it make two web calls with each iteration?

Pat

[jdusek92]

How can I test that it does not perform all the calls twice?

Warm regards, Jakub

[mahoneypat]

You could probably tell with the query diagnostics features (or with an external tool like Fiddler); however, if it is performant and you don't have a limit on web calls, don't worry about it.

Pat

[mahoneypat]

Please see the approach in this video for a simpler way to handle this.

Power BI - Tales From The Front - REST APIs - YouTube

Pat

[jdusek92]

Hello,

unfortunately, this oData API does not return the total count - therefore I cannot generate all the URLs beforehand. I would really need a recursive function to loop through the Skiptokens.

Warm Regards, Jakub