Forum Discussion
Paginate through API call in M
Hi,
I'm pulling data using an API call in M Query. The record count is in the tens of thousands, but the API can only retrieve 2000 records per page. How can I retrieve all records? (If by using Table.GenerateByPage, how would I incorporate that function into the query below?)
let
Source = Json.Document(Web.Contents(
"endpointURL&$top=2000",
[Headers=
[
#"key1" = "abc",
#"id1" = "123",
#"header1" = "def",
#"key2" = Text.Combine({parameter}),
#"response-cache" = "false",
#"header2" = "x",
#"header3" = "y",
#"header4" = "z"
]]
)
),
#"Converted to Table" = Record.ToTable(Source),
Value = #"Converted to Table"{0}[Value],
Custom1 = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(Custom1, "Column1",
{"Column1", "Column2"}),
#"Sorted Rows" = Table.Sort(#"Expanded Column1",{{"Column1", Order.Ascending}})
in
#"Sorted Rows"Thank you!
Please see this video for a good way to do this.
Power BI - Tales From The Front - REST APIs - YouTube
Pat
7 Replies
- mahoneypatMicrosoft Employee
Please see this video for a good way to do this.
Power BI - Tales From The Front - REST APIs - YouTube
Pat
- AnonymousNot applicable
That video was super helpful - thanks so much!
One thing I'm still unclear on: If I parameterize the URL, what would be the best way to incorporate the API call Headers?
- mahoneypatMicrosoft Employee
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