Create a new Excel File in Power Automate and dynamically populate with a Table and Rows

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.
Watch my latest video 👀 Graph vs Office Scripts and a new cleaner method of creating a blank excel file • Create Excel File and ...
👀 Populate an excel file FAST ⚡with Graph API • Create and Populate an...
👀 Create and populate dynamically all file types from Power Apps • Create Word, Excel, HT...
👀 Create Dynamic Word Documents with Optional Sections via Microsoft Forms • Create Dynamic Word Do...
Want to submit an idea or feedback? forms.office.com/r/4EqE7VHVfH
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 • Create & Populate Exce...
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 Please buy me a coffee www.buymeacoffee.com/DamoBird365 ☕

[@DamoBird365]

I have an updated version (Feb 2024) with both Graph API and Excel Office Scripts here 👉kzbin.info/www/bejne/naXPnJ-wnpuAesk 👀

[@cgbeale]

Thank you for such an excellent video. I was lost on how to dynamically create a new file, until I watched both of your videos. I love the pace and how you clearly explain everything especially showing the expressions.

[@DamoBird365]

Cheers Chad. Appreciate your feedback. Do let me know if there is anything else I could cover in future videos 👍

[@rkobiggestfan7]

I can't believe it took me a long time to find this video. This is what I was looking for! Amazing. Thank you very much! This was helpful

[@DamoBird365]

Cheers Alessandro. This means a lot! If you’re ever looking for a solution, drop me a dm as I’m always looking for ideas. 🙏

[@naderhayek3310]

Excellent video - very detailed and easy to follow - thank you for taking the time to share

[@Kralnor]

Microsoft really should provide better support for creating a new Excel file, but your workaround is a great idea!

[@rhodrithomas6040]

This has been super helpful, thanks! Trying to build a flow for creating distribution lists for documents in a SharePoint library and this is getting me a long way down the line!

[@jherschel]

Thanks! I got stuck trying to copy data from one excel table to another and creating a new file helped me through it! Also, I had 26 columns and didn't realize if you dynamically entered the row table Name in add row to table, I was able to copy ALL rows using the row field and current item!

[@nandnandan4198]

exactly what I needed. Keep your work up and running.

[@MitchTorres]

Thanks as always for these perfect demo :) this is just what I wanted to do :) Keep it coming :)

[@weizhang429]

Thanks for this. Exactly what I'm looking for. spend hours trying to figure this out!

[@aT0000my]

Thanks Damien, very helpful!

[@mamadykonate8253]

Great Job! This just saved me from having a lot headache with Excel Thank you

[@notonurnelly]

Really useful Damo, this use case with a few tweaks will be really helpful on a project I am about to start.

[@lucapiu]

Thank you so much, just what I needed!

[@wigglwrrm]

Massively helpful. Thanks!

[@amaniramahi]

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

[@DamoBird365]

Thanks 🙏 Amani. Good luck with your automation.

[@anhduong8484]

Thank you for sharing this video. It was very helpful.

[@sebastianmartinezarenas3696]

Thank you for your content!

[@GregDolan]

Another great video 👍. Please keep making these, I think there very helpful 😀

[@JonDoesFlow]

Nice one, very helpful, thank you Damo !

[@XavierAponte-eq4hu]

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

[@DamoBird365]

No problem Xavier, thanks for letting me know and glad to be of help 👍

[@lakshyasharma1715]

Thanks this was a huge help

[@lfish894]

Damien as always is so helpful!

[@user-hb3gb9zn7n]

Thank you very much for creating this

[@sudarshanreddy6969]

Thank you so much damobird, I am looking for exactly the same topic

[@VaibhavSharma86]

Too good. Really helpful. Cheers.

[@edjk6278]

I love your work mate. Keep teaching and I will keep learning...

[@DamoBird365]

Thank you.

[@rickcoterie5700]

I think you are the best teacher out there! Great stuff! Exactly what I needed as one of the steps. Going to look at your other videos for next steps. My needs and looking forward to finishing this fun one - 1 - Run Stored SQL Procedure 2 - Create Excel File(s) from the file 3 - Filter\Compare against SharePoint list that has the ID and email names of those needing the report 4 - Create charts required and add to template 4 - Create Word File 5 - Save as PDF 6 - Email that file to the user from SP list 😁

[@myintzusan9159]

Thank you so much! This tutorial video has solved my problem. Thanks heaps again!!

[@DamoBird365]

Amazing 🤩 good luck with the rest of your automation 👍

[@the_n8]

very helpful and to the point. Thank you

[@DamoBird365]

You might want to also check out kzbin.info/www/bejne/naXPnJ-wnpuAesk

[@florian1054]

thank you for your video, really appreciate it !

[@financiallyfreegenz]

Thanks for this video. Very helpful in my projects

[@DamoBird365]

Make sure you check out the latest video pinned in comments too. Would love to hear from you with other use cases.

[@yiubili1]

Great video, will you do an video that exactly show how to draw data from excel and paste to other excel with condition please? Would be great if the table range are also flexible as well...

[@ngunghuong6938]

Thank you so much. This is really helpful for me.

[@Sudharaaj]

Its almost an year, but super helpful. Thank you!

[@SaifUlislam-zq2qn]

Thanks God. Finally found this video. Great content. Actually I am doing little bit more advance things like I have big file for creating data into SharePoint list but it takes age's. So I was looking for something that splits the big excel file into smaller files then call the flow for each loop and providing the newly created excel files. Which reduced my creating data into SharePoint super fast

[@vijaymunavalli335]

Well explained🎉

[@apodostroiec]

Solución simple, funcional y elegante. Excelentemente explicado. Y mejor instrumentado. Muchas, muchas gracias.

[@DamoBird365]

Thank you 😍

[@quang110289]

this video save a lot of time for me. thank you

[@44bthoj44]

Awesome presentation.

[@NouradineMahamatArabi]

Thank you! This video is all I've wanted for the past 2 weeks. But I don't know what you put in the first compose (filecontent) because I can't create a table and my excel file is unusable. I want to export the information from my PowerBI report to my Excel file

[@snooki9]

Thanks man !

[@lxz86]

very useful case ， thanks a lot

[@eduardvaldivia]

It help me a lot, thank you very much!

[@DamoBird365]

Cheers Eduard.

[@nlsfrck2633]

Thank you very much ... that is brilliant. Have you ever been running into problems which seem as if the excel file is flushed/saved only after the flow ends? My flow (1) creates the excel, (2) populates it with some data from a sharepoint list, and (3) sends and email with the excel attached. The excel attachment from the email is not complete and even delays, check-out, etc. didn't help. It looks as even the excel on the sharepoint is completed only at the end of the flow.

[@franknielsen3219]

Brilliant video.😀😀😀

[@ColinBaconLabs]

Excellent :-)

[@tranbangviet]

Thank you so much !

[@DamoBird365]

No problem at all.

[@dlep9221]

Great !!!

[@Gwill1983]

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.

[@DamoBird365]

You would need to look at office scripts if you want to automate the width of the columns.

[@johnfromireland7551]

Fantastic! Creating an Excel file using Power automate. I think there are size limitations to take into account, however. 10MB? One thing that would be super cool, in the future, is if Microsoft would create a "Add to Data model" action. So, for large data files (millions of records) the Flow would add that huge array/data source into an Excel or Power BI Data Model. I suppose the question, here, is "Does Power Automate have Action(s) or methods that allow for very large data sets to be handled?"

[@DamoBird365]

That’s interesting John. I’ve read that there is an inter action limit of 100MB but MS have extended this to 1GB with “chunking”. I’ve read of 25MB limits here docs.microsoft.com/en-us/connectors/excelonlinebusiness/ albeit in the above proof of concept I am not pushing these by any means, I’ll admit.

[@user-qm1si6oj6y]

Thank you so much, very helpful. Question- instead of using a blank excel book in get file content, could you use a populated or formatted excel file? I'm trying to have it create file from a template and then populate data based on the power automate workflow and trigger. Any help would be appreciated!

[@DamoBird365]

Absolutely. Get the file content, create file as a copy and then populate the template. I’ve a few examples on my channel. Check out kzbin.info/www/bejne/eYupfZ2vbc14rpIsi=03qZYhH3E6DG4xjZ

[@DeividFaulstich]

goood! thanks!

[@dmstar101]

Hi, This was a brilliant video and It has helped me heaps to get around the create Excel dilemma I was having. I guess that this type of flow can take the excel data and populate a Json script to be uploaded into an API?

[@DamoBird365]

Hey Darren, most things are possible. Ai don’t understand your need to create an excel file first to the create a json array? I do have lots of videos on json and select though 😉

[@NiftyNinjaa]

@DamoBird365 This is a perfect solution for a problem of mine. I used this method and implemented it for collecting monthly data through forms (recurring). Instead of Array, I just used Forms output. whole data is processed and stored in SharePoint. Thanks a lot for the insight! keep it coming :)

[@DamoBird365]

Thanks Chandrasekar. I really value your feedback. Let me know if there are any ideas you would love me to solve.

[@prajaktadoshi9855]

Hey, You are awesome. thank you so so much... I was working on one of my company project and I was facing error while creating file even though i used dynamic field as file name. But your idea of adding unique file name of date, time formula really really helped...

[@DamoBird365]

Thank you for letting me know. I really appreciate the feedback. If there are any ideas for content, please drop me a message below.

[@prajaktadoshi9855]

@@DamoBird365 hey hi thank you for your response. About content, If you can create a flow for power app, to pass value from "one form" to "another" For example i want to pass customer_ID in all my "power app forms" which is connected to different share point list....

[@DamoBird365]

@@prajaktadoshi9855 do you want to drop me a bit more detail / description to [e-mail] with a use case? Thanks

[@prajaktadoshi9855]

@@DamoBird365 yes sure will do

[@zineddine]

Great video and I like especially the way you used Json to create and populate the excel file. To become good in Power Automate, should JSON be my priority of things that I should learn ?

[@DamoBird365]

Definitely. I have a video on a brief overview of json. But it is worth being able to visualise json, understand arrays and object.

[@yojie120]

Thank you so much for this tutorial. I do ran into a bind, whereby the data that i am trying to add into the Excel column is a multi-line text column from a Sharepoint list. It currently only shows the first line but not the rest. Would you have any recommended steps on how to move the full content of this column? Thank you in advance for your reply :)

[@DamoBird365]

I’ve not tried to be honest. Might be worth logging a call on the support forum powerusers.microsoft.com/

[@vincentDev84]

thanks for the video .. one question: concurrent in apply to each is not applicable for inserting row in ordered sort right?

[@DamoBird365]

You could be right. Have you seen office scripts or graph? Create Excel File and Add Rows Fast | Graph API | Office Scripts | Power BI | Power Automate kzbin.info/www/bejne/naXPnJ-wnpuAesk

[@MohammadAli-ts2ev]

Thank you for the informative video. In Power automate/Flow, how can we set the value of cell A2 of a table (of Excel Online for Business) to a variable?

[@DamoBird365]

Take a look at Excel Office Script Tutorial - Populate an Excel File Dynamically from Power Automate kzbin.info/www/bejne/h2iqfYSkq699fJI

[@user-sj3zd3te9o]

Hi @DamoBird365! This video was really helpful. Thank you! Quick question, if I wanted to attach the contents of the file to an email, how would I do that? I've been unable to figure out what the file content should be. I've tried a lot of the dynamic content, but I get an error that that the file cannot be opened. One of the dynamic contents worked, but it was the blank file. Any help is appreciated!

[@DamoBird365]

You’ll need to get the file content. In my latest demo of something similar, I show you how kzbin.info/www/bejne/naXPnJ-wnpuAesk

[@GabrielAGilO]

ca you do this using one drive instead of share point, the setting in share points are different than in OneDrive

[@mattkoch3117]

I cant seem to get it to accept thecarray i typed into the "add a row" field. Is there anything that could be tripping it up? It looks the same as yours with the colon and the quotes

[@KarthikeyanBalasubramanian]

A great video indeed... Thank you so much.. I am looking for a similar solution, but to update the existing table with new records without appending (deleting old news and updating with the new ones) and maintain the same file name. How to achieve it ?

[@DamoBird365]

You could explore office scripts and graph - both of which would allow you to batch update an excel file. I would suggest you also ask on the forum to get more ideas. I have videos on both Graph API and Office Scripts to populate an excel file fast.

[@DamoBird365]

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! kzbin.info/www/bejne/apibfZtmnN94pMU

[@murti82]

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.

[@DamoBird365]

You might want to explore office scripts, I have a few videos but here’s an idea learn.microsoft.com/en-us/office/dev/scripts/resources/samples/copy-tables-combine

[@llssssll]

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.

[@DamoBird365]

Hi Sulamita, I don't understand your question. This flow creates a new excel file, do you wish to update an existing row in the new file? This might be a question for the forum as I don't have a video for that scenario powerusers.microsoft.com/t5/Forums/ct-p/FL_Comm_Forums

[@rupikayelamarthi7219]

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...

[@DamoBird365]

Hi Rupika, glad to hear this has helped. If the emails are already part of your data, you could use that to create a semi colon separated list of email addresses. Check out kzbin.info/www/bejne/fWS0Yoqrj7mKq7s

[@seemunwong9364]

Hi Damion, thank you for sharing the video. It has been a great help for me, a newbie in using Power Automate. Unfortunately, my column names are excessively long and the flow failed with 'bad request'. Any chance I can use an excel template containing the headers (with long names) at the step of 'Get File Content' and paste its JSON under 'Compose'? Is there a different to go about this problem that you can suggest please? Thanks again.

[@DamoBird365]

You could just get the file content and create a new file straight from that 👍

[@seemunwong9364]

@@DamoBird365 Thanks. If that's the case, how do I map the column names with the 'Get Items' values?

[@thiagolopes1132]

Thank you so much for this amazing tutorial. I learned a lot. I would like your help if is possible. I used to receive lots of excel files from different people. They usually post these files into a SharePoint library and it is always in the same layout/format, of course with different information. I always need to pick those files and copy the information into a new standard layout. I'm trying to figure out I way, using as a trigger “When a file is created in the folder”, Power Automate read this file content, identify a table, get all data, and after that start to populate the new template. I believe this tutorial provides a halfway to do that. So Where do you recommend I can study and find a way to do what I described? Thank you so much again. Sorry about my English. It is not so good.

[@DamoBird365]

Hi Thiago. What you’re describing definitely sounds feasible. There is an action to get table(s) in an excel file. I’m happy to help if you want to drop me a message to [e-mail].

[@Ratheesh_G_Nambiar]

Hi, My requirement is to automate the process of handling emails with CSV attachments from SFDC. Upon receiving such an email, the system should automatically create a new Excel file based on the attachment's content. Subsequently, a new worksheet should be generated within the existing Excel file, and the data from the newly created file should be populated into this worksheet. Any assistance with implementing this requirement would be greatly appreciated.

[@croti5391]

Great video! I have to extract data from an aggregate excel file on a daily basis. I wonder if this flow can help.

[@DamoBird365]

This might help too kzbin.info/www/bejne/apibfZtmnN94pMU

[@berouk1955]

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!

[@DamoBird365]

Might be one to post on the forum powerusers.microsoft.com/ as it will be easier to share further details and screenshots. It’s not clear what format the form data is but most logic is possible.

[@jackfreudenheim4846]

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!

[@DamoBird365]

Have you seen or heard of office scripts? This method is far more efficient kzbin.info/www/bejne/apibfZtmnN94pMU

[@jackrealdrummer]

@@DamoBird365 I just watched that video and it's brilliant, thank you! I've never done scripting in Excel but I had the pleasure of being one of the early adopters of Visual Basic way back in the day, so this is all quite familiar. In my case I'm calling a stored proc in Sql Server, but it looks like your dataverse example will provide me with what I need. I'm going to try it now. Thanks again!

[@DamoBird365]

@@jackrealdrummer nice one. Let me know how you get on. The Microsoft docs are very good for office scripts.

[@ankitsharma1943]

In my scenario i need to add the title in the first row in the merged cell and then add the data. how we will do that?

[@mariushernandez6112]

Your guide is great! I'm just struggling with a couple things I hope you can help me with: 1. I'm making this flow inside a loop where I create different Excel files and send them to different recipients through e-mail... the issue is that although the file is generated and filled with all the rows that it should contain the file is being sent with only the first row 2. It's awful to see the Excel table created with fixed column width, is there a way to autofit the column width so it can be better at sight?

[@DamoBird365]

Not sure about your single row. With regards to the auto fit columns, there is a function. Try using the recording tool and copy the code 👍

[@JONODO12]

Hi there, this is a brilliant video- the only issue I am having is when I want to introduce Environment Variables. The Document Library input seems to be the issue; I selected the "Documents" option from the dropdown; after testing the flow (in my dev env) it changes from "Documents" to a drive id. When I have saved the drive ID to an EV the flow fails. Oddly, the drive ID for my Documents folder seems to be the exact same in my Dev and Test env despite referencing different SharePoint sites and lists. Any ideas/help?

[@DamoBird365]

Could you use SharePoint rest to get the drive id? _api/v2.0/drives?

[@jeanegerber9775]

Damion! You are freaken brilliant!! Thank you so much for this. I am using your approach and tweaked it with the trigger "When a new Form is submitted" - getting the response details and dynamically opening up an Excel file for each response and populating the data submitted into a table. I however keep on running into the same problem and cant figure out how to resolve the error = "Another table already exists with the name 'DemoForm Submission' or the name is invalid. Error Code 400". Any guidance on this would be super. have been at it for 3 days now (face in palm)

[@DamoBird365]

Does your template include the table name? Or do you create it once the file has been created? Are you using ‘enter custom value’ on the add row to table action?

[@jeanegerber9775]

@@DamoBird365 Thanks so much for replying! My Flow at the moment is: [Trigger] When a response is submitted -> Get Response Details -> {Compose} your trick to input the File Content -> Create File with Filename using FormatDateTime and File Content linked to the Compose Output -> Create Table {File* linked to dynamic SharePoint Id - as you had it} -. Add row to table {As you had it - the only difference is my row data is populated from the dynamic Forms submission data. I would like the workbook to generate upon submission (Doing this) and then to also generate a table and populate it with the form inputs for further analysis. I made my table name a dynamic input [Company Name] which info is received from the form submitted... I have also just made it a normal text as you did but makes no difference as I still get an error. THANK YOU SO MUCH

[@shubhamgupta-sd9kd]

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

[@DamoBird365]

You can change the colour 💯but it sounds like you are creating a table on top of another table, which isn’t possible.

[@shubhamgupta-sd9kd]

​@@DamoBird365 yes so how to approach it could you please share some details how to do so?

[@angelazou1530]

Thank you so much for this video!!! I have a question - is it possible to format the excel table automatically? For example, is it possible to highlight a few columns? Or merge and center several cells?

[@DamoBird365]

Yes, with office scripts. I’ve a few videos on the concept but you can use the recording tool to create a script that will do the formatting for you. What’s your use case?

[@angelazou1530]

@@DamoBird365 Thank you so much. I tried script and it worked very well!!

[@powerbi9048]

Great! How to create excel file when new data in SharePoint List created ? I need to create excel file one by one of data in SharePoint List. Thank you.

[@DamoBird365]

Hi, can you share with me a bit more detail to [e-mail]

[@pranavsalunkhe3501]

Hello while adding the table name from a variable or from the created file it does not take the name for the table. It takes a default name like "Table1" how to fix it. please guide

[@user-zb6wo9kg4j]

@DamoBird365 am facing one issue when concurrency control is enable . Lets suppose there are 100 records and Degree of Parallelism is set to 30 then in my excel there are only 30 rows . If I change the Degree of Parallelism to 50 then I will get 50 records. Seems like it is due to some locking while inserting in multiple threads. Have you also face this issue ?

[@DamoBird365]

The main problem with concurrent Excel actions is that each one can lock for up to 6 minutes. You are better looking at my videos on using Graph API or Office Scripts to populate an excel file fast 👍

[@PhilHughes-qh4nv]

Hi. Do you have a video going into more detail on how to do this when your original data is already in an excel file (and you want to create a new excel containing your filtered data from the first one) ? You mention it at 12 mins and 25 secs but didn't go into any detail on how to do it and I got lost. Thanks in advance.

[@DamoBird365]

I’ve done a split workbook into multiple worksheets and also a newer video on populating an excel file here kzbin.info/www/bejne/naXPnJ-wnpuAesk. What’s your exact use case? Feel free to reach out with more details.

[@samiarebi5401]

Great stuff. What video software do you use for creating these videos?

[@DamoBird365]

These days camtasia, albeit I haven’t created a video for a while as I’ve been fairly busy ☹️

[@learntoautomate365]

Hello Damien, Appreciate your tutorials! They are so clear and helpful. I'm trying to create a flow that automatically (1) creates a new subfolder (2) Copy an excel template into that subfolder (3) populates this excel file from another. I really can't figure get the first part of the process to work. Any hints?

[@DamoBird365]

If you watch my latest video, I show you how to create folders using PowerAutomate. kzbin.info/www/bejne/r4Ski6SqeJugjsk. Hope this helps, please let me know.

[@learntoautomate365]

@@DamoBird365 Thank you for your prompt reply. I am not trying to create a folder, but a new subfolder that goes into an existing subfolder that is already located in an existing folder (if that makes sense?). I want the trigger to be when a row is selected. Thank you for any advice.

[@DamoBird365]

I do folders and subfolders in the video 😉. You can try the forum powerusers.microsoft.com/ or drop me an email [e-mail] and I’ll try to help.

[@learntoautomate365]

@@DamoBird365 Thank you! You are so kind. I'll check it out and reach out if I can't figure it out!

[@gtigerh]

Hi, I see that your video is showing inserted text file with the data as output. Would like to know if I'm to copy data from my data in another excel table that has more than 1K line item how can I input them in the output?

[@DamoBird365]

Hi, you could use list rows and then select to create an array in the correct shape, before passing that to compose. Office scripts and Graph api enable you to load data into excel too. Each with their own pros/cons and learning.

[@Inno94709]

Thanks!

[@DamoBird365]

Cheers Jay, appreciated 👍

[@Inno94709]

@@DamoBird365 as a seasoned developer in vba, pad, pacf andl more, it's truly astonishing to me that this simple thing, create a dynamic xls file and then update it, is so ridiculously difficult. Without your vid, I was reduced to ludicrous measures to replicate this behavior...

[@DamoBird365]

@@Inno94709 it’s worth pointing out this video too 👉 you can create an excellent file using Rest API Create Excel File and Add Rows Fast | Graph API | Office Scripts | Power BI | Power Automate kzbin.info/www/bejne/naXPnJ-wnpuAesk

[@Inno94709]

@DamoBird365 great! One other problem I can't solve, I do an oData GET with application/json, can dump results to email and see query is working, but PA filter, parse json all fail because it says ala 'must has array but u have object' 😞 . So I can't dump GET results to Excel file (which is the whole point)

[@DamoBird365]

@jaymumford3222 check your history. Have you used {} instead of []?

[@ErikNordblad-pt3to]

Hello! Thanks a lot for making this video, I am sort of a rookie in Power Automate but this helped me a lot. My case is we have an excelfile contatining data regarding 13 different customers. I have been able to extract all rows from one of the customers and presenting them in a new excel file. How would you go about dealing with the rest of the rows? The goal is to create 13 new excelfiles containing only rows of data from a specifik customers. Is there a smarter way than adding 13 different filter arrays, one for each customer after an "List rows present in a table"-action? And then 13 different Create a table-actions?

[@DamoBird365]

You can populate files fast with this method : Create Excel File and Add Rows Fast | Graph API | Office Scripts | Power BI | Power Automate kzbin.info/www/bejne/naXPnJ-wnpuAesk

[@DamoBird365]

And combine it with something a bit like Transform Excel Data into HTML Table and Email: Power Automate #PowerAutomate #Outlook kzbin.info/www/bejne/pqitaGage6qnerM

[@SiyamandRashid]

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?

[@DamoBird365]

Don’t see why not 👍

[@DamoBird365]

Or write an excel script that formats the file for you?

[@SiyamandRashid]

@@DamoBird365 thank you very much for you always support, yes I tried what you explained in your video on my template and it worked! Any resource you recommend on how to write the script for learning purpose ?

[@DamoBird365]

@@SiyamandRashid here are some options kzbin.info/www/bejne/gmeanmutnMlnd80 or Office Scripts kzbin.info/aero/PLzq6d1ITy6c2_qM_ocYDtEaENrqi92YmM

[@SiyamandRashid]

@@DamoBird365 million thanks

[@venkatakrishnagolla9928]

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?

[@DamoBird365]

It will become very inefficient as the size increases. You could try and explore office scripts kzbin.info/www/bejne/apibfZtmnN94pMU and send the data as an array.

[@geralddahl9159]

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.

[@DamoBird365]

I’ve got a basic demo for a vertical html table here near the end kzbin.info/www/bejne/goLYnnaIZqdgp5I and that technique could be used to transpose the data. You could do it direct with an excel office script though.

[@geralddahl9159]

Thank you. I will re-watch and keep this as well as your other tips in mind. Much appreciated.

[@ericabrunetti6043]

It is ok also to copy records from sharepoint list to anew excel file?

[@DamoBird365]

Sure 👌 this works with any data source.

[@lipabera9729]

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?

[@DamoBird365]

Because you are creating a file dynamically, I wonder if you need to supply an object {} to the rows parameter? If it’s identifying the object as text, maybe there’s a formatting issue?

[@bc67]

This looks super helpful, but I'm receiving an, "Enter a valid JSON" error in the compose action that contains the file content. Have you run across this before?

[@DamoBird365]

It will be a typo. If you validate your json, you will probably find an error. You can try something like jsonformatter.org/ if you’re ok sharing the data.

[@bc67]

@@DamoBird365Thanks for your quick response! That's strange. I copied it directly from the file content. Parse error on line 4: ...AFgDNBQAAI2YAAAAA"}{ "$content-type": ----------------------^ Expecting 'EOF', '}', ',', ']', got '{' Thanks in advance if you can quickly identify the problem.

[@2007pradipta]

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 ?

[@DamoBird365]

Hey Pradipta, I’ve not done env variables with this particular solution. Could you use the SharePoint rest api and a filter, as I do in my latest video kzbin.info/www/bejne/pmOZpH9ppq2gnNU

[@elrevesyelderecho]

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

[@DamoBird365]

Use the ‘get file content using path’ option? You might need a delay when creating a new excel file - 6 minutes is a known limitation.

[@elrevesyelderecho]

@@DamoBird365 Ok. Thanks. So after the final step - apply, I will add a delay, then get the content file using path and from there add the send email step... So, can I pick up the file dynamically?. I need the workflow runs on a daily basis to send the new data by email. I will have a new file every day name similar with the UTCNOW expression at the end.

[@DamoBird365]

@@elrevesyelderecho 👍 let me know how you get on.

[@elrevesyelderecho]

@@DamoBird365 I will try again tomorrow. Buy today, I got an error saying that it was unable to find the file. Even the file was on Share point already. I put a previous delay up to 10 min. Not sure if I am selecting wrongly what must be on the attachment content inside the send email step. Name? I'd? Item id? I think should be the dynamic item from the create file above. Nit sure. Any suggestions?

[@elrevesyelderecho]

@@DamoBird365 Hi. I found a solution and I was able to add the step and send the file attached by email. Thanks

[@pgk511]

Great video thanks, We got a requirement to email dataverse table as excel on daily basis. Do you have any thoughts on how we can accomplish it. Any help is much appreciated

[@DamoBird365]

This is actually a request on the forum, powerusers.microsoft.com/t5/Power-Apps-Ideas/Export-Dataverse-view-to-Excel-using-Power-Automate/idi-p/858333. I would suggest you use the Dataverse list rows action and whilst I used add rows to a table, it’s actually more efficient to use excel scripts. I might do a video if you think it’s worthwhile?

[@DamoBird365]

Hi GK, take a look at my most recent video kzbin.info/www/bejne/apibfZtmnN94pMU 6 seconds for 1000 Dataverse rows. Please let me know how you get on.

[@pgk511]

Thank you so much that was pretty awesome. But do you have excel script somewhere that I can copy. Thanks again

[@pgk511]

Also based on this video I came up with something to export data to Excel and send as an email attachment. I am happy to share

[@DamoBird365]

@@pgk511 you can drop me details to [e-mail], share your idea but also let me know which script you want a copy of. I am yet to blog about it.

[@trentbrewer3336]

How do you handle columns with spaces in the title? For some reason only my single word columns are getting any data through.

[@DamoBird365]

I think it’s a limitation of excel. Alphanumeric names only.

[@scoozilife]

would there be any way to be able to add a total at the bottom of a column doing this?

[@DamoBird365]

You can’t easily create an expression using this method alone. You could use office script or graph api. Otherwise sum the values in power automate and add a final row. I’m planning a video on using graph api for excel updates. In the next few weeks.