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
- 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.
- Select an output from previous steps: outputs(list_rows_present_in_a_table')?['body/value']
- Delete a row
- 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
- From: outputs('Run_a_query_against_a_dataset')?['body/firstTableRows']
- Map: I pick simpler names for the titles
- Create CSV table
- output: body('Select')
Step 7 is where I start to have problems.
- Apply to each 2
- Select an output from previous steps: I have tried outputs('Create_CSV_table')
- Add a row into a table (Excel)
- I point to the same Location, Document Library, File, and Table as the file from "List rows present in a table."
- 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?
