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 is refine the data, adding new columns, renaming the columns and many other improvements to create a data models.
Query Editor
What is Query Editor is the first thing that strikes in 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 does not reflect into the original dataset until we apply the changes.
Connecting with Data Source – SQL Server
Now, lets connect our Power BI with our data source SQL Server.
Once after connected, it will open a new window to choose the database and to choose the table. As an example I am choosing the PracticalWorks as my data and Detail.Employees as our table for preparing the 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 our Query Editor. Now I choose Transform Data as my choice and a new query editor window is opened as shown below.
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 display over here.
Just below to this, we have 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 from our data sources.
Now on the right side of the editor, We can see Query Settings.
From the Name field we can change the name of the file which is loaded from 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 in order 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 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.
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.
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.
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.
Reduce Rows
The reduce rows has two options as Keep Rows and Remove Rows. 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 top number of rows, or by bottom number of rows or else by giving the range.
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 row, or else bottom number of rows.
Once the data refining is done, the changes will not appear immediately in our Power BI. We have to choose Close and Apply option from the Ribbon Tool.
The changes will appear in our Power BI, when we see the Fields section, we can see those changes
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 feedbacks in the comment section.
Learn With Sundar 
Leave a Reply