Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Good morning all,
Hoping you can help as I'm well and truly stuck. I've successfully connected to a REST API using the below code:
let
AuthKey = "Token",
url = "https://psa.pulseway.com/api/?$orderby=OpenDate desc",
Source = Json.Document(Web.Contents(url,[
Headers = [Authorization="Bearer " & AuthKey]
,RelativePath = "servicedesk/tickets/"])),
Result = Source[Result],
#"Converted to Table" = Table.FromList(Result, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"
..however this only returns the first 100 results of approximately 6500 expected rows. Some reading tells me I need to paginate my results by first indexing how many pages of results there are and combine them one at a time?
This led me to this article where I'd borrowed, ammended and tested the code:
let
BaseUrl = "https://psa.pulseway.com/api/servicedesk/tickets",
Token = "TOKEN",
EntitiesPerPage = 100,
GetJson = (Url) =>
let Options = [Headers=[ #"Authorization" = "Bearer " & Token ]],
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData)
in Json,
GetEntityCount = () =>
let Url = BaseUrl & "$count=true&$top=0",
Json = GetJson(Url),
Count = Json[#"@odata.count"]
in Count,
GetPage = (Index) =>
let Skip = "$skip=" & Text.From(Index * EntitiesPerPage),
Top = "$top=" & Text.From(EntitiesPerPage),
Url = BaseUrl & Skip & "&" & Top,
Json = GetJson(Url),
Value = Json[#"value"]
in Value,
EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
Table
...it has no syntax errors however it doesn't work and I get the following error:
I know the URL and access token works as I'd tested it with my first code block, and I know the API supports the use of the $count $top and $skip query parameters as it says so in the documentation...
FILTER, SORT AND PAGING EXPRESSIONS
When multiple records are returned by a GET request, Odata expressions can be appended to the end of the URI. The three types of expressions determine whether the multiple records are filtered, sorted, or paged. Documentation identifies whether a field is filterable or sortable. Paging expressions are always available for any GET method that returns multiple records. Multiple Odata expressions can be combined in the same URI. In the following example, the first Odata expression is delimited by a question (?) character. Subsequent Odata expressions are delimited with an ampersand (&) character.
GET /api/accounts?$skip=30&$top=10&$orderby=Name
If anyone can tell me where I'm going wrong it would be greatly appreciated as I just can't figure this out.
Whilst it would be ideal and preferred, I don't necessarily need all 6500+ records.
If it would be easier to drop the part of the code that looks at how many records/pages then it could be:
Thank you in advance and I look forward to your responses.
Solved! Go to Solution.
Hi, @jwillis07
You can refer to these documents and check if they can help:
https://docs.microsoft.com/en-us/power-query/handlingpaging
https://stackoverflow.com/questions/66888658/paging-rest-api-results-in-power-query
https://stackoverflow.com/questions/46904641/how-to-get-paginated-data-from-api-in-power-bi
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @jwillis07
You can refer to these documents and check if they can help:
https://docs.microsoft.com/en-us/power-query/handlingpaging
https://stackoverflow.com/questions/66888658/paging-rest-api-results-in-power-query
https://stackoverflow.com/questions/46904641/how-to-get-paginated-data-from-api-in-power-bi
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This option:
https://stackoverflow.com/questions/46904641/how-to-get-paginated-data-from-api-in-power-bi
Works very well!
Thank you, I'll get busy reading.
After spending all day on this I've realised the above code will not work at all as the API doesn't list the number of pages for me to call. With that in mind, I've managed to get to the number of pages this way:
let
Token = "Token",
BaseURL = "https://psa.pulseway.com/api/",
Path = "servicedesk/tickets/",
RecordsPerPage = 100,
//the below line returns the total number of records - currently 6594
CountTickets = Json.Document(Web.Contents(BaseURL,[Headers = [Authorization="Bearer " & Token],RelativePath = Path & "count"])),
TotalRecords = CountTickets[TotalRecords],
//This line divides the number of records by the number of records per page to determine the total number of pages - Currently 66
PageCount = Number.RoundUp(TotalRecords / RecordsPerPage),
I still can't figure out the code that will call each of the 66 pages seperately and then combine them all together. This is as far as I've got:
let
Token = "Token",
BaseURL = "https://psa.pulseway.com/api/",
Path = "servicedesk/tickets/",
RecordsPerPage = 100,
//the below line returns the total number of records - currently 6594
CountTickets = Json.Document(Web.Contents(BaseURL,[Headers = [Authorization="Bearer " & Token],RelativePath = Path & "count"])),
TotalRecords = CountTickets[TotalRecords],
//This line divides the total number of records by the number of records per page to determine the total number of pages - Currently 66
PageCount = Number.RoundUp(TotalRecords / RecordsPerPage),
GetPage = (Index) =>
let Skip = "$skip=" & Text.From(Index * EntitiesPerPage),
Top = "$top=" & Text.From(EntitiesPerPage),
Url = BaseUrl & Path & Skip & "&" & Top,
Json = GetJson(URL),
Value = Json[#"value"]
in Value,
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
Table
If anyone can point me in the right direction it would most appreciated!
https://community.powerbi.com/t5/Power-Query/Dynamic-data-sources-aren-t-refreshed-in-the-Power-BI-s...
This works perfectly for loading all JIRA , Only issue I am still getting is the post-publish dataset cant refresh and gives the following error.
Hi!
I was looking for a solution and came up with this brilliant way.
https://stackoverflow.com/questions/46904641/how-to-get-paginated-data-from-api-in-power-bi
Check it out!
Thx 👍👍👍
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |