Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Dich123
Frequent Visitor

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"

1 ACCEPTED SOLUTION

You will need to spend time to go through the documentation and understand what it does.  Here is an example that does exactly what you need.  TripPin 5 - Paging - Power Query | Microsoft Learn

View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

Handling paging for Power Query connectors - Power Query | Microsoft Learn

 

lbendlin_1-1713784577620.png

 

 

 

Please do not show your access token on the interwebs

@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

 

You will need to spend time to go through the documentation and understand what it does.  Here is an example that does exactly what you need.  TripPin 5 - Paging - Power Query | Microsoft Learn

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors