Pagination Issue in Power BI execute query API

Question

Pagination Issue in Power BI execute query API

Gandluri Deekshith Reddy 20

Hi Team,

I am attempting to retrieve values from a table in Power BI via an API in Azure Data Factory. However, the table contains over 100,000 rows, and I've encountered a limitation with the API: it does not allow transfers exceeding 15 MB in size. Could you please assist me with this issue?
POST https://api.powerbi.com/v1.0/myorg/datasets/cfafbeb1-8037-4d0c-896e-a46fb27ff229/executeQueries{

User's image

phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-03-22T06:46:00.0066667+00:00
@Deekshith Reddy
Thanks for reaching out to Microsoft Q&A

You're right, the Power BI REST API has a limitation on response size. Retrieving a large table (over 1 lakh rows) in a single call can exceed this limit. Here are a couple of approaches you can consider in Azure Data Factory (ADF) to handle this scenario:

1. Pagination:

Use the $skip and $top OData query options in your API call.

$skip: This specifies the number of rows to skip before returning results.

$top: This specifies the maximum number of rows to return in the response.

Here's an example with pagination:

POST https://api.powerbi.com/v1.0/myorg/datasets/cfafbeb1-8037-4d0c-896e-a46fb27ff229/executeQueries?$skip=0&$top=10000

This retrieves the first 10,000 rows. You can then iterate through the data in a loop within your ADF pipeline, incrementing the $skip value by $top for each subsequent call until all data is retrieved.

2. Exporting the Table:

Use the Power BI Desktop export functionality to export the table to a delimited format (e.g., CSV).

You can achieve this programmatically using the Power BI service API or PowerShell cmdlets.

In ADF, use a copy activity to transfer the exported file from Azure Blob Storage (or another supported data store) to your destination.

This approach might be faster for very large datasets, but it involves an additional step of exporting the data first.

Consider filtering the data in the Power BI table itself before retrieving it using the API. This can significantly reduce the amount of data you need to transfer.

Explore using Azure Functions or Logic Apps for handling the pagination logic if it's complex.

For detailed information on the Power BI REST API and OData options, refer to the official documentation: https://learn.microsoft.com/en-us/rest/api/power-bi/

Hope this helps. Do let us know if you any further queries.
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-03-25T04:52:00.17+00:00

@Deekshith Reddy G We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Gandluri Deekshith Reddy 20 Reputation points

2024-03-25T05:51:57.1+00:00

@phemanth Thank you so much for the response ,Apologies for the delayed reply. I am currently without a primary key in the dataset I'm working with. Could you please assist me in figuring out how to proceed without a primary key?
Gandluri Deekshith Reddy 20 Reputation points

2024-03-25T06:07:41.41+00:00

--I have attempted to resolve the issue using logic app ,How ever the problem persists
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-04-10T04:37:43.7133333+00:00

@Deekshith Reddy G Glad to know your issue has been resolved. Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.

Accepted answer

2 additional answers

Your answer

phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-03-22T06:46:00.0066667+00:00

@Deekshith Reddy
Thanks for reaching out to Microsoft Q&A

You're right, the Power BI REST API has a limitation on response size. Retrieving a large table (over 1 lakh rows) in a single call can exceed this limit. Here are a couple of approaches you can consider in Azure Data Factory (ADF) to handle this scenario:

1. Pagination:

Use the $skip and $top OData query options in your API call.

$skip: This specifies the number of rows to skip before returning results.

$top: This specifies the maximum number of rows to return in the response.

Here's an example with pagination:

POST https://api.powerbi.com/v1.0/myorg/datasets/cfafbeb1-8037-4d0c-896e-a46fb27ff229/executeQueries?$skip=0&$top=10000

This retrieves the first 10,000 rows. You can then iterate through the data in a loop within your ADF pipeline, incrementing the $skip value by $top for each subsequent call until all data is retrieved.

2. Exporting the Table:

Use the Power BI Desktop export functionality to export the table to a delimited format (e.g., CSV).

You can achieve this programmatically using the Power BI service API or PowerShell cmdlets.

In ADF, use a copy activity to transfer the exported file from Azure Blob Storage (or another supported data store) to your destination.

This approach might be faster for very large datasets, but it involves an additional step of exporting the data first.

Consider filtering the data in the Power BI table itself before retrieving it using the API. This can significantly reduce the amount of data you need to transfer.

Explore using Azure Functions or Logic Apps for handling the pagination logic if it's complex.

For detailed information on the Power BI REST API and OData options, refer to the official documentation: https://learn.microsoft.com/en-us/rest/api/power-bi/

Hope this helps. Do let us know if you any further queries.
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-03-25T04:52:00.17+00:00

@Deekshith Reddy G We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Gandluri Deekshith Reddy 20 Reputation points

2024-03-25T05:51:57.1+00:00

@phemanth Thank you so much for the response ,Apologies for the delayed reply. I am currently without a primary key in the dataset I'm working with. Could you please assist me in figuring out how to proceed without a primary key?
Gandluri Deekshith Reddy 20 Reputation points

2024-03-25T06:07:41.41+00:00

--I have attempted to resolve the issue using logic app ,How ever the problem persists
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-04-10T04:37:43.7133333+00:00

@Deekshith Reddy G Glad to know your issue has been resolved. Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.

Answer 1

Nandan Hegde 36,146 MVP Volunteer Moderator

Please follow the below blog :

https://datasharkx.wordpress.com/2023/03/02/overcoming-data-size-row-limitations-of-power-bi-rest-api-for-automated-data-extraction-from-dataset/

that provides step by steps details to overcome the limitation by spliting the data in chunks

Gandluri Deekshith Reddy 20 Reputation points

2024-03-25T06:20:52.7066667+00:00

Hi Nandan Hegde,

I greatly appreciate your response.

I would like to point out that my current situation does not have any primary key. I noticed that the article you referenced utilizes an Employee ID as a key identifier, which unfortunately, I do not have in my case.

Thanks,
Deekshith

Answer 2

@Deekshith Reddy

The lack of a primary key in your dataset can potentially complicate working with large datasets in Azure Data Factory (ADF). Here's how to approach this issue in conjunction with the data size limitation of the Power BI API:

1. Handling Large Data without a Primary Key:

There are two main approaches to process large datasets in ADF without a primary key:

Chunking the Data: You can break down the data retrieval into smaller chunks that fit within the 15 MB limit. ADF allows you to define a chunking mechanism during data transfer. This way, you can process the data in smaller batches and avoid exceeding the size limit.

Leveraging a Surrogate Key: If your scenario allows for modification of the data model, consider adding a surrogate key to your dataset. This could be an auto-incrementing integer column that uniquely identifies each row. Having a surrogate key simplifies data retrieval and manipulation, especially when dealing with large datasets.

2. Choosing the Right Approach:

Chunking is ideal for: One-time data transfers or scenarios where modifying the data model is not feasible.
Surrogate key is ideal for: Repeated data transfers, improved query performance, and easier data manipulation within ADF.
Pagination with Chunking: If chunking alone isn't enough, explore using ADF's pagination feature along with chunking. This allows you to retrieve data in smaller chunks based on a specific date range or other criteria.

4. Logic Apps and Data Factory:

Logic Apps can be used to orchestrate workflows in Azure, but for data movement and manipulation, ADF is generally the preferred tool. You can potentially call ADF pipelines from within a Logic App to handle the data retrieval and chunking logic.

By combining these techniques, you can effectively retrieve data from your Power BI table in Azure Data Factory even without a primary key, while staying within the API's size limitations. limitations.

Answer 3

@Deekshith Reddy G Welcome to Microsoft Q&A platform and thanks for posting your question.

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.

Ask:

I am attempting to retrieve values from a table in Power BI via an API in Azure Data Factory. However, the table contains over 100,000 rows, and I've encountered a limitation with the API: it does not allow transfers exceeding 15 MB in size. Could you please assist me with this issue? POST https://api.powerbi.com/v1.0/myorg/datasets/cfafbeb1-8037-4d0c-896e-a46fb27ff229/executeQueries{

User's image

Solution: I have attempted to resolve the issue using logic app.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Share via

Pagination Issue in Power BI execute query API

2 additional answers

Your answer