Exporting data from Power BI to Excel file stored in Sharepoint

[powerquest1234_]

EDIT 10/20/2023: I found a workaround, please scroll down to my response.

Summary: Does anyone have advice for using Power Automate to export data from a Power BI dataset onto an Excel file that is to be automatically updated every morning?

My goal: I am trying to send an alert to users to let them know when a new problem is assigned to them.

In my testing, I can get the steps in the linked process to work ( https://tomriha.com/send-users-single-email-with-multiple-excel-rows-power-automate/ ). If you read the attached link, this Power Automate flow lists rows present in a table, then e-mails users a filtered HTML table with rows containing their e-mail address. In my testing, I can get the steps in this flow to work.

The tricky part is automatically exporting data from Power BI to an Excel spreadsheet every day so the linked process above can work.

My data would come from a Power BI dataset. This is how I am trying to get the fresh data from Power BI into the Excel file:

EDIT 10/20/2023: I found a workaround, please scroll down to my response. Saving the original post below the dotted line, but the solution I found is in my response.

------------------------------------------------------------------------------------------------------------------------------

• Recurrence: Runs at a specific time ever day
• List rows present in a table
  1. For Location, Document Library, File, and Table I point to a table on a .xlsx file stored in Sharepoint.
• Apply to each

The purpose of step three is to delete the previous day's data from the Excel spreadsheet.

1. Select an output from previous steps: outputs(list_rows_present_in_a_table')?['body/value']
2. Delete a row
   1.       I point to the same Location, Document Library, File, and Table as the same file as "List rows present in a table."

• Run a query against a Power BI dataset
• Select
  1. From: outputs('Run_a_query_against_a_dataset')?['body/firstTableRows']
  2. Map: I pick simpler names for the titles
• Create CSV table
  1. output: body('Select')

Step 7 is where I start to have problems.

• Apply to each 2
  1. Select an output from previous steps: I have tried outputs('Create_CSV_table')
  2. Add a row into a table (Excel)
     1.       I point to the same Location, Document Library, File, and Table as the file from "List rows present in a table."
     2.       The "add a row into a table" step also has fields with the simple titles I provided in step 5b Map. I have tried putting here body('Create_CSV_table')[0][1], body('Create_CSV_table')['Mapped column name'], and others. Power Automate automatically suggests that I reference columns from the output of Step 2, but that seems strange to me.

I saw Curbal's video ( https://www.youtube.com/watch?v=WsbIXJDhC_o ) where she recommended using the Update File in Sharepoint action to update a xlsx stored in Sharepoint, but when I tried a flow based off of that, I kept getting error messages that the file format and file extension did not match. For that one, I tried this:

1) Recurrence: Run at the same time every morning

2) Run a query against dataset

3) Select

- From: outputs('Run_a_query_against_a_dataset')['body/firstTableRows']

- Map: Map column names to simpler names

4) Create CSV table:

- From: body('Select')
- Columns: Automatic

5) Update file
File content: body('Create_CSV_table')
This flow always runs successfully, but when I go to check the file in Sharepoint or Microsoft Excel Desktop, I get error messages saying that the file format does not match the file extension. 

Does anyone have advice?

[lbendlin]

You cannot create an empty Excel file.  Either you copy an existing file or create a file with boilerplate XML.

[powerquest1234_ · Accepted solution]

I ended up finding a work-around for what I'm trying to do. Here is the flow I ended up using.

First, manually create an Excel .xlsx file with your desired headers formatted as a table. Save this .xlsx file to Sharepoint.

Next, make a "tester flow" where you will get the JSON for your Run a Query Against the Dataset action.

This "tester flow" will only have a trigger (like an instant trigger) and a "Run a Query Against the Dataset" action.

After you set up the tester flow, test your "tester flow."

On the "Flow runs" page, scroll down to the run a query against a dataset action to Outputs.

You should see a box that says "First Table rows."

Copy the text inside this box and save it for later.

1) Recurrence action: This is a trigger that tells the flow to run at a set date/time on a regular basis.

2) List rows present in a table (Excel online): Input the information for your Excel file stored in Sharepoint

3) Apply to each action: This step will use step 2's output. You will add a Delete a row action to delete the previous rows in your Excel spreadsheet. 

(I found steps 2-3 in an online tutorial but I unfortunately cannot find it. I will update the post with my link as soon as I do)

3) Add a Run a Query Against the Dataset step to extract data from Power BI.

4) Add a Parse JSON step. 

a) your content will be outputs('Run_a_query_against_a_dataset')?['body/firstTableRows'])

b) Click the "Generate from Sample" button at the bottom of the Parse JSON step, below the blank Schema bin.

c) In the pop-up window, paste the output you copied from your "tester flow." 

d) Power Automate will then populate the Schema bin.

6) Add an "Apply to each" action

a) Output: body('Parse_JSON')

b) Action: Add a row into a table (Excel business)

i. Input the file information for the file containing the table you wish to overwrite.

ii. For the column headers that should automatically populate, input an expression such as items('Apply_to_each_2')['Table[ColumnName]']

iii. I did not select an advanced options.

A colleague showed me the Parse JSON steps. If these steps do not work for you, I was also researching this article prior to trying Prase JSON: 

 https://www.tachytelic.net/2021/02/power-automate-parse-csv/