Power BI REST API Pagination Issue

[Sargey67]

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:

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:

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.

Any assistance would be gratefully received.

[Anonymous]

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.

[Sargey67]

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