In this article we will study what is power query editor in Power BI, why we are supposed to transform data, what’s the need to involve a query editor, how we can launch a power query editor on our Power BI desktop, and what data transformations we can perform. The list of data transformation operations we will study in this article is as follows:
-
Rename operation
- Rename data source
- Rename columns
- Rename query
- Change the datatype of the column operation
- Format operation
- Use the first row as the header operation
-
Removal operation
- Remove rows operation
- Remove columns operation
- Merge operation
- Replace value operation
- Split column operation
- Pivot column operation
- Unpivot column operation
What is Power Query Editor?
Power Query is a data processing and mashup software. The Power Query Editor is a part of Power BI Desktop. It can be in two modes as desktop or online. It’s a data transformation and preparation engine. It’s a tool that is useful 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.
Why is data transformation required?
In the scenarios when we upload data from varied sources be it the internet, any Excel file or SQL server, or any other source, generally the uploaded data is not in the desired format. So, in order to perform functions we first need to transform the data by either deleting some unwanted rows and columns, by splitting the columns, by formatting, or by making certain required changes. Therefore before loading the data directly on Power BI Desktop, we load the data on Power Query Editor to make the required transformations. After the data is transformed it’s then loaded to the Power BI Desktop to create final reports of the data.
Launch Power Query Editor
Under the home menu bar, there’s an option “Transform Data” that provides the query editor feature. On clicking the option transform data it navigates us to a new window called power query editor.
Launching Power Query Editor
Now we will add data to query editor to perform desired operations.
Importing data on query editor
On the query editor window, we can see a new source option under the home menu. Under the new source option, we are provided with the option to add data either from an Excel file, SQL server, or from the web, and so on. The data referred to here for illustration purposes is an Excel file named My movie list.xlsx and its data is as follows:
My Movie List.xlsx
On loading this data on the query editor it appears as:
Excel sheet in the power query editor
Now, since we have the Excel sheet and data imported on the power query editor as well, we are in a state to perform transformations.
Rename Operation in Power BI
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 :
Renamed source to “Movie Data”
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 :
Renamed column to “MOVIE NAME”
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 :
Renamed query to “Columns Name Updation”
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.
Set First Row as Headers
Change Data Types of a Column in Power BI
We can change or modify the data type of any column by right-clicking on the column which is followed by a drop-down under which we have to select the option change type which further provides us with varied suitable data types. Here, we have modified the data type of column “RANK”. Earlier, it was of “Whole Number” type which has been modified to “Decimal Number” by us. This can be observed from the image below as well, where before implementing the operation the numbers mentioned prior to the RANK are 1,2,3 i.e. symbolizing whole numbers. Whereas after the operation is being done those (1,2,3) changed to 1.2 i.e. indication of decimal numbers.
This same operation can also be achieved from an option present in the home bar of the ribbon as “Data type:<type>”.
Changed data type to “Decimal Number”
Format Operation in Power BI
The Format feature is provided in the “Transform” tab of the ribbon. It’s useful in formatting the text. We have further options for formatting the text like we can change the data to lowercase or UPPERCASE, also we can add prefixes or suffixes. Formatting has other options as well like trim and clean. Trim is used to remove leading and trailing whitespaces for each data entry of the specifically selected column. Clean is used to remove nonprintable characters of selected columns. Performing all these operations is very simple as it just involves clicking over the option. And so, we have performed only UPPERCASE operation here. Rest all can also be done in the same fashion.
Formatting the text to UPPERCASE
Removal Operation in Power BI
Remove Row Operation
The removal of rows operation is supported by the “Reduce Rows” block. This feature is present in the “Home” bar. The removal of rows operation has several sub-operations like removing rows(removing the top, bottom, or alternate rows) and columns, removing duplicates, removing blank rows, and removing errors. Here is an illustration performing the removal of the bottom-most row as follows. Rest all removal operations can also be performed in the same fashion.
Removing the bottom row
“Reduce Rows” Block
Please note that the “Reduce Rows” block itself is no operation. It provides the features like “Keep Rows” and “Remove Rows”. We have illustrated the “Remove Rows” operation above. On the same lines the “Keep Rows” operation is performed. It does the opposite of what “Remove Rows” does. It rather than removing the rows provides us with the feature to keep or don’t remove the rows as per the user’s wish.
Remove Column Operation in Power BI
The removal of columns operation provides us with the feature to remove columns or multiple columns. Remove column is present in the “Home” bar. The “Remove Columns” feature is supported by the “Manage Columns” block. On clicking Remove columns it provides whether you wanna remove the selected column by clicking on “Remove Columns”. We can also remove the rest of the columns other than the selected one by selecting the option “Remove Other Columns”. Here, we will illustrate the “Remove Columns” by removing the column “RATING”. After the operation will be performed “RATING” will not exist anymore. The “Remove Other Columns” can be performed the way.
Removing Column Rating
Output:
“RATING” column has been successfully removed.
“Manage Columns” block
“Manage Columns” block has two functions 1.Choose Columns 2.Remove Columns .The 1st “Choose Columns” helps us to keep whatsoever columns user wants to keep. On the other hand, 2nd “Remove Columns” helps user to remove columns user doesn’t need anymore. The “Remove Columns” operation is illustrated as above. The “Choose Columns” can also be done the same way. Both the features perform opposite operations to each other. One helps to select particular columns(1) while the other helps remove specific columns based on user’s needs.
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 :
Merging Columns GENRE and RATING
Output:
Merged Column
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”.
Replacing values of column “GENRE”
Replacing “null” to “geeksforgeeks”
Output:
Successfully Replaced
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 :
Splitting column “Merged”
Before
Before Split Scenario
After
After Split Scenario
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.
Pivot column “Field 2”
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:
Unpivot operation implemented on Field 1 and Field 2 both
Output:
On unpivoting both the columns