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:
- The first 10 pages (100 per page x 10 = 1000)
- All within the 12 months from a column named "OpenDate"
Thank you in advance and I look forward to your responses.
Purpose: Load the paginated API data, which loads only 50 rows per page. This api has 2 URL parameters:
a. limit: Number of rows to load per page
b. skip: Skip number of rows from the start. 0 - skip none; 50 - skip 50 and start the page from 51st row.
and a authorization (bearer) token.
Steps:
1. Invoke the base API with headers (optional), by clicking the menu options, Get data -> Web ->Advanced
If the call is successful, PowerBI will open Power Query Editor and load the results in a table
2. In the left query pane, click on query name
3. Click on Advanced Editor from the top menu.
4. Select all and copy the contents to clipboard/NotePad.
5. Click on Cancel to exit the editor dialog.
6. Delete this query by right-clicking on the query name and by clicking on Delete option.
7. Create a new query by right-clicking anywhere on the left (Query) pane, to open context menu and select New Query -> Blank Query
8. Click on Advanced Editor from the top menu.
9. Select all and clear the default templated code.
10. Paste the content copied in Step #4. Depending upon the transformation, the entries may look similar to this below.
11. Make this as a function, which can be invoked multiple times to handle paging, by adding function parameter. Add a line at the start as shown below.
Please note.
a. the parameter on the first line
b. Check the highlighted area of the url. Url parameter limit=50. In my case, the url parameter indicates that API has to fetch 50 rows per page/call. Your url parameter and limits may be different
c. The function offset parameter is added to the url parameter skip to skip number of rows from the start of whole data. We use Number.ToText to convert the number into text to concat with the url string.
12. Give the function a name by adding to the let in. We will manipulate the output of the function (shown below as dot dots) in the subsequent steps
13. Since the function is ready now, the next step is to
a. Call this function in a loop to fetch all the data, page by page.
b. Determine when to stop looping when all data is retrieved
c. Combine all the results in one table.
To Get total number of rows to fetch
To get total rows in the API dataset, we can call and store base API call result and later use it's total attribute.
here allRows is the variable where I am storing the result of the base API call.
To Loop until all rows are fetched
List.Generate will help us loop and call the function as many times as required. In my case, the page limit is 50 and so the loop is setup in increments of 50 until all rows are fetched, which is determined by allRows[total] attribute.
apiCallResults variable create a list of Tables, as a result of our multiple calls made by List.Generate loop.
To combine all results into one table
Table.Combine(table1, table2 ..) function will help us merge all the result tables. So we will table combine to our output.
Since we got everything here, we replace the lines with dot dots with this let in block as shown below.
14. This is final assembly
References:
https://learn.microsoft.com/en-us/powerquery-m/web-contents
https://stackoverflow.com/questions/46904641/how-to-get-paginated-data-from-api-in-power-bi
https://gorilla.bi/power-query/list-generate-api-calls/
https://learn.microsoft.com/en-us/powerquery-m/table-combine
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.
Helpful resources
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
