Name: How to use Microsoft Power Query
Uploaded: 2025-06-25T18:51:16.000Z
Duration: 16 min 35 s
Description: Thousands of YouTube videos with English-Chinese subtitles! Now you can learn to understand native speakers, expand your vocabulary, and improve your pronunciation...

Today I want to show you how you can use Power Query that comes with Microsoft Excel and Power BI.

Well, it helps you both get and also clean up your data.

Well, let's pretend that you're a business analyst and every month you're on point to analyze the monthly sales data.

Now one of the pains of the job is, well, first off you have to get the data.

So you go to some database or you find the latest Excel sheet.

Then maybe you bring it into Microsoft Excel and then you have to clean it up.

Maybe you have to remove some columns, add some columns, run some calculations, and then finally you're ready to analyze it.

Instead, you can let Power Query do all of that for you and you can automate that entire process.

And best of all, you don't even have to know any coding to do this.

I've included some sample data in the description if you'd like to follow along.

All right, let's jump on the PC and let's see how we can use Power Query.

Here I am on my PC and at the Kevin Cookie company, I'm on point for analyzing our order data.

Here you could see three years worth of order data and very soon 2020 will be dropping in this folder.

Once again, if you want to follow along, I've included sample files in the description.

Let's click into one of these files to see what work is involved.

Here's the Excel sheet and it's a pretty standard data sheet.

I see order ID, I see the customer, how many cookies were shipped, and some additional information.

Now the pain is at the Kevin Cookie company, they're paying me the big bucks to do analysis, but I end up spending most of my time just cleaning up data.

Here, for example, in column B, I don't know why they combine the customer ID together with the customer name.

Also, when I look at cookies shipped, they throw a dollar sign in front of this.

It should be a loss look fine, but I need to add another column for profit.

I also want to know how many days it takes to actually ship these cookies out of our warehouse.

I have to go through and I typically add columns.

It's a lot of manual effort and worst of all, every time a new sheet comes out, I have to do all this cleanup again and again.

I'm really hoping Power Query can help me today.

To access Power Query in the latest version of Excel, simply go up to the top ribbon and click on data.

If you have Excel 2010 or Excel 2013, you need to install an add-in to be able to use Power Query.

I've included a link in the description where you can access that.

Over on the left hand side, Power Query falls within this category, the get and transform data.

If we click over on get data, we see all the different sources where we can pull data in from.

It's a vast collection of different places.

Basically, if you have data somewhere, you'll be able to pull it into Power Query.

When I hover over from file here, you'll see a few examples.

One of the neat ones to call out is you can now pull in data from a PDF.

You could pull in data from databases, from Kusto, from Azure.

The list goes on and on of all the different places where you can get data.

I mentioned that I have my three files all in a folder and very soon the 2020 data will also fall in that folder.

Within the from file list, I'm going to click on the option that says from folder.

Next, I see a prompt where I can specify the folder path where I have all of my Excel sheets.

That'll open up a file picker where I can navigate to where those files are.

I've already copied and pasted the folder location, so I'll simply paste that in and then click on okay.

Next, this opens up another prompt where I can see that Excel or Power Query has found three different Excel files in that folder.

I had the 2017 through 2019 data, so that all looks good.

Down below, there are several different buttons.

Well, I have three different sheets here and I want to combine all of the data from those sheets.

I'll click on the combine option and I want to transform the data.

What does transform mean and why would I click on that?

Well, transform allows me to modify the data before pulling it into Microsoft Excel or into Power BI.

As I was walking through the data, there were a few different items.

For instance, I wanted to get rid of the dollar sign in front of a column.

That's what I could do with transform data.

I could also simply combine all the data and load it into Excel or I could choose where I want to load it to, but then I wouldn't have the opportunity to make changes to the data before I load it in.

Here too, I also have the option to simply load the data as is or I could simply transform the data, but because I have multiple sheets and I want to combine them, I'll click on combine and transform data.

This opens up another prompt where I can now combine all of my files.

Up above, it allows me to choose a sample file and by default, it simply selects the first file, which is the 2017 data.

I could also change any other year, but I think the 2017 data is fine, so I'll simply leave it set to the first file.

Down below, I also see the worksheet within all of the different workbooks.

Now, all of my different workbooks have a sheet called order data, so I'm going to select that.

Over on the right-hand side, here I can see a preview of all of my data and it looks like it identifies it properly.

This drops us in Power Query and if you've never used Power Query before and you're on point for analyzing data and getting data, you're going to learn to love this interface.

It might look a little bit intimidating at first, but I'm going to walk you through step-by-step how you can use it.

First, I want to orient you to the experience.

Let's start off over on the left-hand side where we see all of our different queries.

You have a set of helper queries and here we see a sample file that we can transform.

Down below, you also have the final query that will apply against all of our data, so when we bring it into Microsoft Excel, it'll all be transformed.

And as we walk through this example, that'll make a lot more sense in a moment.

Over on the right-hand side, we also have all of our different query settings.

Down below, we see something called applied steps.

The way to think of this is this is like running a macro and Power Query remembers every single transformation that we make on our data.

Up on top, just like with most Office applications, we see a ribbon with several different options.

We're going to use this to transform our data.

In the center of the screen, I can see a sample of my data.

At the Kevin Cookie Company, we sell a lot of cookies, so it's only showing me the top 1,000 rows of our order information.

Once I make all of my changes to this data and I transform it, then we'll close it and load it, and that'll apply these changes to all of my data beyond just the top 1,000 rows.

Let's go through and let's start transforming this data.

Now, I don't need the source name for the year that it comes from, so I'm simply going to right-click on this column and I'll click on remove.

Here now, I see the order ID, and for the most part, it all looks good, but there's also an XXXXXX that appears.

Now, it might seem a little odd to have that in here, but anytime the Kevin Cookie Company say we send free cookies to different events or different promotions, and so we mark that down in our order data.

But when I do my analysis, I don't want to look at this, so I usually go through Excel and I filter it out.

Let me click on this filter icon up on top, and because it's a letter, I'll scroll to the bottom of the list and then uncheck this.

Next, I was complaining about the customer ID being combined with the customer name.

I'm going to click on this column, and then when I right-click on it, I can go down to the option that says split column, and I want to split it by delimiter.

The delimiter is the space, the dash, and the space.

This opens up a where I can specify a custom delimiter.

Here, it looks like there's a dash, but I also want to insert a space in front and a space after.

This now split those into two separate columns.

If you look at the column title, though, one of them is customer ID.1 and the other is .2.

Subtitles ListPlay Video

How to use Microsoft Power Query

access

description

instance

revenue