Query Editor In Power BI | An Overview

In this tutorial, we’ll show a detailed example of why we always need to use the Query Editor in Power BI and what we can achieve when using it.

It’s deemed a best practice to always bring your data into the Query Editor first.

We’ll show and prove it by running through some examples and issues that we might run into, and the way that data would look like if we don’t use the Query Editor.

Table of Contents

Why You Always Need To Use The Query Editor In Power BI

We can now import that data into our data model by clicking the Get data option. This option makes it easy to import files on our data model. However, we don’t really suggest doing this because we should always clean up and optimize our data on the Query Editor first.

Just to show an example on why it’s not a good idea to bring in our data directly to our data model, let’s import a sample file by clicking on the Get data and click the Excel workbook.

For this example, we’ll use this sample excel data file about Manufacturing Operations. Double click the file to bring it into our data model.

Inside the Navigator, we’ll select this Operation_Data table and click the Load button.

Then, It’ll immediately commit our table to our data model. This is where we might encounter a few problems. This is because committing a data file that’s not optimized yet could have some errors that we weren’t able to adjust or fix before reaching our data model.

We can just close it by clicking the Close button.

After that, it’ll continue to commit this unoptimized data with errors on our data model.

By looking at this data on our data table, we can see how massive this table is. This is not how we want our data to be inside our Power BI. There’s also probably a lot of redundant columns, or information that we don’t need inside this table that should be deleted first. What we want is to have optimized and clean tables to be able to build a model around our data.

This is just some of the many reasons why we should always bring our data to the Query Editor first.

Data Transformation Using Query Editor In Power BI

This other sample spreadsheet looks messy from a Power BI perspective. If we look at this one, there’s nothing good that we can do in Power BI with all these mini tables, redundant titles, and some irrelevant information. So, what we need to do first is to bring this into the Query Editor. Then, try to optimize and clean it up.

I’ll show you how we can clean this spreadsheet or worksheet effectively by layering transformations on top of each other. Again, don’t just bring the data directly to our Power BI. Always transform or clean up your data on the Query Editor first.

To open the Query Editor, just click the Transform data option.

Inside the Query Editor, import the file by clicking New Source. Then, choose the type of the file that you’d like to query. For this example, we’ll click the Excel Workbook because we want to query a spreadsheet or worksheet file.

After that, choose the file that you want to query. In this example, we’ll query the iPhone6 excel file.

We need to clean the entire worksheet so we’ll select the Sheet1 and click the OK button.

Then, we’ll see all these redundant columns, information, and null values that we need to clean up. This is a great example for showing what we can achieve inside the Query Editor. In the next steps, we’ll clean and optimize this table.

Let’s first rename our table. Under the Properties, rename this table to Sales Data.

Then, we can start cleaning up this spreadsheet in a number of different ways and orders. For this tutorial, we’ll start off by removing the null values.

1. Removing The Null Values

We can easily remove the null values by clicking the arrow down icon of the columns with null values.

Then, uncheck the (null) under the Filters option, and click the OK button.

Now, we’ll see that the null values are gone.

2. Removing The Redundant Information

The next thing we can do is to remove the redundant information within these rows of our table. We currently have the ItemNumber, ProductName, ClassificationGroup, and some range of dates. We’ll remove these repetitions and bring the first row up to the header instead.

To do that, click the Transform tab. Then, select the Use First Row as Headers.

As a result, those items became our actual headers which is what they should actually be.

After that, we can now remove the same items on our rows by unchecking them on our Filters. For the ItemNumber column, we can uncheck the ItemNumber option on its filters.

What’s good about this is it automatically hides all those particular items from other adjacent columns on those rows. As we can see, those redundant items on other columns are now gone as well. We now have a cleaner data table.

3. Unpivoting Columns In Power BI

Another thing that we can fix here are the multiple date columns. What we can do is to put them inside one column instead. We can do that by unpivoting columns.

First, select the columns that we don’t want to unpivot. Then, right click on the last selected column and choose Unpivot Other Columns.

We now have our dates inside one column and the sales amount on another column as well.

We can now rename these columns as Dates and Sales Amount.

Click on the data type icon on the left side of the Sales Amount column. Then, change its data type to Whole Number.

Similarly, click on the data type icon on the left side of the Dates column. Then, change its data type to Date.

4. Removing Errors In Power Query Editor

For some instances, we might encounter some errors while cleaning up our data. In this example, we have errors under the Dates columns.

This is due to having text values before changing the data type of the columns to Date. As shown from the example below, there’s a summarized information inside the Date column.

We can easily remove those errors by right clicking on our Dates column and choosing the Remove Errors option.

As a result, it’ll remove all those errors so we won’t have them on our table anymore. Our table is now much cleaner.

5. Turning A Query Into An Actual Table

Lastly, we need to turn it from a query into an actual table and commit it. Just click the Close & Apply option under the Home tab.

We now have a perfect and optimized table for Power BI. This table looks great in terms of running all of our DAX formula and patterns.

Conclusion

To recap, we’ve taken an ugly worksheet of data and turned it into a refined table that’s perfectly optimized for Power BI. We can now build a model around it.

Hopefully, this tutorial was enlightening in terms of what we can achieve using Query Editor in Power BI and why we should always bring our data to the Query Editor first. Even if we only have a small table, there could be something as small as just changing a column name or column data type. We’d want to get used to doing things in the Query Editor first so we can work with various transformations and cleaning procedures to start getting our data optimized.

All the best,