Export filtered matrix to Excel using Power automate

[kb177]

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 

[ImkeF · Accepted solution]

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 

[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 ...

1) Create an Excel file

2) Create a table in that Excel file

3) 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]

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. 

[kb177]

I 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 

[takolota]

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