After a couple of days, and a hundred differet approaches, I'm of the opinion that PowerQuery and API's are a joke.
I have an endpoint that has just under 10,000 records that I want to retrieve.
If I do it in one call - ok. Except that the providers limit is 10,000 and that is going to stop working very soon when 10,001 records are available.
If I use a paged approach without a relative path in the code below, paging the api calls, it works fine in excel, as my sandbox.
When I move it to dataflows in the glorious PowerBi citadel, then suddenly dynamic relativeURLs are a problem, so I have to modify my code.
Fine. So I modify it... code below.
Now suddenly I get every. Single. Record. duplicated 367 times.
@#$%^&*()(*&^%$#@!@#$%^&*(*&^%$#@!~
Of course, PowerBI dataflows take 5 minutes to retrieve 10 records, so this whole process should take me another 1,000 years to resolve.
And also, I can "save" my power BI dataflow (online), and it will give some exception or the other. Just click Save, Cancel, Save, Cancel, Save, go get coffee, Save ... and it saves ok!...
It is the 21st century ... right?
At this point my conclusion is: Don't try do anything too difficult with PowerBI. Keep it simple. Build a python and local SQL database process, and use PowerBI to retrieve data from the SQL database. That shouldn't be too difficult.
I hope.
But I can never be sure.
let
AuthResponse = Json.Document(Web.Contents("https://....../login",
[Content=Text.ToBinary("{ ""username"": """ & username & """, ""password"": """ & password & """}"),
Headers=[#"Content-Type"="application/json"]])),
AccessToken = AuthResponse[access_token],
BaseUrl = "https://......../property",
// Function to get a single page of data
GetPage = (PageNumber as number) =>
let
Source = Json.Document(Web.Contents(BaseUrl,
[Headers=[#"Authorization"="Bearer " & AccessToken,
#"Content-Type"="application/json",
RelativePath = "?limit=100&page=" & Number.ToText(PageNumber)]])),
Data = Source[data],
TotalPages = Source[total_pages]
in
[Data=Data, TotalPages=TotalPages],
// Initialize variables
InitialPage = GetPage(1),
InitialData = InitialPage[Data],
TotalPages = InitialPage[TotalPages],
// Loop to get all pages
GetAllPages =
let
Loop = (PageNumber, Result) =>
let
PageData = GetPage(PageNumber)[Data],
NewResult = List.Combine({Result, PageData}),
NextPage = PageNumber + 1
in
if List.Count(PageData) = 0 or PageNumber >= TotalPages then
Result
else
@Loop(NextPage, NewResult)
in
Loop(2, InitialData),
// Combine all pages into a single table
CombinePages = Table.FromList(GetAllPages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandPages = Table.ExpandRecordColumn(CombinePages, "Column1", {"id", .........."}),
SortedRows = Table.Sort(ExpandPages,{{"id", Order.Ascending}})
in
SortedRows
