Have you ever needed to create a new Excel File in Power Automate and then populate it with data from another source? Maybe a the results of another action, an API endpoint or the contents of another Excel File that has been filtered? I will demonstrate how to build your own flow in 4 actions and then populate a table with many rows from another data source.
👀 Populate an excel file FAST ⚡with Graph API
👀 Create and populate dynamically all file types from Power Apps
👀 Create Dynamic Word Documents with Optional Sections via Microsoft Forms
Want to submit an idea or feedback?
See how you can avoid an apply to each using Office Scripts and without the need to setup a table in your excel file first
00:00 Intro
00:28 Creating an empty Excel File
00:45 Start of the Cloud Flow Build – Getting the File Content
02:15 Creating a new File using the File Content from a Compose
03:17 Create Table Action
04:30 Add a row to the Table with a JSON array
07:05 Saving the Flow, an overview and Testing
08:37 Using an external source to add multiple rows to the Table
11:22 enabling concurrency on the Apply to Each
11:47 Saving the flow and Testing
12:50 Reviewing the flow history
13:34 Summary
#PowerAutomate #Excel #NewExcelFile…(read more)
Convert Word 2 PDF: Word to PDF Converter
Convert PowerPoint 2 PDF: PPT to PDF Converter
Convert Excel 2 PDF: Excel to PDF Converter
Convert an Image 2 PDF: Image to PDF Converter
Convert HTML 2 PDF: HTML to PDF Converter
More Tools: PDF Converter
Want to see how to import SharePoint List or Dataverse Table data into a new excel file quickly and efficiently? 6 seconds for 1000 lines! https://youtu.be/4g8Lh0gzEnc
mine works right up until the create table part. I just get a blank excel file with no tables
Great video!!
Can we change the table layout from blue to any different color using the same template. Do we have to add table with different layout in template file, I tried and got error like "cannot overlap table"? Any advice! Thanks
Great Job!
This just saved me from having a lot headache with Excel
Thank you
How to update multiple rows in Excel using Power Automate?
Great vid!
Quick q. The name of your table didn’t change to “MyNewTable”… I’m trying to change the name of my table so that it’s not named “Sheet1”… your help would be greatly appreciated!!
Impressive video! I'm trying to figure our how to extract form data from surveys that are emailed and have the results put in an excel table with a predefined set of columns. I got it to work only if all the text fields in the survey are filled in. If a few are not required and not filled in, there's no data element, null string or empty separator in the array which messes up the order of the elements when the row is appended to the excel table. Any help would be appreciated!
Is it possible for Power Automate to transpose table values from the source? ie attendance xlsx has student, date and p or a (present/absent) value. I'm envisioning a final sheet with each student's name descending down the left column and a series of 1 – 31 column headers stretching left to right across the top such that the corresponding a/p value might rest in the placeholder created as a result of transposing the original data. Thank you for any thoughts, tips you or others might wish to share. Cheers to you from Canada.
Hi @DamoBird365, Thanks for this, very helpful! I'm building upon this for a payroll system, each site has it's own staff list.
I'm running into a snag though, my create table isn't working, and I'm getting an error:
"The provided drive id appears to be malformed, or does not represent a valid drive."
I've tried the create file "ID", "ItemID" – I've also tried the flow again by including the table in the template that's created – though that presents more errors.
Is this due to licensing (using Business 365). Any thoughts or suggestions welcome – thanks!
Any idea how to create vertical table in excel using power automate?
This video helped me do exactly what I needed to! the flow was failing when I used dynamic values from power automate, when I used your item expression in its place it worked perfectly. Thank you
Hi Damion, thanks so much for your video!
I found this can be applied with Update a row but I stuck at this, do you have any video for the update a row? My flow tested OK with the first few steps, created a new file with existing file content, thanks for your advise in advance.
Such a great video, the content I'm looking for, would like to see how to fetch the email ids from the excel created like above and send a mail to all the recipients…
Great video, thanks for sharing.
One question though: At the step "Apply to each" the flow suddenly stops – or to be more precise – keeps running without completing this particular step. 443 rows and 15 rows are supposed to be written to the new excel file. Any idea what the reason could be?
would there be any way to be able to add a total at the bottom of a column doing this?
Thank you so much damobird, I am looking for exactly the same topic
Thanks for a great video. I ran into an issue though, I want to dynamically create Excel file for each query. Every query is different but Excel file attached to email is always the same.
Power Automate gets the right Excel file, Excel file in Reports library has the correct data. The attached Excel has the right file name but rows in Excel always the same, from the 1st it ran
Thanks as always for these perfect demo 🙂 this is just what I wanted to do 🙂 Keep it coming 🙂
10:31 Hi. I have a small issue in this step. My data source is a Run Query against a PBI dataset. I have a columns with the entrydate.
In all previous steps, I can see in the Output that the matching correctly the data ( Created table fine. Matching Header with data, etc., ) but in this steps all columns in the Excel table are populated with the exception of my date column.
Any ideas with the Excel table doesn't populate this date column?
Thanks
Cant thank you enough! I've been struggling with creating a table after exporting data from powerBI dataset using (Run query against a dataset). all tutorials were pushing to export it as a CSV file. this video is very clear and the jem i was looking for! Thank you
Damien as always is so helpful!
hi I am creating same with Solution and Environment variables. At 3:40 where you are putting the Document library name I putting the Environment variable for it ..I getting error like The provided drive id appears to be malformed, or does not represent a valid drive.. It because it does not take the GUID of Library instead it take some id of the drive… any help to make the Document library dynamic ?
Hi. Thanks this video has been extremly helpful. I have a question. I need to add a last step that is Send an email (v2), but unfortunately, the "Attachments Content" is not giving me the option to select the file done. Any idea what should I do? Thanks
Great video! I am trying to do a similar thing, the only difference is from an excel I am trying to create multiple excels based on some filtered data. My flow is running fine till except the last line where I am adding row to the table. Its failing with the error "message": "The request entity's media type 'text/plain' is not supported for this resource." Any idea what this can be about?
Very useful, Thanks! Is there a way to populate a bunch of rows, for example from the output of a sql query, where I use a different action? I'm guessing this Add Row action is going to open and close the excel file with each insert. I'd use an Apply to Each but would prefer it not open/close the file for each operation if possible. Thanks!
Thank you, Damien. Video was very helpful.
But I see the limit of Apply to each control is 5000.
Can you please explain how to add more than 5000 rows into Excel table? I am using Paging Cookie method to get more than 500 rows and when used apply to each control, it is adding the last iteration of Do until.
How can I overcome this to add more than 5000 rows?
Thank you DamoBird. I prepared an excel template with some formatting (font size, column with, wrap text etc.) and applied your "Split workbook into multiple worksheets based on key column" video. The result is great, I get splitted workbooks with nice formatting. However a template should not include a table, power automate does not like that 🙂 because powerautomate cannot paste one table onto another.
Great stuff. What video software do you use for creating these videos?
Massively helpful. Thanks!
I ran into a snag. After creating the file, I attempted to open it. I received the following error message…"This workbook couldn't be opened because the file format may not be matching with the file extension."…. Afterwards the file closes. How can I overcome this issue?
I love your work mate. Keep teaching and I will keep learning…
Helpful tutorial thanks however I have trouble replicating this. When I use the create file action and choose the "Site Address" i see the SharePoint site I want. I select the folder and then create the file using the File Content. This works. However then when I go to create a table in the new file using Create Table and drop down Location I get a completely different list of sites some starting with "Group – " but none of them are the one I used in the Create File action. I tied using the same SharePoint site, Document library and file (including path) from the Create file plugging them into custom value but this fails with error 400
What if you do not know your "table range"? meaning, I am expecting an excel document that is not yet a table. Upon receiving this file, I want for power automate to identify the range within the file and create a table. is this possible?
You are aStar , can i use get file action to create an excel file from a template which i used several formatting and colors in it?
Great !!!
Hi. I love these videos and am learning so much about automation. One thing that I am struggling following this is how to format the new excel file / table in the flow? Some of my data requires a wider column, but I can’t work out how to automate any adjustments to column width etc.
The newest version of power automate has Body instead of Row as on 5:56, do you know how to include teh values into the row with this changes?
https://youtu.be/RB_ySjhm9Sg?t=356
I do like this video as well. I'm trying to do something similar (Create a dynamically named worksheet, create dynamically named table) but I'm trying to parse a pipe delimited file into the table. I can create the worksheet and the table gets created, however, when I try to build out the "add row into a table" I get stuck. When I supply the table "name" from the Create table step, it won't recognize the columns that I've defined supplied in the create table (those columns do get created). It's still expecting *Row. I tried to build out a json statement as in your example, but I'm not sure how to finish out the field name (I tried using the column names I defined but still I'm getting an invalid json). Any thoughts?
In Scope-ExcelFile>Add a row into a table, every time I finish populated the rows section and save and exit the flow, all of the json array is disappears. Is this normal? How do I keep it there?
excellent video, I have a problem I want to enter data from the sharepoitn filling my excel table but I can't find a way to do it.
your help please. I get this error
A value must be provided for item.
clientRequestId: aa48312f-61e7-4699-aa6f-44f2cd6eeec0
When I add new Json Array into it then save and run the flow again, it says that the file we created is locked for shared use. What should I do for this?