Forum Discussion
Paginate through API call in M
Please see this video for a good way to do this.
Power BI - Tales From The Front - REST APIs - YouTube
Pat
I'm assuming you would use the same headers for each web call. Is that not the case? Can you share your query from the Advanced Editor?
Pat
The query from the Advanced Editor has evolved a bit since asking the original question. Here's the newest version, with keys removed. (Notice the inclusion of $top=50 in the OData call, because otherwise, the call times out.)
let
Source = List.Numbers(0, Count/50, 50),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.AddColumn(#"Changed Type", "Custom", each Json.Document(Web.Contents(
"https://endpointURL?$filter=Type%20eq%20%27Base%27%20and%20Date%20eq%20%272021-09-26%27%20and%20Value%20ne%200&$top=50&limit=50&offset=" &[Column1],
[Headers=
[
#"Key" = "abc",
#"id" = "def",
#"Content-Type" = "application/json",
#"key2" = Text.Combine({key2parameter}),
#"response-cache" = "false",
]]
)
))),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Custom"}, {"Column1.1", "Custom.1"})
in
#"Expanded Custom"I'm also attaching a screenshot of my attempt at using your limit = + offset = method for paginating API calls. From the image, you can see what I'm returning. It looks like, rather than getting 50 records for Column1 = 0, another 50 records for Column1 = 1, etc. as you do in your video (at the 16:55 timestamp), it looks like I'm trying to get all available records (~15k) once within each Column1 (so, 15k records returned for Column1 = 0, another 15k records returned for Column1 = 1, etc.). Any thoughts on where I'm going wrong with the limit = & offset = method?
Found it!!! I was doing "Table.AddColumn" twice. (Somehow did this when adding the custom column.)
Now that that's resolved, it works! Pat, you're the man - thank you so much!!
mahoneypat I may have spoken too quickly here... I got the API to return results, but including that $top=50 parameter in the call just meant I get the top 50 results over and over again. The call now looks like this:
https://endpointURL?$filter=Type eq 'Base' &Date='2021-09-26'&$top=50&limit=50&offset=" &[Column1]But, if I remove the $top=50, then the API returns a 500 error (even with the limit & offset functions included).
Thoughts on what I might be doing wrong? (If I get a 500 error in my browser or Postman, I can get a log ID response that our development team can use internally to track a more precise error message. Is there any way to retrieve a response ID from within Power BI when it throws a 500 error?)
Figured it out. User error; needed to switch limit / offset to top / skip.
Thanks again! Cheers!