Help with recursive web power query (oDATA, SuccessFactors)

[Syndicate_Admin]

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

[Syndicate_Admin]

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:

[Syndicate_Admin]

Hello,

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

So I used it manually on desktop

Jakub

[Syndicate_Admin]

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,

[Syndicate_Admin]

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:

let

  Path = "https://api2.successfactors.eu",

  Auth = 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) =>

    let

    Token = Authentication{0}[Value],

    baseurl = "https://api2.successfactors.eu/odata/v2/",

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

    let

    baseurl = "https://api2.successfactors.eu/odata/v2/",

    newReq=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 data

in

    check,

    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)

in

    expand