Did you see the new order from the headquarters?
They said they want to have our office location added to all our file names, and they want it done
by the end of today. Oh, that's a lot of files, but wait, I know how to do that.
Let me get started, get cracking. One hour later... "Wait, you're doing it manually? How else would I
do that? So many ways, but not manual. You already have a great tool for that on your computer.
Get out of the way, let me show you. Okay, so pay attention. The tool you're gonna
use is called Power Automate for Desktop. Great news is that if you have Windows
11, it's already pre-installed for you. If you don't have Windows 11, but you have Office
365, you just go to your office.com account, go to Power Automate, and install it from there.
With Power Automate for Desktop, you get to build simple to complex robots. This isn't a tool just
for programmers, it's a tool for noobs and pros, so yeah, you can use it too. Once you
have it installed, just open it up, and If you have any existing automations created,
you're going to see it here. You can give your automations cool names, like this one, which
reminds me, there is a button below this video that you can click to make sure you don't miss out
on any cool Office tips. Obviously, learning is a journey, and every journey begins with the
first step, so subscribe and stay in touch. Now, if you've never created automations
before, you're probably going to see a button here that you can click to create a
new flow. In my case, the new flow is up here, so I'm going to click that. Now, I can give
my flow a name. I'm going to call it "Rename System Files." Power Automate is going to start to
get things ready for me, and we come to this view. On the left here, I have so many different
actions I can choose from. Under "File," for example, I get the ability to rename files. But
because my files could be in multiple folders, I could go ahead and get the files in
the folder first, and then rename these. Before we start with our automation, let's take
a look at what we need to do. So, you were trying to rename all of these files manually, and you
want to add '"Chicago_" in front of each one. So, let's go ahead and first grab the files
in the folder. I'm going to click on this, drag and drop it here, and I'm going to get a new
pop-up where I can specify the folder. So, let's select it. We have a mapped SharePoint folder and
we also have a mapped OneDrive folder. So, your files are here, in "Noob", "Work Stuff,' and "System
Daily Data," and click on "OK." Then, we can also apply filters to include only specific files. Now,
since in this case we want to rename all the files in the folder, there's no need to add anything for
filter. Will it also work on files and subfolders? Duh, if you turn it on, of course it will.
And actually, I've realized that you do have a subfolder here called "Old Data," and we have to
rename all of these files as well. So, yeah, we are going to turn on "Include Subfolders." Notice
a variable here was produced called "Files," and this was done automatically for you.This is just
the output of this step, and it's going to become relevant in our next step. So, we are going to
save and close this and move on to the next step. The next one is to rename the files. So, I'm going to
click on this, drag and drop it here. What file do I want to rename? Well, it's not a specific file;
it's the output of my previous step. So, notice that last icon here: it says "Select variable."
I'm going to click on that, and I'm going to see Files. So, let's click it and select it. What
type of renaming scheme do we want to apply? Do we want to add text, remove text, replace
text, change the extension, add date/time, or add sequential numbers? Well, in this case, we
want to add text. What do we want to add? We want to add "Chicago_" Do we want it after the name
or before the name? In this case, before name. If the file with that name exists, what do we
want to happen? Do we want to overwrite the file or do nothing? Well, I'm going to be on the safe
side and do nothing. Now, notice a variable or an output of this step was also produced, and if you
are going to have more steps, you can reuse this. In this case, that's our last step.
We don't really need this. I'm just going to deactivate this and click on save. And
that's it! All we had to do was drag and drop, make a bunch of selections, and we get to rename
everything by clicking this one button here. Now let me just bring up my Explorer so we can
see things happening in real time. I'm just going to snap it here, and now let's run this. We can
see all the names updated super fast, and if I go to "All Data," all of these have been updated as
well. Imagine having to do this manually! No, no, don't imagine that. Now, if this is an automation
that you're planning to reuse, you can save it. You know, HR asked me to rename the contracts.
We have to include the employee name instead of the ID. Do you think you can use your magic tool
for this as well? But each change is different, so probably not. Of course you can! You can make
dynamic replacements too. We just need a template, and we can use the HR master file for that.
Let's go over to your desk, let's go over to task quickly, so I make sure I understand properly.
Here we have the different contracts. They end with the employee ID. We need to replace that
ID with the employee's name or their initials. To get their initials, we have an Excel file down
here which gives us the employee ID, their name, their first initial and the last name, the current
file name of their contract, their manager, and so on. The information that we need is this one here,
so we need to get this ID and replace it with this name here. If we were going to do this manually,
we're going to open this file, we're going to take a look at the Explorer, we're going to go to
"126," scroll down to find "126," and then grab this, copy it, go here, and paste
it in, right? So, that's what we're going to do, and then we have to repeat that for all of these
files. No, no, no, we're not going to do that. We are going to use Power Automates. I'm just going
to press Ctrl+Z here. Let's set this up really quickly. Open up Power Automate and create a new
Flow. Give this a name and click on "Create." Now, how do we start? What do we need to drag
and drop? Well, the way you give instructions to your robot is the same way you would explain
the steps to a newbie. First thing they need to do is to open up your Excel Master File so
that they can see what the old ID is and what the name is. So we're going to go to the Excel
section here and launch Excel. We don't want a blank document. We want to open an existing
document. That document is sitting right here. It's in the "Contracts" folder all the way down.
That's what we need. Click on "Open" and save. Next step is to go through this file row by row.
We have data starting from row 2 all the way to row 60 and we need to go through this row by row.
That's exactly what our robot needs to do as well. And check this out: there is a loop section
here. We need to loop through the rows. So let's drag and drop "Loop" in our flow.
We want to start from the second row, we're going to stop on row 60, and we want to go
one row at a time. The variable that's created is "Loop Index." This is going to always tell us
where inside the loop we're at, and that's the way you can reference each single row. We're going
to click on "Save," and we get this visual loop added here. Whatever we want to happen for each
single row is going to be added inside this loop. Well, what would a person need to
do to be able to make these changes? They need three different
information. They need to know what is the old ID , they need to know what they
should replace it with (which is this one), and they need to know the file name. All of this
information we have here in the different columns. We need to read this information. That's what our
robot needs to do. Under Excel here, we can read from the Excel file. So we're going to drag and
drop this inside the loop. Which Excel file? Well, the same Excel file that we opened right here,
which is reflected with this variable. We want to read the value of a single cell. The start column
is "A" (you can use letters or numbers here). The start row? That's not a specific row,
but that's the row inside our loop. So we're going to go and read each single row. We're
going to use the "Loop Index" variable here. Now, if I don't change these variables that are
produced, I'm just going to get dummy variables called "Excel Data," "Excel Data 1," "Excel
Data 2," and so on. And I'm not going to know what exactly I'm reading from each file. So
you can change these names yourself. You can just click and change. So column "A" has the old
ID. Let's rename that and save. Now, I'm doing this because it's going to make things easier
later on, and you're going to see that in a bit. Next thing we need to read is the new name that
we want to add, and that was I believe in column "D" (because column "B" seems to be hidden).
There again, the start row is the "Loop Index" and save. But, no, not too fast. I should
change the variable name so that it's easier to reference later on. Let's go ahead and edit
this step and call this "NewName" and save. Now we're going to read the existing
file name that was in column "E," Loop Index, and let's change this to
"FileName" and click on save. Now that we have all the information we need,
we can go ahead and rename our files. So, that's inside the "File" section here, we have
"Rename Files", we're going to drag and drop it inside our loop. What file do we want to rename?
Well, it's all our Contracts, so let's go ahead and select the folder we have these contracts
in, which is right here, click on 'OK', and now we want to add on the name of the file for
each single row, right? So, we're inside the loop, we want to add this to that folder path, so
we're going to add a Backslash, and now use our variable, which was "Filename". What is
our rename scheme? We want to replace text, what text do we want to replace? Again,
we're going to go with our variable because we gave them good names, it's easier to
identify, we want to replace the "OldID" with the "NewName". If the file exists, we could
overwrite it, but I'm just going to do nothing, and in case we run into an error, we can either
throw the error or continue the flow run. Now it could be that I have file names in the master
file that don't exist in my file explorer, so I just want to continue the Flow, Run, and click on
Save. OK, so now that I have everything set up, I'm launching Excel. Let's also close Excel in
the end, so let's just scroll down to Excel and close it, the same Excel instance, and we
don't want to save the document. If everything is set up correctly, it should work. Let's test
it out, click on Play. We can see it's running, and we should be able to see it loop here. We can
see it's going through each single row, we can see the result of the variables here, and if you
take a look at our explorer, we should see these change in real time, and we can see the
numbers are being replaced by the names. Imagine if we had to do this manually. Thank you
for watching, subscribe, and wish me luck, bye.
