Start using Hubway Connect today - Start FREE trial

  1. Home
  2. Knowledge Base
  3. Tutorials
  4. How to achieve pagination in Microsoft Excel?
  1. Home
  2. Knowledge Base
  3. FAQs
  4. Data Analysis
  5. How to achieve pagination in Microsoft Excel?

How to achieve pagination in Microsoft Excel?

In MS Excel, go to Data → New query → From Other Sources → Blank Query

pagination in Microsoft Excel?
Pagination in Microsoft Excel?

This will open the Power Query Editor.

Click Advanced Editor and paste the code below. Replace <Service link>/<Entity Name> with your Test or Production service links and Entity name. Also, replace columnA, columnB, etc., with the column names in your Entity. When prompted for Credentials, select Basic Authentication and enter your OutSystems username and password for the service link.

This will paginate in blocks of 1000, retrieve all the records in the Entity and add it to your Excel model. Click Close and Load. This will close the Power Query Editor and load the query into the Excel Worksheet.

    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"})
    #"Expanded Column1"



Was this article helpful?

Related Articles

Need Support?

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