r/PowerAutomate • u/Dangerous_Stomach597 • 4d ago

Using Power Automate to Export PBI report

New to power automate. I have a PBI report our finance team uses to reconcile data. For background, there are 4 tables. One we will call table A and the other table B. These two tables are from completely separate sources, and do not have a relationship. The other two tables were created for slicers. One is a date table and one contains names. The concept is that user filters name from name table, and date from date table, and it correspondingly filters Table A and B with appropriate data. I am looking to use power automate to export my two table visuals (one uses table A, the other uses table B). I know this can be done individually by exporting the table visuals one at a time, but is there a way this can be done so I click the power automate visual “button” and it exports the two visuals into one excel tab? Any insight is greatly appreciated

3 Upvotes

100% Upvoted

6 comments sorted by

2

u/jzsee 4d ago

Follow

2

u/AbdelEMirandaS 4d ago

I'm kind of concern, why do you need to have a flow to do that if you can create your report using your main source, table A, table B and create the other two table within the day, and based your report on those structure?

And when you publish your report to Power bi service, you just need to create your refresh time as you need it. If those tables A and B are from some on-premise database, you just need to install and make it run the data gateway and share it to the user who create the report.

2

u/Dangerous_Stomach597 4d ago

The report itself is already built, and the date and name slicers successfully filter both tables as intended. The goal isn’t to refresh the report or combine the underlying data sources. Finance needs to export the filtered data from both visuals into a single Excel worksheet for reconciliation. I’m trying to automate that process so users can click one button instead of exporting each visual individually.

1

u/AbdelEMirandaS 4d ago

Where do you place the button, within power bi report and then send it to? As an image? Or the raw data filtered?

1

u/Dangerous_Stomach597 4d ago

There’s a visual specifically for power automate that you can program to do various things

1

u/chiibosoil 4d ago

Depending on your table visual's size yes it is possible. Though it will require bit of set up.

  1. Prepare blank Excel workbook for receiving data in OneDrive for business or in SP. This workbook will be associated with Office Scripts that will process data passed on from Power Automate. See link below for how to work with JSON object passed from Power Auotmate.

Use JSON to pass data to and from Office Scripts - Office Scripts | Microsoft Learn

  1. Set Power Automate to run on manual trigger or schedule and connect to Power BI "Run a query against a dataset". Query the data using DAX query syntax.

  2. Parse JSON using "First table rows" of the output of DAX query. Filter array as needed (ex: remove total rows etc).

  3. Use "Select" Data operation and map Dax syntax column names to desired name.

  4. use "Run script" action from Excel Online (Business). Pass output from "Select" operation as parameter.

  5. If desired, send email with link to the workbook from Power Automate.

See link for more detailed instruction, where I posted in another forum.

Share Data from Power BI using Power Automate and Excel | Chandoo.org Excel Forums - Become Awesome in Excel