Power Query User Interface

Understand the Power Query Editor before you start transforming data

Introduction

The Power Query Editor is where you connect to data, clean and transform data,  and load the data into Excel or Power BI. In both Excel and Power BI, the Power Query Editor opens as a separate window.

Power Query Editor has a user-friendly interface where you can choose the command that matches your task. Power Query then generates the M code for you.

So before you start cleaning and transforming data in Power Query, it is useful to understand the Power Query Editor user interface.

This lesson gives you a practical tour of the main areas of the Power Query Editor, so that you know where to look for the options you need.

How to open Power Query Editor in Excel

In Excel, you may not see a separate tab called Power Query on the Ribbon. Instead, Power Query commands are available under the Data tab, mainly in the Get & Transform Data group. Once you open the Power Query Editor, the window title clearly shows that you are working in Power Query.

To open Power Query in Excel, go to the Data tab and choose one of the available data options, such as From Table/Range.

Get Data on the Excel Ribbon

We will discuss the different data sources available in Excel Power Query in a later lesson.Power Query Editor appears in the title in the Power Query Editor window.

If you have already created a query in the workbook, you can reopen it from the Queries & Connections pane. Go to

Data → Queries & Connections

Then right-click the query and choose Edit.

Queries and Connections Pane in Excel

This opens the selected query again in the Power Query Editor.

How to open Power Query Editor in Power BI

In Power BI, the data options are available on the Home tab. You can connect to a data source by selecting Get data from the Data group. e.g., Home → Excel workbook.

After you connect to a data source, Power BI can open the Power Query Editor so that you can clean and transform the data before loading it.

Power BI also allows you to open the Power Query Editor directly, even before adding any data. In Excel, you usually open Power Query by first choosing a data source or editing an existing query.

To open the Power Query Editor directly in Power BI, go to Home → Transform data.

Power BI Get Data

This opens the Power Query Editor, where you can create new queries.

Once the Power Query Editor is open, the interface is very similar to the Power Query Editor in Excel.

Excel and Power BI Work Slightly Differently

In Excel, the Power Query Editor opens as a modal window. While it is open, you cannot directly work in the Excel worksheet.

In Power BI, you can switch between the main Power BI Desktop window and the Power Query Editor window.

The interface is similar in both tools, but the window behavior is slightly different.

Power Query in Excel and Power BI

Power Query is available in both Excel and Power BI.

The Power Query Editor screen may look slightly different depending on whether you are using Excel or Power BI, but the commands are the same.

Both Excel and Power BI use the same Power Query engine and the same M language.

This means that once you learn Power Query in Excel, you have also learned an important part of Power BI.

A query created in Excel can be copied to Power BI, and a query created in Power BI can be copied to Excel. This is very useful when you need to produce reports in both Excel and Power BI. You do not have to duplicate your effort. In many cases, you can build the query once and reuse the same transformation steps in the other tool.

Important Note: Power Query is Case-Sensitive

Power Query is case-sensitive.

This means that uppercase and lowercase letters are treated as different values.

For example, Power Query treats these as different values:

Sales
SALES
sales

This is important when you are filtering, merging, grouping, replacing values, or comparing text.

For example, if one table has North and another table has north, Power Query may not treat them as the same value during a merge.

So, before comparing or combining text columns, it is a good habit to make the text consistent.

You can do this by using options such as:

Transform → Format → lowercase
Transform → Format → UPPERCASE
Transform → Format → Capitalize Each Word

Main Areas of the Power Query Editor

The Power Query Editor has 5 main areas as shown below:

5 Areas in Power Query Editor

    1. Ribbon with four tabs – Home, Transform, Add Column, and View.  These tabs have a wide range of commands that are easy to use while creating queries.
    2. Queries Pane: This lists all the queries that are created in the current Excel workbook or Power BI workbook.
    3. Data Preview Area: This is the main workspace where you can view the data and apply transformations.
    4. Formula Bar: This is similar to the Excel Formula Bar, but it displays the M code for the current step.
    5. Query Settings Pane: This pane has two important sections: Properties and Applied Steps. Properties shows the name of the current query. Applied Steps shows the transformations applied to the current query.

Power Query Editor Ribbon

The Ribbon appears at the top of the Power Query Editor.

It contains the main commands used to clean, transform, combine, and manage data.

The four tabs are:

Home
Transform
Add Column
View

Home Tab

The Home tab contains commands used to start, manage, combine, and finish query work.

Power Query Editor Home Tab Ribbon

It includes commands for removing rows and columns, changing common settings, merging or appending queries, and closing the Power Query Editor.

Think of the Home tab as the main control area of Power Query.

Transform Tab

The Transform tab contains commands used to change the selected table or column.

Power Query Editor Transform tab

Some commands work on the entire table. Other commands depend on the selected column type, such as text, number, date, or time.

Use the Transform tab when you want to change the existing column itself.

Add Column Tab

The Add Column tab contains commands used to create new columns from existing data.

Power Query Editor Add Column tab

For example, you can create a custom column, conditional column, duplicate column, index column, or new columns based on text, number, date, or time values.

Use the Add Column tab when you want to keep the original column and create a new result column.

View Tab

The View tab controls what you can see in the Power Query Editor.

Power Query Editor View Ribbon

It includes options such as the Formula Bar, Query Settings pane, Column Quality, Column Distribution, and Column Profile.

Use the View tab when you want to show or hide helpful panes and data preview tools.

Important Note on Transform and Add Column

Some commands appear in both the Transform tab and the Add Column tab.

For example, both tabs include commands for working with Text, Number, and Date & Time values.

The operations may look similar in both tabs, but the result is different.

Transform commands change the data in the selected column.

Add Column commands create a new column based on the data in the selected column.

This is one of the most important ideas to understand in the Power Query Editor.

For example, if you use a Text command from the Transform tab, the selected column is changed.

If you use the same command from the Add Column tab, Power Query keeps the original column and creates a new result column.

Queries Pane

The Queries pane is shown on the left side of the Power Query Editor.

It lists all the queries available in the current Excel workbook or Power BI workbook.

Each query usually represents a table, a connection, or a data preparation step.

Not every query needs to be loaded. Some queries are only helper queries or work-in-progress queries. These can be kept as Connection only or not loaded.

The final queries that represent the tables needed for analysis should be loaded.

Loaded queries become tables in Excel and/or Excel Power Pivot Data Model, or tables in the Power BI data model.

When you click a query name, the data preview for that query is shown in the center of the Power Query Editor.

The Queries pane is useful when your project contains more than one query. You can quickly move from one query to another.

Example

Suppose your monthly sales data is stored as separate files in a folder.

First, you may create a query to connect to the folder and get the list of files.

Then you may create a sample query using one sample file. In this sample query, you clean the messy data and structure it properly as a table.

Power Query may also create a function based on this sample query.

These helper queries do not need to be loaded.

The final step is to combine the data from all the files by applying the function to each file.

The combined query is the important final query. This is the query you load for analysis.

This is why the Queries pane is important. It helps you manage both helper queries and final output queries in one place.

Data Preview

The Data Preview area is shown in the center of the Power Query Editor.

It displays a preview of the data for the selected query.

This is where you can see the columns and rows of your data while you are cleaning and transforming it.

The Data Preview area is very useful because you can immediately see the result of each step you apply.

For example, when you remove a column, filter rows, split a column, or change a data type, the preview updates and shows the changed data.

The Data Preview is not the final loaded data. It is only a preview that helps you build and check your query.

Power Query works step by step. Every transformation you apply is recorded, and the preview helps you understand what has changed at each step.

Important Note

The Data Preview may not always show all the rows in your data. For large datasets, Power Query shows only the top 1000 rows so that the editor can be fast and responsive. But all the defined steps will be applied to all the rows, and full data will be loaded.

Query Settings Pane

The Query Settings pane is shown on the right side of the Power Query Editor.

It has two important sections:

Properties
This section shows the name of the selected query. You can rename the query here.

Applied Steps
This section shows all the transformation steps applied to the selected query. Every time you perform an action in Power Query, a new step is added to the Applied Steps list.

For example, if you remove a column, filter rows, change a data type, or split a column, Power Query records that action as a step.

The Applied Steps section is like a recipe. When new data is added to the source and the query is refreshed, Power Query follows the same recipe again.

This means you do not have to repeat the cleanup work manually.

This is what makes Power Query powerful data wrangler.

Important Note

You can click any step in the Applied Steps list to see how the data looked at that stage. Further, the M code of that step is shown in the Formula bar.

This is very useful for checking your work and troubleshooting errors.

You can also delete, rename, reorder, or edit steps when needed.

The steps need not be made executed from top to bottom of the list. Because each step stored what the previous step is.

So, be careful when you delete or change a step. Later steps may depend on it and may show errors if the earlier step is changed.

Loading Data

After you finish cleaning and transforming data in Power Query, the final step is to load the data.

Loading means sending the result of the query to Excel or Power BI.

There is a small difference between Excel and Power BI at this step.

Loading Data in Excel

In Excel Power Query Editor, the command is Home -> Close & Load. This has a dropdown with 2 options:

    • Close & Load
    • Close & Load To

Close & Load loads the query as an Excel Table.

Close & Load To opens the Import Data dialog box, where you can choose how the query should be loaded.

Load To dialog box in Excel

So you can choose to load the query in any of the following ways:

    • As a table in an Excel worksheet
      • Existing worksheet
      • New worksheet
    • As PivotTable Report
    • As PivotChart
    • As a connection only
  • Once you make the selection, you have another option:
    • Whether to add to the Excel Power Pivot Data Model, or not.

Loading Data in Power BI

In Power BI Power Query Editor, the command is Home -> Close & Apply. In Power BI, loaded queries become tables in the Power BI data model. 

If you do not wish to load a query you can just select Disable Load in the Queries pane itself.

Important Note

Not every query needs to be loaded.

Helper queries, sample file queries, and intermediate queries are often not loaded.

Only the final queries needed for analysis should usually be loaded.

This keeps the workbook cleaner and more efficient.

What to Learn Next

Now that you understand the Power Query Editor interface, the next step is to learn how to bring data into Power Query.

In the next lesson, you will learn how to get data from different sources such as Excel tables, CSV files, folders, and other data sources.

This is where the real Power Query journey begins.