Forum Discussion
Export Dataset to Excel File On Schedule
Hi tecumseh ,
You are correct, while the Power BI REST API doesn't impose a strict row limit, it does have a payload limit of approximately 1 MB per response, which can lead to truncated results for large or wide datasets, like what you experienced with Power Automate. You can find the official documentation for the API here: Execute Queries in Group – Power BI REST API.
To work around this, consider reducing the number of columns returned and implement pagination logic in your DAX queries (e.g., filtering on row numbers or date ranges) to retrieve data in smaller chunks. However, for greater control and scalability, the most reliable method would be extracting data directly from Snowflake using Python (via the Snowflake Python Connector) and then exporting to Excel using libraries like pandas or openpyxl. This approach avoids API and gateway constraints and can be fully automated using Windows Task Scheduler or Azure Automation.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thankyou.
Hi tecumseh ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.
Anonymous
I need to spend some time on it. I was pulled away my more pressing projects. on REST API approach isn't that going to be limited in size of data? I tried using Power Automate, but that has many limitations on size of data so I only received 9.2K rows out of expected 86K rows of data.
Thanks,
w
Hi tecumseh ,
You are correct, while the Power BI REST API doesn't impose a strict row limit, it does have a payload limit of approximately 1 MB per response, which can lead to truncated results for large or wide datasets, like what you experienced with Power Automate. You can find the official documentation for the API here: Execute Queries in Group – Power BI REST API.
To work around this, consider reducing the number of columns returned and implement pagination logic in your DAX queries (e.g., filtering on row numbers or date ranges) to retrieve data in smaller chunks. However, for greater control and scalability, the most reliable method would be extracting data directly from Snowflake using Python (via the Snowflake Python Connector) and then exporting to Excel using libraries like pandas or openpyxl. This approach avoids API and gateway constraints and can be fully automated using Windows Task Scheduler or Azure Automation.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thankyou.