Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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"
Solved! Go to 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
Handling paging for Power Query connectors - Power Query | Microsoft Learn
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