I am working on a Power Automate flow triggered from a Power BI button, and I’m running into an issue when trying to add data to an Excel template stored in SharePoint.
The goal to the flow is:
- User clicks button in BI report
- Flow identifies the user by email address
- Flow navigates to user folder on SharePoint
- Flow creates a copy of a excel template into the user's folder
- Flow add data from BI report into the table
- Flow renames the file at the end
When I use Create file to copy the template and then try to use the file ID in Add a row into a table, I am unable to map the columns to the correct fields in the table. The flow will either error out or add a blank row to the template, depending on the expression used in the Row field. When configuring the Add a row into a table step using the ID from Create file, the column fields do not appear—only the generic Row field is available. If I hardcode the file path instead, the column mapping works correctly; however, this creates another issue where the file could be overwritten if the user runs the flow again before it is renamed.
I have tried the below, but thing is working. Do you have a suggestion?
- Using ID from Create file
- Using Get file metadata using path
- Using List rows present in table before adding rows
- Building a manual JSON row object (
json(concat(...)))
- Using
items() inside Apply to each
- Using Parse JSON to structure the data
- Using delays to ensure file availability
