Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
21 | |
20 | |
13 | |
13 |