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

Подписаться 12 тыс.

Просмотров 107 тыс.

0% 0

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 ☕

Наука

Опубликовано:

21 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 294

@DamoBird365 4 месяца назад

I have an updated version (Feb 2024) with both Graph API and Excel Office Scripts here 👉ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-gtlklzi6MDg.html 👀

@DamoBird365 2 года назад

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! ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-4g8Lh0gzEnc.html

@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 👍

@Kralnor 2 года назад

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

@rhodrithomas6040 2 года назад

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!

@rkobiggestfan7 2 года назад

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 2 года назад

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 2 года назад

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

@jherschel 29 дней назад

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!

@notonurnelly 2 года назад

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

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

@nandnandan4198 2 года назад

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

@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 👍

@lucapiu 2 года назад

Thank you so much, just what I needed!

@sebastianmartinezarenas3696 Месяц назад

Thank you for your content!

@apodostroiec Месяц назад

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

@DamoBird365 Месяц назад

Thank you 😍

@wigglwrrm Год назад

Massively helpful. Thanks!

@MitchTorres Год назад

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

@aT0000my 9 месяцев назад

Thanks Damien, very helpful!

@JonDoesFlow 2 года назад

Nice one, very helpful, thank you Damo !

@weizhang429 2 года назад

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

@anhduong8484 2 года назад

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

@VaibhavSharma86 7 месяцев назад

Too good. Really helpful. Cheers.

@mamadykonate8253 Год назад

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

@lakshyasharma1715 2 месяца назад

Thanks this was a huge help

@GregDolan Год назад

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

@user-hb3gb9zn7n 10 месяцев назад

Thank you very much for creating this

@edjk6278 Год назад

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

@DamoBird365 Год назад

Thank you.

@yiubili1 2 года назад

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

@vijaymunavalli335 3 месяца назад

Well explained🎉

@44bthoj44 2 года назад

Awesome presentation.

@florian1054 2 года назад

thank you for your video, really appreciate it !

@financiallyfreegenz 4 месяца назад

Thanks for this video. Very helpful in my projects

@DamoBird365 4 месяца назад

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

@sudarshanreddy6969 Год назад

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

@the_n8 4 месяца назад

very helpful and to the point. Thank you

@DamoBird365 4 месяца назад

You might want to also check out ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-gtlklzi6MDg.html

@myintzusan9159 2 месяца назад

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

@DamoBird365 2 месяца назад

Amazing 🤩 good luck with the rest of your automation 👍

@Sudharaaj Год назад

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

@lfish894 Год назад

Damien as always is so helpful!

@SaifUlislam-zq2qn 2 месяца назад

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

@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 😁

@snooki9 Год назад

Thanks man !

@ngunghuong6938 Год назад

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

@franknielsen3219 2 года назад

Brilliant video.😀😀😀

@tranbangviet 7 месяцев назад

Thank you so much !

@DamoBird365 7 месяцев назад

No problem at all.

@quang110289 Год назад

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

@lxz86 2 года назад

very useful case ， thanks a lot

@dlep9221 Год назад

Great !!!

@ColinBaconLabs Год назад

Excellent :-)

@eduardvaldivia 10 месяцев назад

It help me a lot, thank you very much!

@DamoBird365 10 месяцев назад

Cheers Eduard.

@DeividFaulstich 9 месяцев назад

goood! thanks!

@prajaktadoshi9855 2 года назад

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 2 года назад

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 2 года назад

@@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 2 года назад

@@prajaktadoshi9855 do you want to drop me a bit more detail / description to ideas@damobird365.com with a use case? Thanks

@prajaktadoshi9855 2 года назад

@@DamoBird365 yes sure will do

@nlsfrck2633 2 года назад

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.

@johnfromireland7551 2 года назад

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 2 года назад

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 3 месяца назад

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 3 месяца назад

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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-CZFLjy8hEx0.htmlsi=03qZYhH3E6DG4xjZ

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

@zineddine 2 года назад

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 2 года назад

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

@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 😉

@yojie120 2 года назад

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 2 года назад

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

@MohammadAli-ts2ev 2 года назад

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 2 года назад

Take a look at Excel Office Script Tutorial - Populate an Excel File Dynamically from Power Automate ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Q7GLQnvJJF0.html

@vincentDev84 27 дней назад

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

@DamoBird365 27 дней назад

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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-gtlklzi6MDg.html

@thiagolopes1132 2 года назад

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 2 года назад

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 ideas@damobird365.com.

@Inno94709 2 месяца назад

Thanks!

@DamoBird365 2 месяца назад

Cheers Jay, appreciated 👍

@Inno94709 2 месяца назад

@@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 2 месяца назад

@@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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-gtlklzi6MDg.html

@Inno94709 2 месяца назад

@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 2 месяца назад

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

@croti5391 2 года назад

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

@DamoBird365 2 года назад

This might help too ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-4g8Lh0gzEnc.html

@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 ☹️

@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

@NiftyNinjaa 2 года назад

@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 2 года назад

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

@seemunwong9364 3 месяца назад

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 3 месяца назад

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

@seemunwong9364 3 месяца назад

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

@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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-G3Q1WuZTWuY.html

@angelazou1530 2 года назад

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 2 года назад

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 2 года назад

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

@user-sj3zd3te9o 4 месяца назад

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 4 месяца назад

You’ll need to get the file content. In my latest demo of something similar, I show you how ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-gtlklzi6MDg.html

@KarthikeyanBalasubramanian 9 месяцев назад

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 9 месяцев назад

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.

@mariushernandez6112 10 месяцев назад

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 10 месяцев назад

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 👍

@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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-4g8Lh0gzEnc.html

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

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

@powerbi9048 2 года назад

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 2 года назад

Hi, can you share with me a bit more detail to ideas@damobird365.com

@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?

@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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-L67m8wgd4Ak.html or Office Scripts ru-vid.com/group/PLzq6d1ITy6c2_qM_ocYDtEaENrqi92YmM

@SiyamandRashid Год назад

@@DamoBird365 million thanks

@jeanegerber9775 2 года назад

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 2 года назад

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 2 года назад

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

@user-zb6wo9kg4j 11 месяцев назад

@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 10 месяцев назад

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 👍

@gtigerh 2 года назад

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 2 года назад

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.

@ericabrunetti6043 4 месяца назад

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

@DamoBird365 4 месяца назад

Sure 👌 this works with any data source.

@learntoautomate365 2 года назад

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 2 года назад

If you watch my latest video, I show you how to create folders using PowerAutomate. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-ySAZqtC6mXg.html. Hope this helps, please let me know.

@learntoautomate365 2 года назад

@@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 2 года назад

I do folders and subfolders in the video 😉. You can try the forum powerusers.microsoft.com/ or drop me an email ideas@damobird365.com and I’ll try to help.

@learntoautomate365 2 года назад

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

@GabrielAGilO 7 месяцев назад

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

@JONODO12 3 месяца назад

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 3 месяца назад

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

@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?

@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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-LQumCR1B-q0.html 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.

@mattkoch3117 2 месяца назад

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

@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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-4g8Lh0gzEnc.html and send the data as an array.

@ErikNordblad-pt3to 2 месяца назад

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 2 месяца назад

You can populate files fast with this method : Create Excel File and Add Rows Fast | Graph API | Office Scripts | Power BI | Power Automate ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-gtlklzi6MDg.html

@DamoBird365 2 месяца назад

And combine it with something a bit like Transform Excel Data into HTML Table and Email: Power Automate #PowerAutomate #Outlook ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-pwJ73jFEtDQ.html

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

@pgk511 2 года назад

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 2 года назад

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 2 года назад

Hi GK, take a look at my most recent video ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-4g8Lh0gzEnc.html 6 seconds for 1000 Dataverse rows. Please let me know how you get on.

@pgk511 2 года назад

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

@pgk511 2 года назад

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 2 года назад

@@pgk511 you can drop me details to ideas@damobird365.com, share your idea but also let me know which script you want a copy of. I am yet to blog about it.

@anupamtamrakar8630 2 года назад

<a href="#" class="seekto" data-time="521">8:41</a> In the same scenareo, my external data source is a Form processing model, that is extracting tables from pdf. Any guidance on that? Thanks.

@anupamtamrakar8630 2 года назад

Detail: In the new compose action, I'm creating a jason array similar to what you have as an external data source. But my compose action is wrapped in ApplytoEach with names and valus are dynamic values from Form processing model. Its throwing "Bad Request" Error on the "Create file" block.

@DamoBird365 2 года назад

You’ll have to share me a screen shot. Ideas@damobird365.com.

@trentbrewer3336 2 года назад

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

@DamoBird365 2 года назад

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

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

@Scott_OD Месяц назад

Hi Damien, is it possible to get a JSON array from an excel file using the Sharepoint HTTP request? Thanks

@DamoBird365 Месяц назад

You could use office scripts or graph api. A bit like ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-UspiIlj6hSQ.html. Did you see my demo on populating via both? ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-gtlklzi6MDg.html

@Scott_OD Месяц назад

@DamoBird365 yes I had seen those thanks, but they didn't cover getting a JSON array using the Sharepoimt HTTP request

@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

@bc67 3 месяца назад

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 3 месяца назад

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 3 месяца назад

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

@nicbongo Год назад

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!

@DamoBird365 Год назад

I am not sure to be honest. Files can become locked with excel, did any data write? Can you manually open the blank file? Maybe there’s something wrong before the create table. You can also use an office script or graph api ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-mNwEk7hLdfE.html

@nicbongo Год назад

@@DamoBird365 thanks so much for the swift response! I've tried hard coding the site and library paths, and have got a step further. The file and table is being created, but now the data for each staff member is not being iterated and populating the table in the excel! To answer your questions: - yes I can see and open the file. - only column names is writing I will view the script video pronto! Subbed 🍻

@2007pradipta Год назад

hi I am creating same with Solution and Environment variables. At <a href="#" class="seekto" data-time="220">3:40</a> 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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-p26sL3qHmfs.html

@GMarshll 2 года назад

Are you able to populate more then one sheet within the excel file? I am looking to populate multiple sheets from sharepoint lists and then email the excel file. Is this possible?

@DamoBird365 2 года назад

There are several approaches to this. Your template could have the sheets with tables already inserted and you can populate the tables. Or you write an excel office script ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-4g8Lh0gzEnc.html that can write to excel without a table. It could even create sheets based on a dynamic number.