'SKIP' Alternative for EXECUTE QUERIES API pagination

[nckpedersen]

Hi all, 

I am trying to develop a method of pagination for the EXECUTE QUERIES api to retrieve data beyond the 100k row limit. I was hoping something like this would work but it seems the API doesn't support skip... 

for (let i = 0; i < iterations; i++) {

          const skip = i * pageSize;

          const pagedQuery = {

              queries: [

                  {

                      query: `EVALUATE SKIP(TOPN(${skip + pageSize}, VALUES('${tableName}'[${columnName}])), ${skip})`,

                      queryType: "DAX"

                  }

              ]

          };

Thoughts? 

[Anonymous · Accepted solution]

Hi @nckpedersen ,

As far as I know, Power BI Dataset Rest API has the limitation for the maximum of 100,000 rows or 1,000,000 values per query.

For reference: Datasets - Execute Queries - REST API (Power BI Power BI REST APIs) | Microsoft Learn

You can try to use the TOPN function in DAX itself to simulate pagination. You can tweak your query to fetch a specific range of rows by sequentially adjusting the TOPN function as follows:

EVALUATE
VAR PageNumber = <YourPageNumber>
VAR PageSize = <YourPageSize>
RETURN
TOPN(
PageSize,
FILTER(
ALL('<TableName>'),
'<TableName>'[<ColumnName>] > CALCULATE(MAX('<TableName>'[<ColumnName>]), TOPN((PageNumber - 1) * PageSize, '<TableName>', '<TableName>'[<ColumnName>], ASC))
),
'<TableName>'[<ColumnName>], ASC
)

In this query, replace <YourPageNumber> with the desired page number, <YourPageSize> with the size of each page, <TableName> with the name of your table, and <ColumnName> with the column by which you wish to order the results.

This approach involves calculating the maximum value of the key column for the last item on the previous page and then taking the next set of records starting just after that value.

Also you may refer to below link to try to achieve your goal.

To overcome the limitation of Data row / Size, we need to get the overall row count (total number of rows within the dataset) and partition the dataset in some batch range to extract the data in partitions and finally merge the data into a single file.

For reference:  Overcoming Limitations of Power BI REST API for Automated Data Extraction from Dataset

Best Regards,
Rico Zhou

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