Working with Data in the Power BI Power Query Editor

Transform data in Power BI’s Power Query Editor

In this video, you will learn about the Power BI Power Query Editor syntax, and various ways to transform your data in Power Query Editor. Matt will teach you to change data types, create new columns, and how to undo and save your changes.

Hi, this is Matt with Playfair+. In today’s video, we’re going to open the hood on Power Query Editor. This robust tool is a great place to start formatting, structuring, and changing your data. It’s the place that you would start manipulating. Any kind of analysis you want to do in Power BI, I recommend you start in Power Query Editor.

It’s very powerful. It works well. So let’s go ahead and dive into some of the most used features and get you started. Let’s go. 

When we download our data, let’s go ahead and click on Transform at the top of our Home ribbon on the Report view, and we’re going to click on Transform Data. This will open up a separate window, and it is easy to get confused sometimes, when you have that second window up, you might be thinking sometimes, oh, I don’t really know, what I’m looking at.

This doesn’t look like Power BI, my desktop home. It’s more than likely because you have Power Query Editor open and you’re, thinking, oh no, I thought I was on the Table view, but you’re in Power Query Editor, so let’s go back to the Home ribbon. We’ve got a new look at our data and I will refresh all of my tables so that we can actually see them.

I guess I canceled those somehow. Alright, so we have all of our tables here and we’re going to look through these and talk a little bit about some of the things that we can do in Power Query Editor. It’s really powerful. It’s the first step we’re going to take before we start working with our data is we’re going to make sure we’ve shaped it correctly.

We’re going to make sure that the tables are the right settings. We’re going to make sure that we have our data types correct. So if we’re on the Home ribbon right now, I’m looking at the Sales table. So this is our main fact table and I’ll cover in a little bit what that means. But we’re going to look through our Sales table.

So we’re looking through, I’ve got Order Quantity, and I start getting into, okay, Unit Price. So let’s take a look at the Unit Price column. Right now if I’m looking at this and I want to make a change to it, I’ll go to the Transform tab and it says, okay, it’s a decimal number, that’s fine, but Unit Price is a currency, so it’s actually going to be fixed decimal number.

So I’ll change this to fixed decimal. And it says we get a little popup. I’m going to add a new step. I want to see this new step reply, revealed over here in the applied step. So you can see that Unit Price is actually a currency type, and it is listed specifically that way, on our table. So, same thing for like extended amount.

This is a decimal number. Let’s make it a fixed decimal number. So, Unit Price. Discount Percent. It’s listed as a whole number. Let’s make sure we change that to percentage so that we can see what the discount would be in an actual percentage. So we’ll do this, several other times. Looks like we’ve got Product Standard Cost, Total Product Cost, and Sales Amount.

What happens if we want to make all of these a fixed decimal number, you can do them all at the same time. So it’s really handy. It’s a great thing to do for making sure that your data is in the right format that you want before you start using it. And then let’s talk about a few other things that we can do, with our really powerful Power Query Editor. 

Let’s say we wanted to add a new column into our data, and we’ve got some customer information. Okay. Let’s look through our customer information real quick. We’ve got Customer, City, State, Province, Country, and Postal Code. Okay, that all makes sense. What if I wanted to say, oh, my customer information is a full name.

This is great, but I’d really like to have two additional columns. One with just a first name and one with just a last name. Alright. Well, we can do a couple things here. We can go and we can split the column. We can create a new column. So what we’ll do is we’ll take a look in our Transform tab and we’re going to, we can duplicate it if we want to start from scratch. So let’s start here. Now we have a copy so there’s no change to the Customer tab. So if we make any changes here, we don’t like what we’ve done, we’re in good shape. And then let’s go ahead and say, alright, so we are in. Sorry. Go back to the Home ribbon.

Now we’re going to, do our split column. So we’re in Customer – copy. We’re going to split By Delimiter for this example. And we’re going to say, hey, it’s the space between the names and we’re going to take the right-most delimiter, maybe there’s a name that’s like a Juan Carlos, maybe there’s a Sarah Jane.

If you have a split first name, we’re going to make sure that it’s only splitting out the last name. So we’ll do this. And then what you’ll see here is now our copied customer is we have Customer – Copy.1 and Customer – Copy.2. We can go ahead and if you’d like to, you can rename, your column. So we can say Customer First Name, and you can see applied steps shows up on the right hand side.

So we’re going to rename this one to Customer Last Name. And you can see the renamed columns. One awesome thing about Power Query is if you make a change, like let’s say I changed this and I said, Customer Oops Name, and I thought that was, I didn’t really want to rename it, you can just click on the X and it will undo the change completely.

So you don’t have to worry, if you make a mistake, if you split out a column. If you create something that is not needed. You can always undo those steps later. So, we’ll go ahead and we’ll go back to Customer Last Name and Customer First Name again, because we did these, sequentially. They’re going to be shared in the same applied steps.

Alright. Customer First Name. So now we have our new column here. There’s really, there’s so much we can do. This data set is very clean and very good. Sometimes you’ll end up with data where you need to remove some of the top rows, remove some of the bottom rows. Perhaps you have errors in your data and you can view that.

In the View tab, you can say, let me see the column quality. Do we have errors in our data? The AdventureWorks data set does not have errors in it. We’re very fortunate for that today. Simply because the amount of time that we have to go through everything is limited. And so we could spend an entire day in some data sets just making sure we’re removing errors, pulling blanks out. 

So, but the View ribbon at the top is really a great way when you’re starting with a new data set. If you’ve never used it before. I always come in and I take a look at the root. I say, alright. Let’s pull these up. I want to make sure that this is reflecting what I expected to see in the data.

I want to make sure that my, you know, like, okay, City is valid. We have no errors, and the distribution makes sense, where, okay, we’re not seeing all of my customers from one city, and then none others are, you know, represented. Customer is something we’re, we’re going to expect to see individual customers.

That makes sense. But, if you pulled in a data set and the City was a hundred percent valid. But it said all of your customers were in Burbank and you know that you’ve been shipping, you know, materials to other cities. You’d want to check and see what’s going on with the data. Maybe there was an error pulling the data in.

So that’s our View tab. And I should, go ahead, I’m going to uncheck this so that we’re not looking at these the whole time. Okay. And then, alright, let’s see what else we have to cover. So, we’ve done our split column. We’ve looked through that. We are looking at our Add Column. 

Let’s go ahead and go back to the Sales table, and I hope you guys are able to follow along. If I am speaking too quickly, if I’m moving too quickly and navigating, please, ping the chat. Tell Juan Carlos, hey, please, during a break, have Matt slow down. we’re moving too quickly. I’m happy to. I just want to make sure we get all this information in for you guys.

So, alright. We are in the Sales table now and we’re looking at making sure we want to add a new column. We have our sales figures, we have our, you know, the amount for Product Cost. We have the amount for Sales Amount, but we want to, let’s go ahead and add a new column that’s Profit. So what we’re going to do is we are going to create a custom column.

So, we’ll click on Custom Column. We will call this “Profit Column”, and then we’re going to start writing our code here, in Power Query Editor, this is called M Code. It’s very similar to DAX. It’s written in the same way, but it’s only used in Power Query. So this M code is transformable by you, to change the names. You can use it to change names of columns, you can use it to change data types, and you can write your custom queries here as well.

So we are going to take Sales Amount. So as I start typing, you see, it automatically says, here’s what you have available when you start writing Sales Amount. So Sales Amount minus Total Product Cost. Okay? That’s going to be our Profit. So what we sold it for, which was what we, you know, what the amount of the item cost, that’s going to be the Profit per item.

So Profit Column. And no syntax errors have been detected. Okay, let’s go ahead and hit Enter. And you can see on the applied steps we’ve added a column. Excuse me, we’ve added the column. It’s 100% valid. Looks like it’s shown up as a general type. We want to make sure again, that this is a fixed decimal number.

And you notice we’ve already done one transform, from the top, from data type, before. You can also do it in the header ribbon for each column. So, Power BI is great, it makes your workflow flexible. If you want to always make sure you’re going to the Transform tab and changing the data type, you can. If you just want to click on the columns, you also can. 

So we’re going to do fixed decimal number. Bang. There we go. So now we are set, we have our Profit Column created and you can see, you know, we’ve got our Sales Amount, our Product Cost. You see the math works out between the two. There we go. So now we have created our new column, done a couple transformations. Now I want to go ahead and say, alright, let’s lock these in. Let’s go ahead and close and apply. So all the way in the top left is close and apply. If you click on the actual words, you see the options are Close & apply, Apply, and Close. So if you’re working, you’re making several changes and you want to just apply these, but then make some more changes, you can certainly do it that way.

I just like to make my changes, close and apply. You can see Power BI working as your model changes. We did add that column, we changed those column types. So, the model is changed. and then go ahead and we’re going to move from the Report view onto the Table view, and we can see, alright, let’s make sure we look at our Sales table and let’s scroll over and we see, yep, our Profit Column is here.

That’s a brief look at some of the Power Query Editor features that you’re likely to use on a regular basis. Thanks for tuning in. We appreciate it here at Playfair+.