Forum Discussion
How to use the $Skip ODATA expression in a loop?
A simpler approach is shown in this article/video - Power BI - Tales From The Front - REST APIs - YouTube
You can adapt that approach. Basically, you create a list of number that increment by 100 with List.Numbers, convert that to a table, make your number column a text column and then concatenate the number into your URL on each row. You then expand the column of "Table"s to get your result.
Pat
A simpler approach is shown in this article/video - Power BI - Tales From The Front - REST APIs - YouTube
You can adapt that approach. Basically, you create a list of number that increment by 100 with List.Numbers, convert that to a table, make your number column a text column and then concatenate the number into your URL on each row. You then expand the column of "Table"s to get your result.
Pat
Hi Pat, thank you for your response. I've watched the video - Excellently narrated and if I understand it right, it seems a very efficient way of doing this - So I've come up with my version of the code as below:
let
Token = "Token",
BaseURL = "https://psa.pulseway.com/api/",
Path = "servicedesk/tickets/",
RecordsPerPage = 100,
//Count the total number of records
CountRecords = Json.Document(Web.Contents(BaseURL, [Headers = [Authorization="Bearer " & Token], RelativePath = Path & "count"])),
TotalRecords = CountRecords[TotalRecords],
//Create table of calls
ListCalls = List.Numbers(0, TotalRecords/RecordsPerPage, RecordsPerPage),
TableCalls = Table.FromList(ListCalls, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ColumnType = Table.TransformColumnTypes(TableCalls,{{"Column1", type text}}),
AddColumn = Table.AddColumn(ColumnType, "Custom", each Json.Document(Web.Contents(BaseURL, [Headers = [Authorization = "Bearer " & Token], RelativePath = Path & "?" & "$Skip=" [Column1]])))
in
AddColumn
This produces a table of 67 rows for each of the pages I will be calling and correctly increases in increments of 100 which is the total records per page that the API I'm using returns.
It turns that column to text, adds another, but then fails at either creating the URL per line, or returning the JSON. Not sure which. This is what the code results in:
Any tips on where I could be going wrong? Is perhaps that I'm trying to pass the authorization headers?
Thank you.