For this tutorial, I’ll teach you my best practices while working on the Query Editor. This is an important feature of Power BI which, in my opinion, is under-utilized.
Nowadays, they call the query editor the Power Query Editor, which is a much better name than what they initially had called it.
Through the query editor, we can retrieve data from multiple sources.
To access the Query Editor, click on the Edit Queries tab from the Home ribbon.
I recommend always going to the Query Editor first. You need to transform your raw data into an optimized form before you can do anything in Power BI. This is where you can transform and do back-end configurations for your data before you actually load them into Power BI.
Launching The Power BI Query Editor
Before you can work on your data in Power BI, you obviously need to bring in your raw data first. There are two ways to bring data into Power BI.
Most users click Get Data to get their data from the most common locations such as Excel.
New users can click More to get data from various data source locations.
But the most essential way to source your data is through the Query Editor, and not through Get Data.
I always recommend getting your data by clicking Edit Queries and launching the Query Editor. Through this technique, you can query your data before doing anything else.
This is what I always recommend because there are other things that you can do in the Query Editor that are not possible if you simply use Get Data.
This option allows you to do some optimization and cleaning of your data tables, such as changing a column name, reordering columns, or deleting columns.
That’s the main reason why I don’t recommend sourcing your data using Get Data. With Get Data, you are physically getting the data straight away to your Power BI model and bypassing the query aspect of retrieving information.
Getting Data Using New Source
After launching the Query Editor, you now need to get your data. This time, you need to click New Source.
This is somehow similar to getting your data from various sources when using Get Data.
You can also see other database locations such as Azure, SAP, or Dynamics 365 in the drop-down list when you click More.
You can bring in data from different sources using New Source. To demonstrate this technique, I’ll use my Excel file and show you how to go about this step. After clicking New Source, select Excel, and then double-click the applicable file.
After that, you can see the Navigator window where you can select the options that you want to display in your model.
The first seven items have a different icon because they are tables while the other items are actual worksheets. What I recommend when working on your data in Excel is to make sure it’s all in a table format. This makes it easier for Power BI to retrieve your data.
After you select the data tables, click OK.
The Query Editor will now take a screenshot of your data. You can use this to clean and optimize your data without having to use so much memory in Power BI.
Transforming Your Data
Now that you can see all the queries from your source file, you can click on any item to see the complete information.
This is now the time to transform your data using different options under the Transform ribbon.
When you right-click on a certain column, you can see the following options below:
You can clean up your data tables, add or remove duplicate columns, reorder columns, rename tables, pivot or unpivot columns, and much more. You can layer different techniques onto one another and do more automated transformation work.
Finally, click Close & Apply to transfer this data into your Power BI model.
***** Related Links *****
My Practice Tips When Using The Power BI Advanced Editor
Unpivot And Pivot Basics In Power BI – Query Editor Review
How To Set Up Your Financial Data in Power BI
Conclusion
Using the Query Editor can help you work on your data more efficiently. I hope you apply these techniques going forward.
It’s essential to build the foundations of your model, which is why I want you to use Query Editor. Everything will be easier for you once you get to master this.
Stay tuned for more educational content.
Sam
