Learn to Automate Everything with Power Query in Excel (Download Files) transcript

If you use Excel, which I assume you do because you clicked on this video, chances are you're spending way too much time on stuff you hate, like copying and pasting data for hours or messing around with formulas and hoping nothing breaks. The thing is, you actually need Power Query. Now, I know the name sounds misleading. It sounds like you need a PhD in Data Engineering to work with it, but it's nothing like that. You don't even have to write anything.

It's just click, click, click. The best way to learn it is to download the files that are available below this video and follow along with me so we can do this together. We're going to be covering three common scenarios. Cleaning up messy data like data with blanks in the middle, double headers, empty cells, you name it. Combining multiple CSV files into one clean data set. And turning reports into proper data sets so you can actually analyze the data.

I tell you, after this you're going to love Power Query. So let's start with the first one: cleaning up messy data. So we've been served this campaign data by our marketing department and they want us to make sense of it. Come up with some Pivot Tables. Come up with some insights. We take one look at this and we immediately notice there's some problems here. We have blank cells. These shouldn't be blank. If we want to make a Pivot Table, these should be the value that's above.

So in this case, the Pinkman promotion. There are blank cells in Location as well. These should be filled. If I scroll down, there are blank rows in the middle. We also have these double headers. No, we don't want this. We need to clean this up. That's where Power Query comes to the rescue because you don't have to do this manually, right? You don't have to go to each row and delete this and copy and paste these down and then when you get new data repeat the process again.

No, no, no. Don't do that. Instead, use Power Query. Where's Power Query? It's in the Data tab. The thing is though, it's not called Power Query in the ribbon, so it's difficult to spot, but you're going to find Power Query right here under Get & Transform Data. Notice there are two things you can do here: Get & Transform Data. So if your data wasn't right in front of you, you can fetch it from wherever it is. It might be in another Excel file, in Text/CSV, in a PDF file, in a folder, in a SharePoint folder.

It could be in a database. It could be in Azure, in an online service. You have so many choices. And look at this. This is where we see the name Power Query mentioned. You can launch the Power Query Editor. If your data is right in front of you and you want to transform it right here, you can use From Table/Range. This expects your data set to either be in a table or in a named range. Now, I'm not going to transform this into a named range.

We're going to do an example later on. What I want to do is create my report in a separate file. You see, this file that I keep receiving from marketing isn't my file and I don't want to create my report in their file. I want to have my own file and connect to their file, right? So whenever I get a new file from marketing, I just need to refresh my own report. That's what we're going to do. So I'm going to close this file and open up a blank file.

Now let's go to Data > Get Data > From File > From Excel Workbook. So, we're going to connect to the marketing file. The file is right here. Just browse for it and find it and then click on Import. Now, we can see the Power Query Navigator pop up. We can also see what is inside that file. So, these are my sheet names. If I click on this, I can see a preview of what's on that sheet. This is the data that I want. So I'm going to select Campaign Metrics, and then I can decide whether I want to load this directly in my file or I can select Load To and load it as a Pivot Table or as a Pivot Chart.

But in this case it's not going to work, right? This data is messy. It needs to be cleaned up first. So I'm going to click on Transform Data. This is going to open up the Power Query Editor, and we get to clean the data before we report on it. So, how does this work? Well, on the left side, we can see our queries. We can have multiple connections. We just have one. That's the sheet we're connected to. I'm just going to collapse this so we have more space here.

This shows us our data. On the right, we can see our Applied Steps. And some steps have automatically been applied by Power Query for us. You can click on these steps. So if I click on Source, these are the sheet names that were in the file. Then if I click on Navigation, this is when I picked the sheet that I wanted to connect to. Now, if I ever change my mind, I can click on this gear icon and pick another sheet or connect to all sheets in the file by selecting this folder icon.

Now, in this case, I'm fine. So, I'm going to cancel this. Then, Power Query went ahead and promoted the headers. It thought that the first row should be headers. Actually, that's not true. So, we need to get rid of this. To get rid of this, I can just click on the X here. It's asking me if I'm sure if I want to delete this step. Yes, I want to delete this step. And I also want to delete that last step. So, the last step is related to the Promote Header step.

Because I deleted that, this one isn't working. I don't need this at this point. So, we're going to delete that. Okay. So, you should be left with just these two steps. You see, Power Query repeats these steps every time you refresh your file. And we're going to be adding a lot more steps to this, the steps we want it to run to clean the data set. So, make sure that you are on the last step. So, in this case, the Navigation step.

And we're going to add our next cleaning step. What I want to do is to remove the first four rows because they are not necessary. Look at the ribbon. On the Home tab, you're going to find the most common cleaning steps that people need to do. Remove Rows is one of them. So, I'm going to select this and go with Remove Top Rows. Put in the number "4" and Enter. Notice a new step was automatically added. Next, I want to promote the first row to be a header row.

So I'm going to go here and use First Row as Header. So let's just select that. A new step was added. In fact, two steps were added. A Changed Type step was automatically added. This step makes sure that every column has the right data type, and you can see it with this icon here. So for example, Impressions is an integer number. It's a whole number. This is something Power Query did on its own. Sometimes it doesn't pick up the correct data types on its own, and you would have to come here and correct it.

In this case, we're going to review it, and everything looks good. Notice this green-gray bar here. If you hover over this, you get more information like what percentage of the data is valid, what percentage has errors, how many are empty. Now to get a better view of this for all the columns, go to the View tab and place a check mark for Column Quality. Now this is visible for all the columns. Okay, so it's a toggle.

Toggle this on and off as you need. One thing I do recommend that you turn on is your Formula Bar. So yours might be like this and you don't see the Formula Bar. Toggle it on so you can see it. What this is, is Power Query writing formulas for you as you add your steps, right? So remember we added a step to remove the top rows when we did our clicks. Power Query created this function for you. It's written in M, the language behind Power Query.

Now, as our next cleaning step, I want to get rid of any blank rows. So let's go back to the Home tab. Remove Rows. Remove Blank Rows. But take a look at this. When I select it, I get this popup. Are you sure you want to insert a step? Look where I am in the Applied Steps. I'm right here. It's trying to insert the step after it removed the top rows. Now here, I don't want that. I want this to be my next step after the Changed Type step.

So I'm going to move down here by just clicking on Changed Type and then go to Remove Rows, Remove Blank Rows. Notice this bar is all green now. I don't have any empty cells anymore, but I do have empty cells for Campaign Name and for Location. Well, I need to fix this because these values should be filled down. So, I'm going to do both at the same time. So, select the first column, hold down Ctrl, select the second column, right-mouse click up in the header, Fill, Down, and take a look at this.

All the values are automatically filled down. We have so many helpful transformation steps, and you can find the most common ones up on the Home tab. You can Replace Values. For example, let's say Car Wash Sites shouldn't actually be Sites, but it should be Forums. I can select this column, go to Replace Values, type in Car Wash Sites, and replace it with Car Wash Forums. Press Enter and that step is automatically added.

Now, let's say we want to adjust the letter case for Campaign Name because that P for Promotion is in lowercase. We want it in uppercase. So select the column, go to Transform > Format > Capitalize Each Word, and a new step was automatically added. Did you see the other options? We could add a prefix, add a suffix. We could also trim the values to make sure we don't have any leading or trailing white spaces. So let's actually do that.

There are so many transformation steps available. You can split columns based on delimiter. You can extract values before a delimiter or text between delimiters. Anytime you want to change the content of a column, you can come to the Transform tab here. If you want to add a new column, go to the Add Column tab. So let's say if I wanted to add a new column for Campaign Name with the values as uppercase. I'm going to select Uppercase here.

And a new column is automatically added. To delete a step, just go to the step and click on the X icon. If you also wanted to move steps around, let's say you wanted to trim text before you capitalize each word, you can move it up here to bring it before Replace Values. You can move it further up. I'll just drag it back down again. The cool thing with Power Query is that you can also do calculations here. So let's say I wanted to add a new column that calculates Conversion, which is Units Sold divided by Clicks.

I'm going to select Unit Sold, hold down the Ctrl key, select the Clicks column, go to Add Column > Standard > Divide, and here's my division. Let's rename the column. Just double click in the column header and call this Conversion. That's it. All the steps are applied and my data is beautiful. It's clean. If you recall, this is how it looked before. We added all these cleaning steps by just clicking around in the ribbon.

Or remember, there's always the right-mouse click options as well. So, if you select a column, right-mouse click, you get a lot of the most common options here, too. Now that we're done, we're going to send this to our file. So, let's go to Home and select Close and Load. And this is going to load the data as a table. Now, you can, of course, get rid of this ugly table formatting by going to Table Design and selecting None for the table style.

And then adjust the cell to your needs. To see when this was last refreshed, just hover over the query here and you can see Last Refreshed. If you close the side pane, you can open it by going to Data > Queries & Connections. Anytime you want to adjust the steps or adjust the source file, maybe you change the name of the source file or you change where it was saved, you can go all the way to Source, click on this gear icon, and browse for your file again.

Okay, so you can always make adjustments in the Power Query Editor. Now, the cool thing is whenever we get new data added to our source file, all we need to do is refresh this. Let's quickly test this out. Let's say I've received an updated file from marketing and I have a new campaign called Walter Promo. I'm going to close this, bring up my query. Let's just right mouse click anywhere in the table and select Refresh.

You can see the query is updating and the new rows were automatically cleaned and added to my data set. And by the way, if you want to get a Pivot Table instead of a table, all you have to do is right-mouse click on the query > Load To > PivotTable Report and OK this. You're going to get a notification that Excel is removing the data that you have and putting a Pivot Table instead. That's fine. Click on OK. Then you have a Pivot Table.

Now we can drag and drop what we want. So let's say I want Campaign Name in the rows and I want Units Sold as the values. I now have a proper data set. So I can create meaningful reports. By the way, if your file is on OneDrive or SharePoint and you're collaborating with other people, you can connect directly to the file using the web link. There is so, so much to Power Query. It's amazing. You can do so much. There are also some pitfalls you need to be aware of.

And we go into detail in all of this in our best-selling Power Query course on XelPlus. Over 20,000 people have gone through this course. Some automated their entire monthly reporting process. Others went from spending hours combining data to just minutes. Everyone benefited in some way or other. Almost everyone tells me the same thing: "I wish I learned Power Query sooner." I'm glad you're here today. So, let's move on to the next problem Power Query solves, and that's to combine data from multiple files into one clean data set.

So, in this example, I have these three CSV files. We have sales data for our Rubik's Cube sales. This is a preview of what's in each CSV file. So, we have a Date column, Product Name, Units Sold, and Unit Price. Now, it doesn't matter whether your data is in CSV, in TXT, or in Excel files. You can use the same method to combine everything. And it's so simple, you're going to love it. If you're following along, just make sure you save these files into a folder on your C drive.

And then open a blank workbook. Go to Data > Get Data > From File > From Folder. If you're using a SharePoint folder, you'll select From SharePoint Folder. In this case, we're just going to go From Folder. Select a folder that contains your files. Mine is called Sales. And then click on Open. This dialog box shows us the content of that folder. We just have three CSV files. Now, we could just go ahead and combine everything together.

Either we can Combine & Transform Data, so if your data needs cleaning, you can select this one. If everything is clean and you just want to combine everything together as a table, you can select Combine & Load. If you want to maybe have a Pivot Table instead, select Combine & Load To, and then select Pivot Table Report. So it really just takes two or three clicks to get everything combined. In this case, I just want to show you this option because it's likely that you might need to clean your data.

So let's go ahead and select Combine & Transform Data. In this popup, we first need to specify the settings for each file and select a sample file. So by default, the sample file is the first file in the folder, but we can change that. This sample file helps Power Query decide on the columns that you're going to need, and it uses that to define the delimiter and so on. So in this case, it's picked up everything correctly.

The delimiter is a comma. These all look good. So I'm going to click on OK. Now the Power Query Editor pops up. And look at this. All of these queries and functions were added automatically by Power Query. All the steps on this side were added automatically by Power Query. And it's gone ahead and combined everything. It's even given us a column for the source name. That's the name of the file. Now, in case you don't need this in your end result, you can select the column and press Delete.

And if you ever change your mind and you want it back, you just come here and you remove the step and it's back. It's as easy as that. In this case, I do want it removed, so I'm just going to delete it. Now, let's say I want to add a new column that gives me the quarter number. I'm going to go to Add Column. Make sure the Date column is highlighted. Then go to Date > Quarter > Quarter of Year. And look at this. The quarter number is automatically added.

Now one tip I have for you is to add a filter to your folder. What I mean by that is this. So if I jump back to the Source step, these are the files that Power Query sees in that folder. If I happen to have other files that are maybe Word files or PDF files or Excel files, Power Query is going to attempt to include those as well. I want to make sure it doesn't, because otherwise I'm going to end up with an error. So, what I could do is to add a step right after Source for Extension just to make sure that only CSV files are included.

So I could go and add a text filter: Ends With. We want to insert a step here and we can type CSV. If you want only TXT files to be included or also TXT files to be included, you can add that extension here as well. Now you can also add a step for Name to make sure that only files that start with "RubiksCubeSales" are included in your final data set. You see, there's so much control and so many options you have on how you can transform these data sets.

Once you're happy, you can send these to your Excel. So, let's go to Home and I'm going to click on the down arrow here and Close and Load To > PivotTable Report. So I can directly create a Pivot Table based on the combined data of all those files. Let's add Product Name and Units Sold. What happens if we get new data, you ask? Well, here I've added 2026 data as a CSV file to our folder. Let's go back to Excel and take a look at this Grand Total.

I'm going to right-mouse click and Refresh this, and take a look at this. 2026 has been automatically added. Now, if you ever change your mind and you actually want a table instead of a Pivot Table, no problem. Let's just go back to our query. So, I can see the toggle here. If you happen to have closed this, no problem. Just go to Data > Queries & Connections, and it's going to pop back up here. Go to the query that we have loaded, which is this one, Sales.

Right-mouse click > Load To, and select Table instead of PivotTable Report. Now you get this Possible Data Loss popup. That's fine. It's just going to replace your Pivot Table with the table. And now we have a table that includes all the fields from all our CSV files. How cool is that? I know this video is getting long, but I just had to show you this cool thing, and that's how Power Query can turn reports into proper data sets.

This is what I mean. A lot of us deal with reports that look like this, right? So, this is our coffee units sold in the current year. We have months in the columns, and we're planning on adding more columns for future months. Now, you probably know that this layout isn't optimal for a Pivot Table. It would be best to have the months in the same column, optimally as dates. Well, don't do this manually. Instead, use Power Query to clean this up.

Remember, in the Data tab right here, there is the option to send your data from a table or range. The range should be a named range or an array in this workbook. Well, this is not a table and I don't want to turn it into a table because it doesn't have the right format. I don't have a named range yet, but let's give it a name. So, I'm going to select the range and include more space—so more columns and rows for future data.

With the cells highlighted, I'll go to the Name box and type in a name. I'll just call it "SalesData" and press Enter. Now, we're going to send this to Power Query. Just make sure that before you go to this button here, you have your range highlighted. So go to the Name box here on the side and select the name that you gave your range, right? So make sure it's highlighted, and then click on this button and it's going to send your data to Power Query.

The first thing you should do is to remove this Changed Type step. Why? Because notice Power Query has promoted the headers and then it's assigning the data type for each header. And as it does that, it hardcodes each column name. But these names can change. That's why we should remove this step. So make sure you click on the X here to get rid of it. Now, you're going to love this next step. To get all these months into the same column, all you have to do is select the first column, right-mouse click on the header, and Unpivot Other Columns.

And take a look at what happens here—all the months are in the same column. Let's go ahead and give these columns proper names. This is going to be Date. This one is Units Sold. And that first one is Coffee. Now I call this Date because I want to convert these months into proper dates. So what we're going to do is select the Date column, go to Transform > Format > Add Suffix. These dates are for 2025, so add a comma 2025 and Enter.

Now, this is still not a date. Notice the data type is text. But click on this icon here and convert it into a date. If you're using a different locale, you can select Using Locale. I'm just going to go and select Date. And I have proper dates. Now, all I have to do is make sure the other columns have the proper data types assigned. This one should be a whole number. And this one should be text. That's it. Now, we're going to go ahead and load this as a Pivot Table.

So go to Home, click on the down arrow here, select Close and Load To > PivotTable Report and OK this. Now we can create different views of this data set. Let's add Date to the rows, Units Sold to the values. If you wanted to see the dates as quarters, all we have to do is adjust the grouping. So let's go and right mouse click here, select Group, and select Quarters. And let's get rid of Days and Months. And OK this.

Now what about new data, you ask? Well, I've hidden some new data in another sheet. So, let's unhide. And here we have August to December data. So, I'm just going to select these, copy it, go to Data, and let's just paste it in at the end here. Now, let's go to our Pivot Table report, right-mouse click, and Refresh. And the new data pulls over automatically. Okay, that's Power Query for you. Don't forget to download the file so you can practice what we covered today.

I also recommend that you come back and repeat the steps in a few days without watching the video just to make sure that everything sticks. And if you want to take your skills further, do check out our complete course on XelPlus. Thank you for dropping by, and I'll catch you in the next video.