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!
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!
If it works and is performant, that's the real test. Does it make two web calls with each iteration?
Pat
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
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:
Picking up on this - as for our organization - and for security in general - we did not want to use basic authentication against SuccessFactors - I came up with an alternative to the marked solution here.
Firstly I created a seperate dataflow for the token authentication.For that part I use a service account and generate the assertion with the SAML generator supplied from SAP.
For the powerquery for this part it looks like below:
letAuth = Json.Document(Web.Contents(Path, [Headers=[#"Content-Type"="application/x-www-form-urlencoded"],Content=Text.ToBinary(#"insertion"), RelativePath="/oauth/token"])),#"Converted to table" = Record.ToTable(Auth),#"Transform columns" = Table.TransformColumnTypes(#"Converted to table", {{"Value", type text}}),#"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"Value", null}}),#"Transposed table" = Table.Transpose(#"Replace errors"),#"Promoted headers" = Table.PromoteHeaders(#"Transposed table", [PromoteAllScalars = true]),#"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"access_token", type text}, {"token_type", type text}, {"expires_in", Int64.Type}}),#"Added custom" = Table.AddColumn(#"Changed column type", "Token", each [token_type] & " " & [access_token]),Navigation = #"Added custom"[Token],#"Navigation 1" = Navigation{0},#"Convert to table" = Table.FromValue(#"Navigation 1")in#"Convert to table"The insertion part I referenced as a seperate variable.
2nd - for any new Odata sources I want to fetch into PowerBI I setup a seperate dataflow, where I reference the Authentication Token ( this is running 1 a day to get a new token)
For getting the odata - I have this function. For this to work, you should always use inlinecount=allpages in the query.(RelPath as text) =>letToken = Authentication{0}[Value],RelPath = RelPath,initReq = Json.Document(Web.Contents(baseurl, [Headers=[#"Accept"="application/json", Authorization=Token],RelativePath=RelPath]))[d],nextUrl = Text.AfterDelimiter(initReq[__next],"v2/"),initValue= initReq[results],gather=(data as list, url)=>letnewReq=Json.Document(Web.Contents(baseurl,[Headers=[#"Accept"="application/json", Authorization=Token],RelativePath=url]))[d],newNextUrl = Text.AfterDelimiter(newReq[__next],"v2/"),newData= newReq[results],data=List.Combine({data,newData}),Converttotable = Record.ToTable(newReq),Pivot_Columns = Table.Pivot(Converttotable, List.Distinct(Converttotable[Name]), "Name", "Value", List.Count),Column_Names=Table.ColumnNames(Pivot_Columns),Contains_Column=List.Contains(Column_Names,"__next"),check = if Contains_Column = true then @gather(data, newNextUrl) else dataincheck,Converttotable = Record.ToTable(initReq),Pivot_Columns = Table.Pivot(Converttotable, List.Distinct(Converttotable[Name]), "Name", "Value", List.Count),Column_Names=Table.ColumnNames(Pivot_Columns),Contains_Column=List.Contains(Column_Names,"__next"),outputList= if Contains_Column= true then @gather(initValue,nextUrl) else initValue,expand=Table.FromRecords(outputList)inexpand
