Start using Hubway Connect today - Start FREE trial

  1. Home
  2. Knowledge Base
  3. Tutorials
  4. How can I achieve pagination in Microsoft Power BI?
  1. Home
  2. Knowledge Base
  3. FAQs
  4. Data Analysis
  5. How can I achieve pagination in Microsoft Power BI?

How can I achieve pagination in Microsoft Power BI?

Here is a reference article to loop through OData Entities and retrieve all the records Power Query Rest API Paging with @odata.nextLink

In PowerBI, go to Transform Data → New Data Source → Blank query

Click Advanced Editor and paste the code in the black box below. Replace <Service link>/<Entity Name> with your Test or Production service links and Entity Name. Also, replace columnA, columnB, etc., with the attributes in your Entity.

When prompted for Credentials, select Basic Authentication and select the OutSystems username and password for the service link.

This will paginate in blocks of 1000, retrieve all the records in the Entity and add them to your Power BI model.

let 
    BaseUrl         = "<Service link/<Entity Name>",
    RecordsPerPage = 1000,
 
    GetJson = (Url) =>
        let Options = [
        Headers = [#"accept" = "application/json"]
      ],
            RawData = Web.Contents(Url, Options),
            Json    = Json.Document(RawData)
        in  Json,
 
    GetRecordCount = () =>
        let Url   = BaseUrl & "?$count=true&$top=1",
            Json  = GetJson(Url),
            Count = Json[#"@odata.count"]
        in  Count,
 
    GetPage = (Index) =>
        let Skip  = "?$skip=" & Text.From(Index * RecordsPerPage),
            Top   = "$top=" & Text.From(RecordsPerPage),
            Url   = BaseUrl & Skip,
            Json  = GetJson(Url),
            Value = Json[#"value"]
        in  Value,
 
    RecordCount = List.Max({ RecordsPerPage, GetRecordCount() }),
    PageCount   = Number.RoundUp(RecordCount / RecordsPerPage),
    PageIndices = { 0 .. PageCount - 1 },
    Pages       = List.Transform(PageIndices, each GetPage(_)),
    Records    = List.Union(Pages),
    Table       = Table.FromList(Records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(Table, "Column1", {"columnA", "columnB", "columnC"})
in
    #"Expanded Column1"
Was this article helpful?

Related Articles

Need Support?

Can't find the answer you're looking for?
Contact Support