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. In order to work with python at transforming data you need a Personal Gateway to make it work. Python only works like that. For the alternative you can use the power bi rest api. Execute a DAX query to the table and reduce unnecessary columns to prevent limits.
This is the request: https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/execute-queries-in-group
You can make it work with SimplePBI library at python to make it easy or use Power Automate.
I hope that helps
Thanks ibarrau
Can I use personal gateway for pythin bit in conjunction with Gateway I am currently using for Snowflake refresh? How do I setup so I can use the 2 different Gateways together?
Thanks,
w
Like v-tsaipranay said you can't do both of them. Just configure the Personal Gateway for the whole semantic model. It should work. It's not the best approach for snowflake to download at the VM notebook and upload again but it will work.
I hope that make sense.