Hi,
I already have a solution to get an unknown number of pages of data from an API, but need some help to improve the security of it. This API does not provide a 'Next Page' link, so what the following query does is firstly, a query to count the number of records. It then loops through and retrieves pages of 2000 records until it reaches the count value + 2000. This works fine right now, other than the security aspect...
The following PowerQuery code works, but only if we hard-code a 'sessionID' or 'apiKey' parameter as part of the URL. This is not great from a security perspective as the URL could be logged. This API does allow you to send the sessionID/apiKey via the request Headers, but the developer who wrote the query for me couldn't get this to work with Power Query. I believe this was related to the use of RelativePath.
We need this query to be able to work to refresh data in the Power BI Service - not just Desktop. I saw a different solution on a thread somewhere but the user said that it wouldn't work in the Power BI Service due to the use of a function?
So my main question I'd love some help on: Is there a way to modify the below query (or replace it with a different solution) so that we can pass the sessionID/apiKey in the request Headers?
If that's not possible, passing the sessionID in the URL would be OK enough, as that changes frequently. It's hard-coded in the query for now, but I expect I could add it as another line where it first does an API call to do the login, which generates the sessionID. We'd then need to use the result of that in the other API calls where they do the count and retrieve the actual records.
Any guidance on this would be super appreciated! Thank you.
let
CountSource = Json.Document(Web.Contents("https://myapi.com/count?sessionID=331fbae3919942f79f14894865eaf47f")),
Count = List.First(Record.FieldValues(CountSource[data])),
TableList = List.Generate( () => [i = 1, sourcetable = Table.FromList(Json.Document(Web.Contents("https://myapi.com/search?sessionID=331fbae3919942f79f14894865eaf47f&$$LIMIT=2000&ID_Sort=asc&$$FIRST...", [RelativePath="1"]))[data], Splitter.SplitByNothing(), null, null, ExtraValues.Error)], each [i] <= Count+2000, each let upto = Number.ToText([i]), GetTable = (upto as text) => Table.FromList(Json.Document(Web.Contents("https://myapi.com/search?sessionID=331fbae3919942f79f14894865eaf47f&$$LIMIT=2000&ID_Sort=asc", [Query=[#"$$FIRST"=upto]]))[data], Splitter.SplitByNothing(), null, null, ExtraValues.Error) in [i = [i] + 2000, sourcetable = Function.Invoke(()=>GetTable(upto), {})], each [sourcetable]),
#"CombinedTable" = Table.Combine(TableList)
in
#"CombinedTable"
