How to export an Excel file (not CSV) via Power Automate button?

[PowerAutomater]

I have a power automate button/visualiser in Power BI which triggers the following flow:

This works fine except I have hit the CSV row limit, and for some reason I can only get 15000 rows. I have read that with an xlsx you can have more rows than a CSV, so I have tried to adjust the flow but cannot work out how to create an xlsx file instead of a CSV. If I change the file extension in the "Create a file" action the file becomes invalid and cannot be open, and I am not sure what to replace the "Create a CSV table" action with.

Can this be done?

[lbendlin · Accepted solution]

The limit is elsewhere - the size of the Base64 encoded string.

You can create the Excel file and table directly and then you can use the Graph API to batch upload rows into the table. 

Note that your flow action seems to be decoupled from your Power BI user experience. Make sure you include the expected filters in your DAX queries.

[PowerAutomater]

Sorry shouldn't I be creating the excel table after running the query? Since the table needs to contain the contents of the Power BI query? Also you have included a post action whereas I actually need to create an xlsx file.

[lbendlin]

Please check my screenshots again. The first two steps create the Excel file.

Then it is a matter of preference what you do first. But assuming you need to run multiple queries (to avoid hitting the row limit) then creating the table first makes more sense.