This is my current code. It of course only returns the first page:
let
url = "https://id.myapi.ac.uk/connect/token",
GetJson = Web.Contents(url,
[
Headers = [#"Content-Type"="application/x-www-form-urlencoded;"],
Content = Text.ToBinary("client_id=mechamp&client_secret=notthislol&grant_type=client_credentials&scope=myapi")
]
),
FormatAsJson = Json.Document(GetJson),
// Gets token from the Json response
AccessToken = FormatAsJson[access_token],
AccessTokenHeader = "bearer " & AccessToken,
// Uses the GET search method using the bearer token from the previous POST oauth2/token method
GetJsonQuery = Web.Contents("https://data.api.myapi.ac.uk/activities?recordState=0&pageSize=100",
[
Headers = [#"Authorization"=AccessTokenHeader,
#"api-version"="2",
#"Accept"="application/json"]
]
),
FormatAsJsonQuery = Json.Document(GetJsonQuery),
Name"})
in
FormatAsJsonQuery
I previously implemented this in Excel, using a curl request wrapped in a batch file created by VBA. Omitting the parts that already work in M query the batch file looked like this:
rem ommiting getting the token and setting it as %token%
set pagenum=1
set pagesize=100
curl "{https://data.api.myapi.ac.uk/activities?&page=%pagenum%&pageSize=1&recordstate=0}" -G --header "api-version: 2" --header "Authorization: Bearer %token%" --header "Accept: application/json" --trace-ascii %userprofile%\api\totalpages.txt
for /F "delims=" %%a in ('findstr /I "totalCount" %userprofile%\api\totalpages.txt') do set "totalrecords=%%a"
set totalrecords=%totalrecords:,"t=%
set totalrecords=%totalrecords:0000: X-Pagination: {"currentPage":1,"pageSize":1otalCount":=%
rem del %userprofile%\api\totalpages.txt
set /a totalpages = %totalrecords%/%pagesize%
set /a totalpages = %totalpages%+1
start "" /min curl "{https://data.api.heat.ac.uk/activities?&page=%pagenum%&pageSize=%pagesize%&recordstate=0&sort=-activityStartDate}" -G --data-urlencode "fields=%fields%" --header "api-version: 2" --header "Authorization: Bearer %token%" --header "Accept: application/xml" --output %userprofile%\api\file%pagenum%.txt
:loop
set /A pagenum=pagenum+1
start "" /min curl "{https://data.api.heat.ac.uk/activities?&page=%pagenum%&pageSize=%pagesize%&recordstate=0&sort=-activityStartDate}" -G --data-urlencode "fields=%fields%" --header "api-version: 2" --header "Authorization: Bearer %token%" --header "Accept: application/xml" --output %userprofile%\api\file%pagenum%.txt
if %pagenum% LSS %totalpages% (goto :loop)
rem omitting lines that copy all the destination files into one and format into into a valid single json
exit
You can see it first makes a request and dumps the raw output into a file, including the headers which tell me how many records there are, which I can then use to work out how many pages there are. I googled how to get the headers in PowerBI, but in their unwisdom it seems that Microsoft have decided nobody needs them and would do bad things with them.
How can I find out how many pages there are and request them all at once?
Edit: the below are attempts to find out how many pages there are. The alternative would be to just keep requesting the next page until the server returns 404, that's fine if it works concurrently. In other words if this can work without seeing the response headers, that's good enough for me. I'm new to power bi so if you can point me in the direction of a script which does that, that would be great.
This blog post is widely cited as demonstrating that approach, but unfortunately is no longer available: https://www.mattmasson.com/2014/11/iterating-over-an-unknown-number-of-pages-in-power-query/ Internet archive also doesn't contain this.
Things that I couldn't make work: https://medium.com/@marktiedemann/how-to-do-pagination-in-power-query-430460c17c78 "@odata.count" was not found was the error I received, apparently not supported by this API.
There was a reference to something called WebMethod.Head, which sounds interesting, but no documentation is provided on how to use it: https://msdn.microsoft.com/en-us/query-bi/m/webmethod-head
I tried to recreate this exact script which claims to receive the response headers, but the syntax didn't work https://community.powerbi.com/t5/Desktop/Retrieve-API-response-headers/td-p/423704?lightbox-message-images-641107=151369i21A62C85FABC9F9C
Apparent for some apis it's as simple as
= try GetJsonQuery[next_page_url] otherwise null
And for me it's null!
Odata.Feed tells me that there is no Odata at this endpoint.
GetJsonQuery = OData.Feed("https://data.myapi.ac.uk/activities?recordState=0&pageSize=100",
[
#"Authorization"="bearer " & "AccessToken",
#"api-version"="2",
#"Accept"="application/json"
], [IncludeMetadataAnnotations="*"] ) in
GetJsonQuery
Any ideas?
