cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jdusek92
Helper III
Helper III

Help with recursive web power query (oDATA, SuccessFactors)

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:jdusek92_0-1622575476097.png

Step 2:jdusek92_3-1622575643719.png

 

Step 3:jdusek92_4-1622575693591.png

 

 

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

jdusek92_5-1622575873553.png

 

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

 

2 ACCEPTED SOLUTIONS
mahoneypat
Super User
Super User

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

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:

 

jdusek92_0-1622759931868.png

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!

View solution in original post

12 REPLIES 12
mahoneypat
Super User
Super User

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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:

 

jdusek92_0-1622759931868.png

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
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

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:

image.png

 

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,

Hello,

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

So I used it manually on desktop

 

Jakub

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

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

 

Warm regards, Jakub

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


mahoneypat
Super User
Super User

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

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

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors