Forum Discussion

Dich123's avatar
Dich123
Frequent Visitor
2 years ago
Solved

Limit 1000 rows in powerquery from SuccessFactors API

Hello,

I am trying to pull the data from SF via the web connector using the API " https://api55.sapsf.eu/odata/v2/User?$format=JSON".
I am redirected directly to power query, however I have only 1000 rows.
The M code provided automatically is working fine (even if it is very long) how ever i want to modify it to loop through all the data and bing it to me.
Can you help please?
Note: the code will be modified because of the lenght.

 

 

 

let
    Source = Json.Document(Web.Contents(" https://api55.sapsf.eu/odata/v2/User?$format=JSON", [Headers=[Authorization="Basic QJJDGMMDHCGJKBJTlRAYWJkdWxsYWhhYjpTDE5ODk="]])),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded d" = Table.ExpandRecordColumn(#"Converted to Table", "d", {"results", "__next"}, {"d.results", "d.__next"}),
    #"Expanded d.results" = Table.ExpandListColumn(#"Expanded d", "d.results"),
    #"Expanded d.results1" = Table.ExpandRecordColumn(#"Expanded d.results", "d.results", {"__metadata", "userId", "country", "zipCode"}),
    #"Expanded d.results.manager" = Table.ExpandRecordColumn(#"Expanded d.results.benchStrengthNav.__deferred", "d.results.manager", {"__deferred"}, {"d.results.manager.__deferred"}),
    #"Expanded d.results.manager.__deferred" = Table.ExpandRecordColumn(#"Expanded d.results.manager", "d.results.manager.__deferred", {"uri"}, {"d.results.manager.__deferred.uri"}),


in
    #"Removed Columns1"
Need Help

3 Replies

    • Dich123's avatar
      Dich123
      Frequent Visitor
      2 years ago

      lbendlin thank you very much for your reply and for the advice.
      I came up with this solution but i still have just 1000 rows.
      Please note that i don't know the M code.

      let
    pageSize = 10000,
    pageNumber = 1,
    skipValue = (pageNumber - 1) * pageSize,
    url = https://api55.sapsf.eu/odata/v2/User?$format=JSON&$top= & Text.From(pageSize) & "&$skip=" & Text.From(skipValue),
    Source = Json.Document(Web.Contents(url, [Headers=[Authorization=" Basic QJJDGMMDHCGJKBJTlRAYWJkdWxsYWhhYjpTDE5ODk=
    // Your existing transformations...
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded d" = Table.ExpandRecordColumn(#"Converted to Table", "d", {"results", "__next"}, {"d.results", "d.__next"}),
    #"Expanded d.results" = Table.ExpandListColumn(#"Expanded d", "d.results"),
… till the end of the code