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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Sargey67
Frequent Visitor

Power BI REST API Pagination Issue

Hi community,

 

I am new to Power BI / Power Query and I am trying to fathom how to retrive all pages of data from an API.

The API in question returns very little response to work with (I have for example read on forums about Paging, where number of pages, number of records and all sorts of additional detail is provided in response to the API call) but unfortunately the API I am working with provides only the following response:

Sargey67_0-1657292280286.png

 

So, I see that there are 636 records in total.

I have further realised that the API provides data in chunks of 200 records, so I have cobbled together the following query which does calculate that 4 calls will need to be made at 200 max records per call to retrieve the 636 records.

My query below does return 800 records(which is of course incorrect) and is returning the first 200 records 4 times.

Is there anybody who can assist please as this is driving me just a little bit crazy, I have tried multiple different queries to try and get this working and the below is the closest I have got!

 

let

    Source = Json.Document(Web.Contents("MyApiURL"  , [Headers=[Accept="application/json", #"Cache-Control"="no-cache", #"HA-Api-Key"="APIKey"]])),

    BaseUrl = "MyApiURL",

    EntitiesPerPage = 200,

     

GetJson = (Url) =>

  let RawData = Web.Contents("MyApiURL" & "&take=200" , [Headers=[Accept="application/json", #"Cache-Control"="no-cache", #"HA-Api-Key"="APIKey"]]) ,

 Json = Json.Document(RawData)

  in  Json,

   

    GetTotalEntities = () =>

        let Json = GetJson(BaseUrl),

            Total = Json[total]

        in  Total,

 

    GetPage = (Index) =>

        let Skip  = "skip=" & Text.From(Index * EntitiesPerPage),

            Url   = BaseUrl & "&" & Skip ,

            Json  = GetJson(Url),

            Value = Json[results]

        in  Value,

 

    GetUrl = (Index) =>

        let Skip  = "skip=" & Text.From(Index * EntitiesPerPage),

            Url   = BaseUrl & "&" & Skip

        in  Url,

 

    EntityCount = List.Max({ EntitiesPerPage, GetTotalEntities() }),

    PageCount   = Number.RoundUp(EntityCount / EntitiesPerPage),

  PageIndices = { 0 .. PageCount -1},

  URLs  = List.Transform(PageIndices, each GetUrl(_)),

 Pages       = List.Transform(PageIndices, each GetPage(_)) ,

 Entities    = List.Combine(Pages),

    #"Converted to Table" = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "assetType", "siteId", "area", "region", "licencedBoatId", "licencedAccountId", "isLicenced", "maxLength", "maxDraft", "length", "unusable", "createdOn", "modifiedOn"}, {"id", "name", "assetType", "siteId", "area", "region", "licencedBoatId", "licencedAccountId", "isLicenced", "maxLength", "maxDraft", "length", "unusable", "createdOn", "modifiedOn"})

in

    #"Expanded Column1"

 

I get the following results:

Sargey67_1-1657293714608.png

Sargey67_2-1657293761653.png

Sargey67_3-1657293796807.png

Sargey67_4-1657293837185.png

 

Sargey67_6-1657293947552.png

Issue with the pages returned above is that clicking into each of the 4 lists returns the same first 200 records.......

 

Below combined entities gives me 800 records in total (first 200 records * 4) so record no. 201 for example is duplicate of record 1 and so on.

Sargey67_7-1657294037747.png

 

Any assistance would be gratefully received.

 

 

 

 

 

 

 

2 REPLIES 2
Anonymous
Not applicable

Hi @Sargey67 ,

 

Here's a power query pagination example:

let 
    BaseUrl         = "https://fake-odata-api.com/v1/Entities?",
    Token           = "F4K3-T0K3N-D0NT-U5E-L0L",
    EntitiesPerPage = 1000,
 
    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

You could refer to the following links for reference:

How to do Pagination on a REST API in Power Query (Part 1) - YouTube

powerbi - How to get paginated data from API in Power BI - Stack Overflow

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Stephen

 

Thanks for your post however when I tailor the above query to work with my API I still get same results, 800 records returned in table (4 x first 200 results) instead of the 636 records expected.

My revised query is below, can you see anything within it which would restrict it only to the first 200 results and then duplicate it 4 times?

 

let

BaseUrl = "https://api.harbourassist.com/assets/" & "?SiteId=11" & "&TenantKey=MyTenantKey",
EntitiesPerPage = 200,


GetJson = (Url) =>
let Options = [Headers=[Accept="application/json", #"Cache-Control"="no-cache",#"HA-Api-Key"="MyApiKey"]],
RawData = Web.Contents(BaseUrl, Options) ,
Json = Json.Document(RawData)
in Json,

GetEntityCount = () =>
let Url = BaseUrl & "$count=true&$top=0",
Json = GetJson(Url),
Count = Json[total]
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[results]
in Value,

 

GetUrl = (Index) =>
let Skip = "skip=" & Text.From(Index * EntitiesPerPage),
Url = BaseUrl & "&" & Skip
in Url,

EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 0 .. PageCount -1},
URLs = List.Transform(PageIndices, each GetUrl(_)),
Pages = List.Transform(PageIndices, each GetPage(_)) ,
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
Table

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.