The Query Editor in Power BI is designed to help the user apply most of the common transformations by interacting with a set of ribbons and menus.
In this tutorial, you’ll learn how the query editor is organized. Knowing your way around the user interface will help you become more efficient.
The Query Editor In Power BI: Getting Started
First, let’s check the Power Query Editor options inside the Power BI desktop.
Go to File, select Options and settings, and then select Options. Under the Global tab, you’ll find the Power Query Editor settings.
It’s recommended to enable the display of the Query Settings pane and the Formula Bar. You also need to allow for parameters and have M IntelliSense enabled.
Power BI desktop offers two ways to open the Query Editor. On the home tab, you’ll find a section called Queries with a button called Transform data.
This button is divided into two sections. The top half will bring you straight to the Query Editor while the bottom half opens a menu with four items. The first item in this menu will also take you to the Query Editor.
The Editor always opens in a separate window. It has a ribbon that’s organized in several tabs.
The Query Editor Menu In Power BI
The Home tab contains a collection of the most used query, table, and column level actions.
The Transform tab contains a collection of table and column level actions. Once selected, each of these buttons will transform the contents of the table or column.
For example, if you click Date and select Year, you’ll see that the Date column in the table will only contain the year instead of the complete date.
The Add Column tab contains a collection of column level actions. Some of these actions can already be seen in the Transform tab.
The difference is that instead of transforming the contents of an existing column, a new column is added which contains the transformation.
If you click the Date button under the Add Column tab and select Year, you’ll see that a new column has been added showing the year, while the original date column is preserved.
The View tab contains the option to enable or disable the visibility of the formula bar and the query settings pane.
The Tools tab contains all the query diagnostic options. Query diagnostics offer insight into what the Power Query engine is doing.
Optional Tabs
The Tool tab can also become available depending on the return type of the query you’ve selected. For example, if you select a query, an optional Text Tools ribbon appears.
Optional List Tools and Record Tools ribbons also appear depending on the type of query you select.
There are many ways to minimize and expand the ribbon. In the top-right corner of the Query Editor, you’ll find a toggle to minimize or expand the ribbon. You can also press the keyboard shortcut Ctrl+F1.
You can also right-click on the light gray area of the Menu bar to minimize the ribbon.
If you prefer to use keyboard shortcuts to navigate the quick access toolbar or the ribbon, you can use the Alt key to reveal a number or letter combination.
After pressing Alt, press the number or letter combination to navigate between the options in the quick access toolbar. To exit, press the Esc key until all the numbers or letters disappear.
The Query Editor Formula Bar
Directly under the ribbon, you’ll find the formula bar. It contains the M code generated by the user interface.
The formula bar exposes you to the flow and syntax of the M code. This not only helps you better understand the M language, but it also allows you to quickly access and make changes to the code.
You can also add a manual step by pressing the fx before the formula bar.
The Query Editor Panes In Power BI
Query Pane
The query pane is placed on the left-hand side of the Query Editor. This lists all the queries in the current file.
The icon in front of the query name represents the query type.
Right-clicking on the blank space shows a context menu for creating new items, such as a new query, a new parameter, or a new group.
To manage any of the existing queries, right-click on its name.
The copy option not only copies selected queries, but also other reference queries if relevant. Enable load lets you load the query to the data model when Close and apply is selected.
The Include in report refresh option refreshes the query when a refresh action is triggered. Duplicate copies the full M script of a selected query into a new query.
Reference returns the output of the selected query and adds it as a new query to the query list. Using the Move To Group option, you can organize your queries and place them in a group.
Query Settings
On the right-hand side of the Query Editor, you can find the Query Settings pane which contains the Properties and Applied Steps.
This pane allows you to quickly rename your query and get access to the property dialog box. Applied Steps contains a list of all the transformation steps applied to your query.
The gear icons, when clicked, give access to associated dialog boxes.
The exclamation marks, on the other hand, view comments. You can view a comment by hovering over the icon.
Also, when you click on a step, a context menu will appear.
Edit Settings is only available for steps with a gear icon. You can also rename and delete a step but note that there isn’t an undo option.
If you make a mistake, repeating the same transformation through the UI is the only way to bring that step back. The Delete Until End option removes the currently selected steps.
Insert Step After creates a manual step. It returns the previous step name as a result of the newly added step. Extract Previous splits the query into the previous step and references the output in the source step of the original query.
View Native Query enables query folding to the source. The Property dialog box lets you rename and add a comment to the current step.
Preview Pane
The center of the Power Query Editor is the Preview pane. It has an additional context menu which is only available for queries that return a table type indicated by the mini table icon on the top left corner.
When you click on this table icon, it will give you access to most of the table and add column options.
Moreover, when you right-click on a column header, a context menu appears.
It contains most of the column and transformation options from the ribbon. The contents of this menu can change depending on the columns you select.
Also, similar to other Microsoft products, pressing the Shift button allows you to select a continuous range of columns whereas the Control button only highlights the specific columns you selected.
Right-clicking on the green line directly below the column header reveals a context menu to resolve issues with column quality. A secondary preview becomes available when you click off to the side in the white space of a cell that contains a structured data type.
At the end of the table, you’ll find a List object. Once you click this, an additional preview section at the bottom of the screen appears which allows you to view a sample of the contents within that list.
At the bottom part of the Query Editor, you can see the status bar.
It is considered as best practice to select the top 1000 rows instead of the entire data set to improve the performance of your report.
The Query Editor Quick Access Toolbar
Found at the top of the Query Editor, beside the Save button, is the quick access toolbar.
You can customize this toolbar to your preference. Customizing your quick access toolbar allows you to have an immediate access to options you often use so you don’t have to navigate the ribbon to access items.
Another advantage of placing items in the quick access toolbar is that they get assigned a keyboard shortcut.
***** Related Links *****
Power Query Editor Tutorial: Extract Record Field Values From Lists
Variables And Expressions Inside Power Query Editor
Power Query Editor: Staging Queries
Conclusion
There’s a great advantage in being knowledgeable about the basics when creating queries. Having a good understanding of the Query Editor’s user interface makes it easier to navigate between panes and ribbons.
Similar to arranging your desk to improve overall work experience, the same goes for any software’s user interface.
Melissa
