Power BI Beginner Level: 11 - Basic Transformations in Power Query

Table of Contents?|?Sample Input?|?Power BI Report File

Short summary:?In the Power Query editor in Power BI, you can easily apply transformations to your input data. The steps get recorded, so you can come back at any time and change your query as desired. You can also preview the result of each step and refresh the data with just one click.

In the previous two articles, I showed you how to merge and append tables. Now let us do some simple transformations on the merged data, all of which can be found in the Home ribbon in the Power Query editor. You can download the sample input to follow along.

By this point, you should know already how to import a table from an Excel workbook into Power BI. By the way, loading the data from a database would work almost the same way.

Let us continue by sorting the rows by Transaction ID. Click on the dropdown on the right of column Transaction ID and choose Sort Ascending.

You will see that the step is applied and added to the Applied Steps in the right panel. As we established in the first few articles, a major advantage of Power Query over regular Excel is that Power Query remembers all the steps and you can revisit each step and edit or delete as you wish as well as refresh your data with just one click (or automatically).

Whenever we perform a transformation, Power Query remembers the step and it also assigns each step a default name. It’s best practice to still rename steps if you can find a more meaningful description than the default step name. That way, if you or another user look at the query again, possibly weeks or months later, you can easily understand what’s going on by looking at the step names and clicking on each step to see what it does. Let’s rename the step to “Sorted by Transaction ID” by right-clicking the step and then choosing rename (or pressing F2).

Next, we see that for Product D, we don’t have any data for the columns on the right. There are several ways of dealing with missing data, for now let’s just filter out the row(s) with empty Product name. To do that, select the dropdown next to the Product name column, then click Remove Empty or alternatively unselect the (null) in the item list and click ok. Note that once you filter out data here, it will not be loaded into Power BI. However, if you change your mind, you can easily come back and edit that step of the query and refresh the data.

Next, let's assume we want to delete the Product column because we already have the column Product details. Let’s do so by clicking the Product column, then selecting Remove Columns in the Home ribbon. There are often several ways in the Power Query editor to perform the same action. Here, you could alternatively right click to column and select Remove or you could press the delete key on your keyboard.

As you can see, it’s really simple to perform basic operation in Power Query. Try for yourself using Replace Values in the Home ribbon to rename one of the sales representatives. In the next article, we will continue with adding a custom column.

Please like, share, and subscribe and feel free to ask questions in the comments below.

Next article:?Adding Calculated Columns