How to Use the Power Query Editor

Hello again, for my second blog, I will proceed with the continuation of my initial blog titled “How to Import Excel Data into Power BI.” In this blog, I will delve into providing an explanation on how to use some of the different abilities that the Power Query Editor can do. There’s no time like the present so let’s get right into it.

To use the file I am working on click here.

Now that the data has been successfully loaded into Power BI, the next step is to make necessary modifications to the data, enhancing its usability and manageability. To do this, we need to have the Power Query Editor open. But how do we open the Power Query Editor? To begin this journey, we will start on the “Report View” in Power BI. Next, click on the “Transform Data” button, highlighted in red as seen in Figure 1 below.

From here you may run into the same issue I did but it has an easy fix. In Figure 2, you can see an error message that popped up on my screen (highlighted in red) along with question marks next to all the tables that I imported (highlighted in blue). To fix this all you need to do is click the “Retry” button (with the arrow pointing to it) for each of the tables.

Moving on, we need to start modifying our data because Power BI doesn’t always correctly identify what specific data types that each column needs to be. To start cleaning up our data, we will begin in the “FactResellerSales19 Table”. Notice, if you scroll across the columns you will start to see columns like “UnitPrice”, “TotalProductCost” and others that seem to be incorrectly identified. This is because Power BI automatically identifies them as the “Decimal Number” Data Type (Labeled as 1.2) or the “Whole Number” Data Type (Labeled as 1²₃). We need any column that would indicate money or currency spent of some kind to be identified as the “Fixed Decimal Number” Data Type (Labeled as $). To do this, you will need to select the column(s) you want to change the Data Type of. To select multiple columns, you can hold the CTRL button on your keyboard and click on the column headers. The columns that I am changing are shown in Figure 3.

After selecting which columns, you want to change the Data Type for, you will need to click the drop-down arrow next to the “Data Type: (Whole Number or Decimal Number)” button (highlighted in red in Figure 4) and then select the “Fixed Decimal Number” Data Type.

Once you click the Data Type you want to transform the column(s) to, you need to be careful because you may get a pop-up like this (Figure 5). When you encounter this, you want to select the “Add new step” button (highlighted in red). By clicking the “Replace current” button this could ruin what data has already been transformed.

Now that you have added the new step, you’ll notice that there has been a change. If you look at the pane on the right side of your screen, there is an “APPLIED STEPS” section (Figure 5 above). In here there is now an entry that is labeled as “Changed Type 1” as well as an X next to it. This is probably going to blow your mind but the new entry shows that there is a new step being applied to the data transformation. WHAT?!?! Yeah it’s crazy I know. Anyways, the X next to it allows you to remove steps from this section as well allowing you to undo different changes that you have made.

The next transformation we are going to make is to delete a column. If you click on the drop down arrow next to “UnitPriceDiscountPct” column you will see that a warning sign shows up saying “List may be incomplete”. This is because the data loaded into the Power Query Editor is usually just the first 1000 rows. To get rid of this, click on the “Load more” button (highlighted in red in Figure 6).

You’ll notice that even after loading the rest of the data, the only entry in the column remains “0”. So, we are going to delete this column from the table to reduce the size of the model. To do so, you will need to make sure you are on the home ribbon of the Power Query Editor, your next step will be to click the column header to select the entire column and click on the “Remove Columns” button (highlighted in red in Figure 7)

Now for the next transformation we will tackle, we are going to move to the “DimDate4” Table and if you look through the columns you will see a few columns that are titled “EnglishDayNameOfWeek”, “EnglishMonthName”. To get this into a better-looking format we’ll need to be in the Transform ribbon. From here we’ll click “Rename” (highlighted in red as shown in Figure 8) and proceed to remove the “English” part from “EnglishDayNameOfWeek” as well as “EnglishMonthName”.

To finish cleaning up this data for the time being you will just need to repeat the same steps in the other tables: Delete columns containing only “0” or “NULL” values. In this data set I also left some columns that use other languages, as you are going through the data, delete columns representing other languages. Change Data Types to their correct forms and rename columns for easier readability.

Finally, it is essential that the transformations have been saved and applied. To save and apply changes, go to the “Home” ribbon. From there, click on the “Close & Apply” button (highlighted in red in Figure 9 below). Now that you have saved the changes, this concludes the transformations we’ve made and uploads this data into our Data Model, a topic I’ll talk about in my upcoming blog post.