Hi,
Sorry for the late responses: I was fixing some small issues in the recommended flow design :)
Knowledge base
As we are talking about a lot of different topics in this answer, I'm sharing some articles from different complexity levels that I wrote in the past about some of the processes discussed here, so whoever finds this thread in the future will be able to explore it in more details.
Overview
Here is the flow overview:
For this example, I'm working with the following dataset (from your end, you will work with the data pulled from PBI):
And my goal is to populate the following template:
Copying template file
We start by capturing the file content from the template, with the Get file content action, and then using its output for creating a new file where the table will be populated. To achieve it, you need to add a Create file action, populating the File content field with the dynamic content from the previous action (marked in red). For the File name input, make sure to insert a unique name and to include the .xlsx extension. I usually name it with a timestamp, which can be achieved with a formatDateTime expression (marked in blue):
Populating the copy
Then we will need to loop all records from the variable (in your case, from Power BI) with an Apply to each action. Inside the Apply to each, insert an Add a row into a table action, using the id dynamic content from Create a file action (marked in blue). It will dynamically reference the copy extracted from the template. You can manually type the table name (it uses the same name from the template's table), and for the Row input you will need to use a JSON notation, where the column names will operate as keys (like the hardcoded id, name and logged hours). As value for each of these keys, you must insert the dynamic content from your current iterated record, as marked in red:
Note: for some reason, my Power Automate is representing these records' dynamic contents with the Manual trigger icon. You can ignore it, as these value are actually coming from the records variable.
Optional and extra step: removing the empty first row in the table
At this point, your table may look like this:
The data is there, but you have this annoying extra first row in the table. If you are ok with that, you can skip this step. If you want to remove it, then you can use an Office Script for that.
In any Excel file, go to Automate tab (highlighted in red) and click in New Script (highlighted in blue). If this is the first time that you are creating a script, click in Write code, and then the Code Editor will appear in the right side of your screen (marked in orange):
Replace everything within the the Editor for the following script:
function main(workbook: ExcelScript.Workbook) {
//Script to remove first row from a table, if it's an empty row
//Get table by name. Replace employees_hours for your table's name
const tbl = workbook.getTable('employees_hours')
//Get values from the first row, which is represented in an array with the elements qty corresponding to the number of cols
const firstRowValues = tbl.getRangeBetweenHeaderAndTotal().getRow(0).getValues()[0]
//Test if first row is fully empty. If yes, delete it, shifting the cells to the top
if(firstRowValues.every(e => e === "")){
tbl.getRangeBetweenHeaderAndTotal().getRow(0).delete(ExcelScript.DeleteShiftDirection.up)
}
}
This script is basically accessing your Excel file and checking if the first row of a given table is empty. If yes, it will delete this first row. In the const tbl line of code, make sure to replace employees_hours for your actual table name.
Save your script and let's get back to Power Automate. Out of the Apply to each block, add a Delay of at least 1 minute. This step is important, because sometimes the updates from Power Automate to Excel take some seconds (or minutes) to be executed. I ran some tests and 2 minutes worked great for me.
After that, add a Run script action, using the Id dynamic content from your Create file action as input for File. Select the name of the script that you just created from the dropdown. This action will execute the script for you:
Getting populated template file content
Now let's give some seconds or minutes of delay to Excel and get the content of the populated template. You can achieve it by adding another Get file content action and using the Id property from your Create file action as input for File Identifier:
Sending the email
Finally, we can add the Excel file as attachment to an email. In the Attachments input, set the Attachments Content as the File Content property from your Get file content 2 (the latest one, from where we pulled the populated template data), as marked in red. Also, make sure to name the attachment, including its extension (highlighted in blue):
Testing the flow
After running the flow, an email was received with the report as attachment:
When accessing the attachment, we find the same content from the populated template, as expected (without the annoying empty row):
Let me know if it works for you or if you need any additional help!
-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.
