Forum Discussion
Helper IIExport filtered matrix to Excel using Power automate
Hi,
I have 3 matrix tables in seperate pages on my report, I'm looking out to export the same to excel into one workbook into seperate sheets
I dont have access to power apps, only power automate is available
I've tried export to csv each file but I'm not getting any flow to merge all 3 files into .xlsx workbook into seperate sheets
my data is being saved into onedrive but I'm unable to see the files directly, I'm creating a dummy note pad file or folder etc and deleting it then only those csv files are showing up
Oh I see: You have entered the formula into the wrong window. That will indeed just interpret it as text.
Instead, click on "Add dynamic content" and then choose the "Expression"-window for the formula.
7 Replies
- ImkeF
Community Champion
Hi kb177 ,
You can use the "Run a query against a dataset"-action to fetch the data from Power BI.
You can retrieve the query code by turning on the performance analyzer, start recording and refresh the matrix visuals. Then copy the query and use it in the Power Automate action.
kb177I want to export to excel not to csv, After pasting the query in the below flow what should be done next
I dont want to create csv table and create a file from that
From there you can try converting CSVs to Excel with something like this template:
https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-To-New-Excel-Table/td-p/1826096
- ImkeF
Hi kb177 ,
to make using the result of the query against your dataset as easy as possible I'd recommend to transform the matrix visual into a table visual instead. This ensures that only one table is returned by the DAX-query and you then therefore reference it unambigously.
In your flow, you create a compose action and use this formula in it:outputs('Run_a_query_against_a_dataset')?['body/results'][0]['tables'][0]['rows']This will return an array. Based on this, you can follow the instructions in this video to: Power Automate Export to Excel | Dynamically create Table, Columns & Add Rows to Excel | Send Email - YouTube1) Create an Excel file2) Create a table in that Excel file3) Fill in the rows from your array into this table- kb177
Should I add run a query against data set and then compose flow ?
if that's the case then compose action is just returning the formula as text in output
- ImkeF
Oh I see: You have entered the formula into the wrong window. That will indeed just interpret it as text.
Instead, click on "Add dynamic content" and then choose the "Expression"-window for the formula.- kb177
Thanks for the correction, I have added all the steps as per the video till add a row to table but not sure why data is not getting added just table is expanding
