Power Query Editor in Power BI

Power Query Editor Power Query is a powerful Power BI tool that takes care of Power BI operations in locating, connecting, and loading diversified data across various sources. It also shapes and transforms the imported data so that it can be used to create visuals. It is also referred to as a self-service ETL tool in Power BI since it is self-sufficient in extracting, loading, and transforming data.

How to use Power Query Editor

The Power Query Editor is an environment for us to work with Power Query. Let us have a look into the Power Query Editor and understand its various parts and their purposes.

·?????? To open the Power Query Editor after logging in to the Power BI Desktop, go to the?Get Data?option in the?Home?Menu ->?Blank Query

Since no data has been imported yet, the Power Query Editor opens blank.

1.???????????????? Queries Pane:?It shows a list of all the active queries in the project. A query can be seen as a container with multiple data transformation steps, done with code (M-Language) or the GUI. On selecting any query, its transformed data can be seen in the center pane. Double-click on the query tab to change its name or drag them to change their order. You can also do non-repetitive tasks of the query by simply refreshing it, or copy-pasting it into another query.

2.???????????????? Formula Tab:?This tab will display a short snippet of the latest M-Language commands related to the transformations that were performed on the data in a particular query or the steps within a query. Even if the transformation is made using GUI, the formula tab will show the backend M code snippet that was applied. You can expand the tab to see the entire command.

3.???????????????? Query Settings:?This section on the right shows all the information of the selected query from the Queries pane. It consists of two sub-sections:

·?????? Name Section?shows the current query name which we are working on

·?????? Applied Steps?keeps showing the transformation steps being performed in the query one after the other. A query step will work on the output of its previous query step in the list. Hence the order of these steps is important. Steps of a query within?Applied Steps?can be arranged in any order, renamed, and deleted with a right-click on that step. A query will be executed as its steps are ordered in the?Applied Steps?section.

On clicking any query in the Queries pane or its steps in the Applied Steps, the particular modification on the dataset can be seen in the center pane. However, the underlying data is?not?changed in the Editor, it manages to create views of data according to the selected query or a transformation step within a query.

Importing Data in Power Query Editor

Let us load a sample web data into it to get more insights on Power Query Editor features.

Our Editor now looks much more active and interactive. The dataset can be seen imported at the center in the Data Pane. Let us study the changes it developed:

1.???????????????? Queries Pane:?A new query tab named Tach Stocks (name of our dataset) has been formed.

2.???????????????? Name section (Query Settings):?This shows the query name we are currently into.

3.???????????????? Menu:?This ribbon shows many functions and operations that we do with our dataset.

4.???????????????? Column Names:?It shows us the dataset's column names and symbols indicating their datatype. It also contains an arrow button to select the entire column and do some column operations.

5.???????????????? Applied Steps section (Query Settings):?It shows all the transformation steps that took place in the query. Although we have performed a simple query of importing our dataset, the Power Editor has automatically performed a few query steps for us such as putting the names of the columns as their headers in the original dataset and assigning appropriate data types to all columns.

·?????? On clicking any of the query steps, we can see a view of the data as an output of the transformation steps applied from the first query step till the selected query step (inclusive) E.g. on clicking the step names ‘Promoted Headers‘we will be able to see the dataset having its headers as column names but with unapplied datatypes on the columns. The ‘Changed Datatype shows all the query steps are done on the data: from its import to its column datatypes changed accordingly.

6.???????????????? Formula Tab:?When we are on the?‘Changed Datatype, the tab shows the M-Language equivalent code snippet that was applied in the backend for the transformation to work.

The Advanced Editor

This Editor is the main place to try out your queries in the form of M-Language code. It also shows the code that the Editor has created for a particular query or query step.

·?????? Click the?Advanced Editor?button on the?Home?Menu

You can see the existing code in the Advanced Editor or start writing your own query. In the image below within the Advanced Query Editor, the highlighted part of the code is the most recent transformation applied to the dataset which is exactly reflected in the Formula Tab above. As discussed above, you can see that the M-Language in the Advanced Editor consists of ‘let’ and ‘in’ block statements.

To close the window, select the?Done?or?Cancel?button.

?

Working With Power Query Editor

Let us transform the dataset a little to further clarify our concepts discussed earlier. To create a new query:

·?????? Right-click below on the gray area of the?Queries Pane?->?New Query?->?Blank Query

We use the existing query created earlier. Let us perform simple data transformations and see how Power Query Editor works on them. Execute these operations with the GUI and in order:

·?????? Sort Data column in descending order

·?????? Filter AAPL, GOOG, and MSFT Columns respectively to remove null value rows.

You can see in the?Applied Steps, the order of the query steps (we have renamed those steps for your better understanding). It stops on the latest step we applied i.e., filtering the MSFT Column. The underlying M code for this latest step is shown in the?Formula Tab?too.

Imagine a business environment having real-world messy and huge data, to do vast transformations to build a perfect data model for visualizations. They have to keep a track of queries applied, replicate or backtrack to previous steps, and also order similar or logical steps into a single query. This is where the real strength of Power Query can be applied to make the work easy and fast.

Power Query is thus effective in manipulating data and storing all the previous query information to be reused later. Power Query has many more benefits in transforming data with the M-Language to create more powerful and complex data transformations.

Save Changes to Power Query

After performing all operations in the Power Query Editor, we need to apply and save changes to the data model in our Power BI Desktop before we exit the Power Query Editor.

·?????? Go to the?File?menu in the Editor ->?Close & Apply

This only saves your imported and transformed data in the?Data Model?button on the left of the Power BI Desktop. When you are satisfied with your data model, you need to?save the project?before closing the Power BI Desktop application.

·?????? Go to?File?menu ->?Save/Save As?to save your Power BI Project

?