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,
    
    GetTextData = (Url) =>
        let Options = [
        Headers = [#"accept" = "text/plain"]
    ],
        RawData = Web.Contents(Url, Options),
        TextData = Text.FromBinary(RawData)
    in TextData,
 
    GetRecordCount = () =>
        let Url   = BaseUrl & "/$count",
            TextData  = GetTextData(Url),
            Count = Number.FromText(TextData)
        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"

We recommend you assess the implementation of an incremental load approach for these big tables, and you can achieve this by using 1 of 2 ways I list below. You can even combine both for optimal result:

• Delta sync – filtering by date range, to sync only records created or updated after the last sync.
  This article has detailed instructions on how to achieve this in Excel, but it’ll apply to PowerBI as well.

• Pagination – If applying the approach above, your result set is still bigger than the restriction, you can split it in smaller chunks and use pagination.
  Here is another article you might find useful.

Optionally, we can increase the limit, in case you don’t see the need for the approaches above. From R2.3+ there’s a feature that allows you to customize this limit at a project level.