Power Query Editor in Power BI

Power Query Editor is a tool in Power BI Desktop used for data transformation and preparation. It allow to connect, shape and transform multiple data sources according to the user's needs. After making the desired changes the transformed data is then loaded to the Power BI desktop to fetch final outputs and reports. It can be in two modes as desktop or online.

When we upload data from various sources be it the internet, any Excel file or SQL server or any other source the uploaded data is not in the desired format. Data transformation helps clean and organize the data by removing unnecessary rows, splitting columns, and changing formats before loading it into Power BI for analysis.

Launching Power Query Editor

To open the Power Query Editor click Transform Data under the Home tab in Power BI Desktop. This will open the editor where you can load and transform your data.

Now we will add data to query editor to perform desired operations.

Importing Data

In the Power Query Editor click New Source to add data from different sources like Excel, SQL, or the web. After importing you can perform necessary transformations on the data. The data referred to here for illustration purposes is an Excel file named My movie list.xlsx and its data is as follows:

On loading this data on the query editor it appears as:

Now since we have the Excel sheet and data imported on the power query editor as well we can perform transformations.

Common Data Transformations

1. Renaming Elements

The power query editor provides us with a variety of possible renaming. We can rename the data sources or tables, columns and queries. We will look into each of them one by one.

Rename Data Source 

It can be achieved by right-clicking on the source and opting for the rename option. Here, we have renamed the data source from sheet 1 to Movie Data. On transformation, we get :

Rename Column

It's also done by right-clicking the column in which you wish to change the name and selecting the option of renaming and renaming as per the wish of the user. Here, we have renamed the column "TITLE" to "MOVIE NAME". On renaming the column it appears as :

Rename Query

Under the query settings pane an "Applied Steps" section under which all the changes we made are stored as queries. Using the query editor we can rename the queries also. Here, we have renamed the query "Renamed Columns" to "Columns Name Updation". On renaming it appears as :

2. Make First Row As the Header in Power BI 

This operation is used to set the first row as the column header. This option is available under the "TRANSFORM" of the ribbon. It has another option of "Use Headers as First Row" as well.

3. Change Data Types of a Column in Power BI

To change a column’s data type in Power BI, right-click the column, select Change Type, and choose the appropriate data type (e.g., "Whole Number" to "Decimal Number"). For example, we changed the "RANK" column from whole numbers (1, 2, 3) to decimal numbers (1.2, 2.3, 3.4). You can also change data types from the Home tab under "Data type"

4. Format Operation in Power BI

The Format feature in the Transform tab helps format text in Power BI. You can change text to uppercase or lowercase, add prefixes or suffixes, and remove leading or trailing spaces using the Trim option. The Clean option removes non-printable characters. To use these simply click the relevant option. In this example we applied the UPPERCASE transformation but the other options work the same way.

5. Removal Operation in Power BI

Remove Row Operation

The Reduce Rows feature in the Home tab allows you to remove rows in Power BI. You can remove top, bottom, or alternate rows, as well as duplicates, blank rows and errors. For example you can easily remove the bottom row and other removal operations work in the same way.

Please note that the "Reduce Rows" block in Power BI offers two options: "Remove Rows" and "Keep Rows". We have illustrated the "Remove Rows" operation above. On the same lines the "Keep Rows" operation is performed. The "Keep Rows" feature lets you select the rows you want to keep while "Remove Rows" deletes unwanted rows.

Remove Column Operation

The Remove Columns feature allows you to delete one or more columns. It's found under the "Home" tab in the Manage Columns section. You can click "Remove Columns" to delete the selected column or use "Remove Other Columns" to delete all columns except the one you selected. For example if you remove the "RATING" column it will no longer appear in your data.

Output:

The Manage Columns block has two features: Choose Columns and Remove Columns. Choose Columns lets you keep the columns you want while Remove Columns lets you delete the ones you don't need.

6. Merge Operation in Power BI

We can merge multiple columns. To select multiple columns hold down the Ctrl key, navigate to the columns you want to be selected using the left and right arrows, and then press the Space bar to actually select those columns. The "Add Column" bar supports the "Merge Columns" feature which is followed by a prompt of merge columns that asks for the name of the merged column and to set the separator. Here, we have merged the columns "GENRE" and "RATING". We have used a custom separator " ;) " and kept the name of the merged column simple as "Merged". The illustration along with the output is as follows :

Output:

Replace Values Operation in Power BI

Replace values operation replaces some specific value to our desired value. It's present in the "Transform" bar as "Replace Values". Here, we have replaced "null" to "geeksforgeeks" for column "GENRE".

Output:

Split Column Operation in Power BI

It's present in the "Transform" bar on the ribbon. In the transform bar lies an operation as "split column". We can split either by using a delimiter, by providing no. of characters or positions, and so on. Here, we have split the column "Merged" using the customized delimiter " ;) " that splits the column into two columns "Merged.1" and "Merged.2". The illustration is as follows :

Before 

After

Particularly for PIVOT and UNPIVOT operations, we will refer to some other examples as the data we have been using so far will produce huge output and would not be so comprehensible and also can't be uploaded here.

Pivot Column Operation in Power BI

The pivot operation basically turns rows into columns. By default, the query editor does sum as aggregation which can also be set as don't aggregate or minimum or maximum or whatever as per the user's wish from available options. It is present in the "Transform" bar.

In the output we see 9.3 corresponding to 2 and not multiple values such as 1.1, 4.3, and 3.9. It's because we have set the aggregate value function as sum i.e. it will sum all the values of 1.1, 4.3, and 3.9 which is "9.3". Also, for 3 and 9 we have no repetitions so data corresponding to them is shown as "2.6" for each respectively.

Unpivot Column Operation in Power BI

Unpivot column operation as the name also suggests does the opposite of what pivot does. Unpivot basically unpacks similar values and gathers them under one label. When we did unpivot on the same data we took for the pivot operation it produces the output:

Output: