36|POWERAUTOMATE – Consolidation by Cybernation

BYU Student Author: @Christian
Reviewers: @Spencer, @MitchFrei, @Jonathan_Weston
Estimated Time to Solve: 30 Minutes

We provide the solution to this challenge using:

  • Power Automate

Need a program? Click here.

You’ve been working as the IT specialist for all the accountants, and, boy, are they always grateful for your help! You’ve managed to save them over 300 hours every year! While walking around for everyone else to acknowledge your greatness, you overhear someone exclaim, “this is so mind-numbing!” Looking over their shoulder, you see that they are tasked with combining .csv files into the same .xlsx file and having to rename each file after copying the worksheet. You instantly know there is a much faster way to get this done, so you set to work to save the company some more time. You gather a handful of old files from a random company’s financials and general information in 2015. You are now ready to automate the process of constructing a new excel file out of these 8 files!

Create a flow that can open an excel file, and copy the contents of another excel file to a NEW worksheet on the original file. Rename the worksheet according to the name of the file and then, when all the pages have been combined, save your excel file as “2015CombinedBooks” (be sure to save it as a .xlsx file, not a .csv file!).

Data Files

Remember to unzip the contents of this zipped folder. Referencing a zipped folder can cause problems when writing a bot.

Suggestions and Hints
  • Use an action that creates a list of items to store the individual .csv files.
  • Start off by selecting one file and test it out until it can run with just 2 excel files. After you figure it out for 2 files, loop it for the other files in the folder.
  • When you are trying to rename the worksheets to the name of the Excel files, be sure that you are referencing the correct property from the item in the list. In this case, you want to rename the excel worksheet using the property “.NameWithoutExtension” to isolate the list item’s file name.


I had never used Power Automate before this challenge, so this was my first introduction! It reminds me a lot of UiPath. I like how simple the tools in Power Automate were and how well they worked with other Microsoft products. Your challenge and solution image was very helpful for teaching me how to create a workflow. I’ll definitely be testing this software out some more!


I’m new to Power Automate, so this challenge was great practice! I didn’t know about the “.NameWithoutExtension” property; that’s super useful! Here is the solution I came up with: