I hope you enjoyed this tutorial 😃 If you did please give it a LIKE... and don’t forget to SUBSCRIBE for even more content! For More 👉 ruclips.net/user/EssentialExcel
Hi There, Your Power automate screen looks completely different to mine. How do I get to the ACTIONS side bar? Would some help getting to the start screen. Please and thankyou
Great tutorial! I have a question and maybe its not possible as I cannot find this without using software, but is there any way to have each of the files you are combining to become their own worksheet in the workbook? I have logs that get created daily as separate CSV's and I don't want them all one on worksheet when combine, I need them to be tabulated at the bottom. Any help would be greatly appreciated.
Hi, Thank you man it was very helpful I tried it on my files which have two sheets in each workbook each sheet have diffirent table and the template file have the same structure as the separated files when running the flow it only gets the second sheet data and combined them in the result output file first sheet was skipped. is there a fast solution for this? to go through all sheets one by one moving all rows to the right output sheet in the template file.
Thanks for sharing. A great use case and one that I am needing to implement. Dates are part of the import. However, they appear in different formats 12/01/2023 if perfect but I am getting some 12/01/2023 12:00:00 AM. I have made sure the template date fields are formatted correctly and the original ( Source ) files are formatted correctly. Despite this it continues to do this action. I have to double click into each cell and hit return for it to change to the correct format. Is there an easy fix ?
Also this flow is pulling in dates and completely changing the format. 10th May 2023 is in the original as 10/05/2023 and in the new file it is 05/10/2023. Not only that but in the original field with two entries it has changed only one of the dates in this way. This means this is unusable as we are introducing random errors into the data. I have checked the date formatting in the input file and the header template used . they are both correct for Aus date. I have no idea what to do from here but cannot use this
Hi, thanks for the video, works perfectly except ... my Excel file contains several time fields, while running the flow fields are converted to a decimal number. Can you prevent this? Can you also dynamically determine the filename you are creating? With for example the current date in the name?
Hi, this is amazing stuff, however my question is what if one of the files you are trying to combine is dynamic; so in my case, I have two files I would like to combine. They have same properties etc etc. One of the files is generated via an API using power automate, however it only creates 2 months worth of data when the flow runs once every month. My second file is a master file that contains historical data. at the moment, I do this manually once every month where the new data generated from the API flow to the master file. Is there a way to automate the process of appending the API generated file to the master file, master file should only have new API generated data added to it. Any help would be appreciated, thanks.
Hi. Are you able to explain how one can use the LIST ROWS PRESENT IN A TABLE flow TWICE, i.e. for two different tables and then get both sets of fields to then add to a new dataverse table using ADD A ROW inside an APPLY TO EACH function? I need to create a new table using rows from two separate Excel tables. Many thanks.
I am wanting to merge files where the first 6 rows have headings, is there a way to compensate for that? I saw where at the end where you were able to toggle that the first row has headings, but can it go more advanced?
Dear, Thanks a lot for amazing tutorial video. I have used Power Automate for first time and was able to create merge file following steps mentioned here. However, even though last step is save as but template file is getting saved with data of merging.
Hi Anand, apologies for delay getting to your question! Glad to see you have resolved and you make a great point highlighting the potential issue caused by auto save!
Hi, who is this video intended for? Is it beginner or intermediate or expert? I got very confused trying to follow the logic. Could you do another with the interface that is easier to follow?
Hi Mduduzi, sorry to hear you found this video hard to follow. You could say this is more intermediate level but is intended for all levels being able to follow step by step and replicate for their own needs. This scenario required us to use the desktop version of Power Automate, if you are referring to the cloud version as being the easier interface?
Hi, this flow is working fine, but I am not able to open the excel which I have consolidated all the files to, once the flow is done. Any idea why this is happening ? Thanks!
Hi, great video! Quick question - I have a main file with all the data in it and this then feeds out to multiple files. Is it possible, using power automate, to ask the computer to open and read the main file when any of the "multiple files" are opened? Thanks
Hello. Yes this scenario would be fine as power automate will store your main file as a set instance so that it can identify the file when working with multiple files. Hope that helps?
If the files explored in initial step have multiple sheets does it only take data from the first sheet? If so, How do you make it retrieve from more than one sheet?
Some of the Excel files I want to merge have empty rows in them (they are generated elsewhere, which is exactly why I need to merge them) Will that result in `the GET FIRST FREE ROW ON COLUMN instruction finding rows in the middle middle of the filled rows? Example: 1 - finds first free row (row 2), copy contents of file A and pastes on Output file. 2 - file A however, 5th row was empty and then had 100 filled rows. 3 - iterates to 2nd file 4 - finds first free row on Output file. It should be row 106 (file A's 4 filled rows, 1 empty row, 100 filled rows), 5 - pastes content of 2nd file starting at 6th row instead of 106th row! ??
Ive done all the steps but i need a certain range to be copied from the files but nothing gets copied to the new file , how can i rename new file to have a new name based on the flow run date so each new run has its own excel sheet created ,
Hello and thanks for your question! It is very similar to this video however you will be able to find a trigger action for when a file is modified on OneDrive… although be aware this are separate triggers depending on whether you are using a personal or business OneDrive account.
Hello, I seem to be having the problem where it saves as a new file and overwrites the template header so if I run it twice the output is doubled. How do I stop this?
Figured out the issue! One thing that wasn't mentioned in the tutorial is the header file cannot have Auto Save turned on as it will save the output to both the header file and combined output file. Great Tutorial though!
I hope you enjoyed this tutorial 😃 If you did please give it a LIKE... and don’t forget to SUBSCRIBE for even more content!
For More 👉 ruclips.net/user/EssentialExcel
Liked and subbed. This happened to be exactly what I need for a project my boss has me doing. Power Automate is a legit Superpower.
This video is great! Is there a video for combining Excel files in OneDrive with an Instant Cloud?
Awesome video. I was able to accomplish my objective. Thanks.
Thank you for your detailed tutorial. Does this steps work for Power Automate web version as well?
Hi Frances. Unfortunately this tutorial just applies to the desktop version of power automate.
Hi There, Your Power automate screen looks completely different to mine. How do I get to the ACTIONS side bar? Would some help getting to the start screen. Please and thankyou
Great tutorial! I have a question and maybe its not possible as I cannot find this without using software, but is there any way to have each of the files you are combining to become their own worksheet in the workbook? I have logs that get created daily as separate CSV's and I don't want them all one on worksheet when combine, I need them to be tabulated at the bottom. Any help would be greatly appreciated.
Hi, Thank you man it was very helpful
I tried it on my files which have two sheets in each workbook each sheet have diffirent table and the template file have the same structure as the separated files
when running the flow it only gets the second sheet data and combined them in the result output file
first sheet was skipped.
is there a fast solution for this? to go through all sheets one by one moving all rows to the right output sheet in the template file.
Thanks for sharing. A great use case and one that I am needing to implement. Dates are part of the import. However, they appear in different formats 12/01/2023 if perfect but I am getting some 12/01/2023 12:00:00 AM. I have made sure the template date fields are formatted correctly and the original ( Source ) files are formatted correctly. Despite this it continues to do this action. I have to double click into each cell and hit return for it to change to the correct format. Is there an easy fix ?
Also this flow is pulling in dates and completely changing the format. 10th May 2023 is in the original as 10/05/2023 and in the new file it is 05/10/2023. Not only that but in the original field with two entries it has changed only one of the dates in this way. This means this is unusable as we are introducing random errors into the data. I have checked the date formatting in the input file and the header template used . they are both correct for Aus date. I have no idea what to do from here but cannot use this
@@davidcampling1686 Same for us have you found any solution or other method?
@@Siiggyy I found this, and it worked ruclips.net/video/ujX34zOFKJY/видео.htmlsi=7Jpp4Mma9jAUDXqQ
I have the exact same issue. Please let me know if you found a solution to fix this.
Hi, thanks for the video, works perfectly except ... my Excel file contains several time fields, while running the flow fields are converted to a decimal number. Can you prevent this? Can you also dynamically determine the filename you are creating? With for example the current date in the name?
Bro, you are the best! Thanks
Thanks Sebastian! Glad it was helpful 😃
Hi, this is amazing stuff, however my question is what if one of the files you are trying to combine is dynamic; so in my case, I have two files I would like to combine. They have same properties etc etc. One of the files is generated via an API using power automate, however it only creates 2 months worth of data when the flow runs once every month. My second file is a master file that contains historical data. at the moment, I do this manually once every month where the new data generated from the API flow to the master file. Is there a way to automate the process of appending the API generated file to the master file, master file should only have new API generated data added to it. Any help would be appreciated, thanks.
Hi. Are you able to explain how one can use the LIST ROWS PRESENT IN A TABLE flow TWICE, i.e. for two different tables and then get both sets of fields to then add to a new dataverse table using ADD A ROW inside an APPLY TO EACH function? I need to create a new table using rows from two separate Excel tables. Many thanks.
I am wanting to merge files where the first 6 rows have headings, is there a way to compensate for that? I saw where at the end where you were able to toggle that the first row has headings, but can it go more advanced?
Dear, Thanks a lot for amazing tutorial video. I have used Power Automate for first time and was able to create merge file following steps mentioned here. However, even though last step is save as but template file is getting saved with data of merging.
Resolved. issues was, file was getting auto save in one drive folder.
Hi Anand, apologies for delay getting to your question! Glad to see you have resolved and you make a great point highlighting the potential issue caused by auto save!
Hi, who is this video intended for? Is it beginner or intermediate or expert? I got very confused trying to follow the logic. Could you do another with the interface that is easier to follow?
Hi Mduduzi, sorry to hear you found this video hard to follow. You could say this is more intermediate level but is intended for all levels being able to follow step by step and replicate for their own needs.
This scenario required us to use the desktop version of Power Automate, if you are referring to the cloud version as being the easier interface?
Hi, this flow is working fine, but I am not able to open the excel which I have consolidated all the files to, once the flow is done. Any idea why this is happening ? Thanks!
Hello, How to write the next excel file data in to the another column?
Hi, great video! Quick question - I have a main file with all the data in it and this then feeds out to multiple files. Is it possible, using power automate, to ask the computer to open and read the main file when any of the "multiple files" are opened? Thanks
Hello. Yes this scenario would be fine as power automate will store your main file as a set instance so that it can identify the file when working with multiple files. Hope that helps?
can we do it from Power automate online version ?
Is it possible to get image from cell to be merged into one file?
If the files explored in initial step have multiple sheets does it only take data from the first sheet? If so, How do you make it retrieve from more than one sheet?
Can I do the same task with word file to excel sheet instead of excel to excel?
It also works for copying/merging csv files into an excel file.
🙂👍
Thank you for a great video! Is it possible to combine cell colors also?
Some of the Excel files I want to merge have empty rows in them (they are generated elsewhere, which is exactly why I need to merge them)
Will that result in `the GET FIRST FREE ROW ON COLUMN instruction finding rows in the middle middle of the filled rows?
Example:
1 - finds first free row (row 2), copy contents of file A and pastes on Output file.
2 - file A however, 5th row was empty and then had 100 filled rows.
3 - iterates to 2nd file
4 - finds first free row on Output file. It should be row 106 (file A's 4 filled rows, 1 empty row, 100 filled rows),
5 - pastes content of 2nd file starting at 6th row instead of 106th row!
??
Ive done all the steps but i need a certain range to be copied from the files but nothing gets copied to the new file , how can i rename new file to have a new name based on the flow run date so each new run has its own excel sheet created ,
Mine has worked fine, but has only taken x1 workbook from each file, is there a way to read all 3 tabs in each workbook?
Hi, How to set run this flow when file is modified. (on OneDrive)
Hello and thanks for your question! It is very similar to this video however you will be able to find a trigger action for when a file is modified on OneDrive… although be aware this are separate triggers depending on whether you are using a personal or business OneDrive account.
Hello, I seem to be having the problem where it saves as a new file and overwrites the template header so if I run it twice the output is doubled. How do I stop this?
Figured out the issue! One thing that wasn't mentioned in the tutorial is the header file cannot have Auto Save turned on as it will save the output to both the header file and combined output file. Great Tutorial though!
Great process. Is there a way to add a step to add meta data such as file path, filename, for each file stacked and check for duplicate records?
Hello. Yes these could be captured with additional steps.
Hi Can you share steps if the files are in different folder
how we will merge them
By different folder i mean , my files are in different folders on sharepoint. Your early response would be highly appreciated
The only issue is in Power Query, you don't need the Columns in the same order but here we would need them to be in a specific order
I got a Warning in step 6 (write in a excel) "Deferred type provided"
How can I fix that?
i am facing the same issue as well for a different flow where the steps are all the same. No idea whats happening.
Why I can't find my onedrive in getfile
What advantage is there to doing this in power query?
Hi Simon, apologies for not seeing your comment earlier! You certainly could do this in power query 🙂
Interesting but power query is much more easy to get the same result
It certainly is 🙂