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 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.

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 the 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 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

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.

Fig. 4 Untitled Power Query Editor

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.

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 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.

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 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.

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 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.

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

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.

Consider to read other articles

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Website Built with WordPress.com.

Up ↑

%d bloggers like this: