Query Editor in Power BI for Data Transformation
Introduction
From our previous articles, we know how to load data into our Power BI from various data sources. But that exact data is not enough to create our Power BI reports. We need to shape those data, that refine the data, add new columns, rename the columns, and many other improvements to create data models.
Query Editor
What is Query Editor is the first thing that strikes our mind? It’s simple, A Query editor acts as an intermediate data container where we can modify our data by selecting the rows and columns and doing necessary changes. But remember the changes that we make in the Query Editor do not reflect into the original dataset until we apply the changes.
Connecting with Data Source – SQL Server
Now, let us connect our Power BI with our data source SQL Server.
Fig. 1 Connecting Power BI with SQL Server Data Source
Once after connected, it will open a new window to choose the database and to choose the table. As an example, I am choosing PracticalWorks as my data and Detail.Employees as our table for preparing the dataset.
Fig.2 Choosing Tables to prepare Dataset
If we look into the screen, we have three options as Load, Transform Data, and Cancel. As we know from our previous articles, Load is used to load the data into the Power BI report. We can still use this option if we confided that there is no need of refining the data. That is from the source itself we are choosing the refined data.
The next option is Transform Data, when we choose this option, from the source, the data will be loaded into the new window which is our Query Editor. Now I choose Transform Data as my choice and a new query editor window is opened as shown below.
Fig.3 First look of Query Editor
The interface of the Power Query Editor
On the top left of the editor, we can see Untitled – Power Query Window. Once we save the Query Editor, then that name will be displayed over here.
Fig. 4 Untitled Power Query Editor
Just below this, we have a Ribbon tool with options as File, Home, Transform, Add Column, View, Tools, Help. We will explore one by one. The data that we have connected to our Power BI using Transform Data Option is visible under the Query Section (left side of the query editor). It will show the list of files that we are connected to from our data sources.
Fig. 5 Query Section
Now on the right side of the editor, We can see Query Settings.
Fig. 6 Query Settings
From the Name field, we can change the name of the file which is loaded from the data source. Applied Steps shows whatever changes we do in the dataset will appear here as a Step. We cannot do CTRL+Z in the Query Editor, so to go back to the previous stage, we have to click the cross icon in the steps which is in the Applied Steps.
Example
Manage Columns – Remove Columns
Now let’s remove the Salary column in the Editor. To remove the column, we can do it by selecting the Home tab, under the home tab, we have two options as Choose Columns and Remove Columns in the Manage Columns Section. Now select Remove Columns, and there are options as Remove Columns and Remove Other Columns. Remove Columns will remove the selected columns and Remove Other Columns will remove the other than the selected columns.
Fig. 7 Remove Columns in Power Query Editor
Now let’s choose Remove Column and Salary column is removed. If we look into the Applied Step on the right side of the screen, a new step is added.
Fig. 8 New Step added
If we want to undo the changes, then just click the cross icon in the Removed Columns step. The Salary Column will be added back into the Query Editor.
Choose Column
This option also has two options as Choose Columns and Go to Column. Choose Columns are used to choose the columns that are really needed. As said before, we may not need all the data from the data source. Only chosen columns will be displayed in the Query Editor.
Fig. 9 Lists all the Columns and we can choose the required one
Fig. 10 Only selected columns
Go to Column is used to navigate from one column to another column. Instead of scrolling the screen from left to right or right to left, we can easily navigate from One column to another column.
Fig. 11 Go to Column
Reduce Rows
The Reduce rows have two options as Keep Rows and Remove Rows. The keep Rows option will keep the rows that we are interested in, i.e., only those rows will be present in the Query Editor. We can do this by Keeping the top number of rows, or by the bottom number of rows, or else by giving the range.
Fig. 12 Keep Rows Options
Remove Rows will remove the rows from the query editor, i.e., it will remove the selected rows. We can do it by Removing the Top number of rows, or else the bottom number of rows.
Once the data refining is done, the changes will not appear immediately in our Power BI. We have to choose the Close and Apply option from the Ribbon Tool.
Fig. 13 Close and Apply
The changes will appear in our Power BI, when we see the Fields section, we can see those changes
Fig. 14 Changes applied to Power BI – See the Fields
Change Data Type of a Column
Before changing the datatype of a column, let's see what are the datatypes supported by Power BI. There is a total of nine different data types in Power BI,
- Decimal Number
- Fixed decimal number
- Whole Number
- Date/Time
- Date
- Time
- Text
- True/False
- Binary
Now, let's change the data type by using the Transfer Data option from the ribbon tool which will open the Query editor window.
Fig.15 Query Editor in Power BI to perform data transformation
From the above example, if we look into the Salary column, its datatype is as Decimal. If we go to the Transform menu and see the Data Type, it shows the current data type of the selected column.
Fig. 16 Example column with its datatype
If we want to change the data type of the selected column, from the Transform datatype, just click the down arrow and choose the accurate datatype for that column.
Fig. 17 List of Datatypes in Power BI
In our case, let's convert the decimal datatype to the Whole Number datatype.
Fig. 18 Datatype changed to Whole Number from Decimal
Conclusion
In this article, we have seen how to refine our data using Power Query Editor. There are more options in the Query Editor which we will discuss in our upcoming articles. Please share your feedback in the comment section.
Consider reading other articles
