Comprehensive Guide to Power Query Editor

In this article we are going to study about Power Query Editor. It is also known as Heart Of Power BI. All the pre-processing and Data cleaning part is covered in this Power Query Editor. 

So let’s start….!

The Application Ribbson contains all options and settings. Complete the steps:

1. Click the View tab from the ribbon. Make sure the following are ticked:

2. Formula Bar

3. Column quality

4. Column distribution

Note: If show white space is ticked, you can leave it as is.

The figure below shows the entire interface of Power Query Editor

As Figure illustrates application ribbons contain all options and settings, transformations, and other settings configurations. 

1. The left pane shows different Queries. These can be the tables you selected for import or custom functions, query parameters, or queries with constant values that you create in Power Query. 
2. The Column header shows the column name and data type (Number, Date, Text, True/False, etc). 
3. The Column quality bar shows details on the number of valid, empty, and error records in the data. 
4. The Column distribution bar provides counts of distinct and unique values. 
5. The Data displays the view of the data based on the data transformation step that has been selected. 
6. The Data Transformation Steps show a list of data transformations that have been applied to the data. 
7. The Status bar shows the number of columns the selected query from the Queries pane has, the number of rows, and data preview refresh date and time.
    

Splitting Columns

Power Query allows us to split a column into one or more other columns. There are various options available under the Split Column dropdown. 

We will be using By Delimiter to split the Name into First and Last Name. 

1. Go to the Queries pane on the left and select DimCustomer. 
2. Select the Name column. 
3. From the ribbon bar, go to the Transform tab, click Split Column and select By Delimiter.
    

1. In the dialog box, ensure Select or enter delimiter is set to Space. 
2. Select Each occurrence of the delimiter and click OK.

1. The Name column is split into Name1 which has the First Name and Name.2 which has the Last Name. The splitting column by delimiter action above creates a new applied step as shown in Figure below:

Renaming Columns

We will rename the two columns created by the splitting of the Name column. There are a couple of ways to do this: either double click the Header name or right-click the header and select rename. 

1. Double click the Name.1 Header and rename it to FirstName. 
2. Right click the Name.2 Header and rename it to LastName.

Adding Columns

Adding Columns is a common task and there are many ways to do this. There is a dedicated tab in the Ribbon bar for adding columns. In the next few steps, we use a Conditional Column and a Column from Example to add new columns. 

In the steps below, we will add a new conditional column - TotalChildrenNumber. 

1. Go to the Queries pane, select the DimCustomer query. 
2. Go to the Ribbon bar, select Add Column, select Conditional Column. 
3. In the dialog box, change column name from Custom to TotalChildrenNumber. 
4. Modify the If statement to read: If TotalChildren equals one Then 1. 
5. Click Add Clause. 
6. Add the additional ‘if’ clauses for the remaining values (the values in this dataset goes up to five. Remember that one is the only value that needs to be in all lowercase!). * 
7. Enter 0 for Else, this will assign 0 to any remaining records including those with the value ‘None’. 
8. Click OK and our new column will be added. 

Important: Note that Power Query is CAsE sEnSiTive. In this dataset, you’ll need to write ‘one‘ in all lowercase. For Two, Three, Four and Five, the first letter will be uppercase. 

In the next steps, we’ll add an Annual Income column: 

1. From the Queries pane, select the DimCustomer query. 
2. Go to the Add Column tab, select the Column from Example dropdown 
3. Select From Selection. This adds a new column called Column1 at the end  of the columns list. 
4. Select the YearlyIncome column.
5. In the first row in Column1, type in the same number as is shown on the first row of YearlyIncome. (Unless the table has been sorted differently, you should type in  90000, note you don’t need to type USD just the numbers.). 
6. Press Enter and all the rows will be updated. You may need to enter more rows for the rest of the column to populate. 
7. Rename Column1 to AnnualIncome (double click the column title to rename, note that in some versions of PowerBI your column maybe be renamed “YearlyIncome- Copy”). 
8. Click OK

Removing Columns

We can remove columns that we no longer need e.g. duplicate columns. This also applies to columns that are used to add new columns such as the TotalChildren and YearlyIncome. 

You can remove a column that has previously been used to create a new column. It does not impact on the data in the new column. 

1. Select the DimCustomer query from the Queries panel. 
2. Hold Ctrl and click the column headers for YearlyIncome, TotalChildren, SpanishEducation, FrenchEducation, SpanishOccupation and FrenchOccupation. 
3. Right click in any of the selected column headers and click Remove Columns.

Changing Data Types

Each column in Power BI has a data type. When connecting to a data source, Power Query loads 1,000 rows of data as sample data and automatically detects the data types. While in many cases the detected data types are correct, in some cases we may face data type conversion errors when the detected data type is incorrect. Therefore, it is advised to always review the data types before continuing with other transformation steps. For instance, storing a customer number as a number where it should probably be stored as a text field.

The most common data types and their column icon in Power BI are:

In the steps below, you’ll make a correction in the automatically detected data types: 

1. Select the DimCustomer query from the Queries pane. 
2. Go to the TotalChildrenNumber column and click the data type icon. 
3. Select Whole Number from the context menu. 
4. The data type icon changes to the Whole Number icon. 
5. Click the data type icon next to the AnnualIncome column. 
6. Select Fixed Decimal Number. This is similar to currency. 
7. The data type icon changes to the Fixed Decimal Number icon. 
8. Click the data type icon next to the HouseOwnerFlag column. 
9. Select True/False. 
10. The data type icon changes to the True/False icon. The 0 values have been set to False and 1 has been changed to True.

Transformation Steps

All transformations are applied as a series of steps shown in the Query Settings pane as shown in the Figure below.

It’s possible to see the resulting data of each step by clicking on a desired step from the Query Settings pane as shown in figure below. This makes it easy to visually inspect the data after applying each transformation step.

Transformations can be added, edited, removed, renamed, or reordered by right clicking a desired step from the Applied Steps selector in the Queries Settings as shown in figure below.

Depending on the data source type, the following transformation steps may already exist for each query: 

1. Source: The very first step of the query is normally connected to the data source. In this tutorial, the sources will be a Text/CSV source and the Excel Source containing the other tables. 

1. Navigation: If the data source we are connecting to has different objects containing data, such as an Excel workbook that can contain many worksheets and/or tables, then by selecting each object from the source system we are navigating through that object to get the containing data. For each Query, this will be the corresponding worksheet or table name in the Excel File, while Text/CSV files do not have a Navigation step as each file contains only one set of data.

So these was some of the important topics related to Power Query Editor. Hope you had fun. 

Thank you!!