Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jwillis07
Helper I
Helper I

Pagination of a REST API in Power Query using M

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:

 

Capture.JPG

 

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.

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

7 REPLIES 7
v-robertq-msft
Community Support
Community Support

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

https://stackoverflow.com/questions/63920538/issue-fetching-paginated-data-from-a-rest-api-in-power-...

 

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.

Anonymous
Not applicable

Thank you, I'll get busy reading.

jwillis07
Helper I
Helper I

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.

 

codelover87_0-1667640769981.png

 

 

 

Anonymous
Not applicable

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 👍👍👍

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.