Query overview in Power BI Desktop

With Power BI Desktop you can connect to the world of data, create compelling and foundational reports, and share your efforts with others – who can then build on your work, and expand their business intelligence efforts.

Power BI Desktop Views

Power BI Desktop has three views:

• Report view – You can use queries that you create to build compelling visualizations, arranged as you want them to appear, and with multiple pages, that you can share with others.
• Table view – See the data in your report in data model format, where you can add measures, create new columns, and manage relationships.
• Model view – Get a graphical representation of the relationships that are established in your data model, and manage or modify them as needed.

Access these views by selecting one of the three icons along the left side of Power BI Desktop. In the following image, Report view is selected, indicated by the yellow band beside the icon.

Power BI Desktop also comes with Power Query Editor. Use Power Query Editor to connect to one or many data sources, shape, and transform the data to meet your needs, then load that model into Power BI Desktop.

This article provides an overview of the work with data in the Power Query Editor, but there's more to learn. At the end of this article, you'll find links to detailed guidance about supported data types. You'll also find guidance about connecting to data, shaping data, creating relationships, and how to get started.

But first, let's get acquainted with Power Query Editor.

Power Query Editor

Power BI Desktop offers two Power Query Editor experiences:

• New Power Query experience (Preview): A redesigned editor with streamlined navigation, improved accessibility, and a consistent Power Query experience across Power BI Desktop, web modeling, and other Fabric products.
• Legacy experience: The original Power Query Editor window.

In both experiences, you use Power Query Editor to connect to one or many data sources, shape and transform the data to meet your needs, and then load the result into Power BI Desktop. Select the tab that matches the experience you're using.

For known limitations, workarounds, and a place to share feedback, see New Power Query experience known limitations and workarounds.

New experience (Preview)

Note

The new Power Query experience is in preview.

Prerequisites

Power BI Desktop with the New Power Query experience preview feature enabled.

To enable the new Power Query experience:

1. Launch Power BI Desktop.
2. Go to File > Options and settings > Options.
3. Select Preview features, and then select the New Power Query experience checkbox.
4. Select OK.

The same preview feature enables both the new Get Data experience and the new Power Query Editor. To return to the legacy editor, clear the checkbox. You don't need to restart Power BI Desktop. For more information about the new Get Data experience, see Get data in Power BI Desktop.

Open Power Query Editor

To open Power Query Editor, select Transform data from the Home tab of Power BI Desktop.

With no data connections, Power Query Editor appears as a blank pane, ready for data. After a query is loaded, the editor presents five distinct components. The numbers in the following table correspond to the numbered callouts in the screenshot.

#	Component	Description
1	Ribbon	The ribbon navigation experience, which provides multiple tabs to add transforms, select options for your query, and access different ribbon buttons to complete various tasks.
2	Queries pane	A view of all your available queries.
3	Current view	Your main working view, which by default shows a preview of the data for your query. You can also enable the diagram view along with the data preview. You can switch between the schema view and the data preview while maintaining the diagram view.
4	Query settings	A view of the currently selected query with relevant information, such as query name, query steps, and various indicators.
5	Status bar	A bar showing important information about your query, such as execution time, total columns and rows, and processing status. This bar also contains buttons to change your current view.

Each of these components is described in the following sections. For a full walkthrough of the Power Query user interface, see Use Power Query to transform data.

The ribbon

The ribbon is the component where you find most of the transforms and actions that you can do in Power Query Editor. Commands are grouped on the Home, Transform, Add column, View, Tools, and Help tabs. Each tab provides specific buttons and options that give you easy access to the transforms and actions you might need.

The Home tab contains the common query tasks, including the Get data commands that you use to connect to data and begin the query building process. For more information about the available data sources, see Data sources in Power BI Desktop. For more information about using connectors, see Getting data overview.

The Transform tab provides access to common data transformation tasks, such as adding or removing columns, changing data types, and splitting columns. The Add column tab provides more tasks associated with adding a column, formatting column data, and adding custom columns. The View tab controls which panes and views are displayed, and opens the Advanced Editor.

The Power Query interface is responsive and adjusts to your screen resolution. If you want a compact version of the ribbon, select the collapse button at the bottom-right corner of the ribbon. To switch back to the standard ribbon, select the expand icon in the same corner.

Many of the tasks available from the ribbon are also available by right-clicking a column, or other data, in the current view. Transforms can often be reached from more than one place, so you can use the experience you prefer.

For more information about transforming data, including examples, see Tutorial: Shape and combine data in Power BI Desktop.

The Queries pane

The Queries pane displays all of your available queries. When you select a query, its data is displayed in the current view, where you can shape and transform the data to meet your needs.

Select the icon in the top-right corner of the Queries pane to collapse the pane, and select it again to expand the pane. Similar icons throughout the interface let you collapse or expand other views and sections, so you can focus on the view that matters most to you.

For more information, see Queries pane.

The current view

The current view is your main working view. By default, it shows the data preview for the selected query. You accomplish much of the query work in this area. Select a column header, or right-click it, to see the transformations available for that column. Many of these items are the same as the buttons on the ribbon tabs.

When you select a transformation, the query applies the step to the data. It also saves the step as part of the query itself. The Query settings pane records the steps in sequential order, as described later in this article.

You can also enable the data profiling tools to better understand your data, including value distribution and column quality. Enable and disable these tools from the View tab at your convenience. For more information, see Using the data profiling tools.

Switch between views

Apart from collapsing panes and sections, you can also switch which views are displayed. Go to the View tab in the ribbon, where you find the Preview and Layout groups that control how the interface looks.

The right side of the status bar also contains icons for the diagram, data, and schema views. Use these icons to change between views, or to enable and disable the view of your choice.

Try these options to find the view and layout that you're most comfortable working with.

Schema view

Schema view offers a quick and straightforward way to interact only with the components of the schema for your table, such as the column names and data types. Use schema view when you want to do schema-related actions, such as removing columns, renaming columns, changing column data types, reordering columns, or duplicating columns.

When schema view is active, the Schema tools tab appears in the ribbon with the actions that apply to columns.

For more information, see Using Schema view.

Diagram view

Diagram view helps you visualize how your query is structured and how it might interact with other queries in your project. Each step in your query has a distinct icon to help you recognize the transform that was used, and lines connect the steps to illustrate dependencies.

When both the data preview and diagram view are enabled, the diagram view appears on top of the data preview. With diagram view enabled, select the plus sign to search for a new transform to add to your query.

For more information, see Diagram view.

The Query settings pane

The Query settings pane shows information about the currently selected query, such as the query name, its steps, and various indicators. Every transformation applied to your query is saved as a step in the Applied steps section.

If you need to check how your query is transformed from step to step, select a step to preview how your query resolves at that specific point.

It's important to know that the underlying data isn't changed. Rather, Power Query Editor adjusts and shapes its view of the data.

You can rename steps, delete steps, or reorder the steps as you see fit. To do so, right-click the step in the Applied steps section, and choose from the menu that appears. Select Properties to change the name of a step or add a description to it. All query steps are carried out in the order they appear.

For more information, see Using the Applied Steps list.

The status bar

The status bar appears at the bottom of the editor and shows important information about your query, such as execution time, total columns and rows, and processing status. The right side of the status bar also contains icons that you can use to change the current view.

The formula bar

The formula bar shows the Power Query M formula language expression for the selected step. You can edit the expression directly in the formula bar to adjust a single step, or use the Advanced Editor to work with the full query script.

Get help

The Help tab in Power Query Editor provides links to Power BI information. You can reach the same help options by selecting File > Help.

Legacy experience

Open Power Query Editor and connect to data

To get to Power Query Editor, select Transform data from the Home tab of Power BI Desktop.

With no data connections, Power Query Editor appears as a blank pane, ready for data.

After a query is loaded, Power Query Editor view becomes more interesting. If you connect to a Web data source using the New Source button in the top left, Power Query Editor loads information about the data, which you can then begin to shape.

Here's how Power Query Editor appears after a data connection is established:

• In the ribbon, many buttons are now active to interact with the data in the query.

• In the left pane, queries are listed and available for selection, viewing, and shaping.

• In the center pane, data from the selected query is displayed and available for shaping.

• The Query Settings pane appears, listing the query's properties and applied steps.

  

Each of these four areas are explained later: the ribbon, the Queries pane, the Table view, and the Query Settings pane.

The query ribbon

The ribbon in Power Query Editor consists of the Home, Transform, Add Column, View, Tools, and Help tabs.

The Home tab contains the common query tasks.

To connect to data and begin the query building process, select New Source. A menu appears, providing the most common data sources.

For more information about available data sources, see Data Sources. For information about connecting to data, including examples and steps, see Connect to Data.

The Transform tab provides access to common data transformation tasks, such as:

• Adding or removing columns
• Changing data types
• Splitting columns
• Other data-driven tasks

For more information about transforming data, including examples, see Tutorial: Shape and combine data in Power BI Desktop.

The Add Column tab provides more tasks associated with adding a column, formatting column data, and adding custom columns. The following image shows the Add Column tab.

The View tab on the ribbon is used to toggle whether certain panes or windows are displayed. It's also used to display the Advanced Editor. The following image shows the View tab.

It's useful to know that many of the tasks available from the ribbon are also available by right-clicking a column, or other data, in the center pane.

The left (Queries) pane

The left pane, or Queries pane, displays the number of active queries and the name of the query. When you select a query from the left pane, its data is displayed in the center pane, where you can shape and transform the data to meet your needs. The following image shows the left pane with a query.

The center (Data) pane

In the center pane, or Data pane, data from the selected query is displayed. This pane is where much of the work of the Query view is accomplished.

The following image shows the Web data connection established earlier. The Overall score column is selected, and its header is right-clicked to show the available menu items. Notice that many of these items in the right-click menu are the same as buttons in the ribbon tabs.

When you select a right-click menu item (or a ribbon button), the query applies the step to the data. It also saves step as part of the query itself. The steps are recorded in the Query Settings pane in sequential order, as described in the next section.

The right (Query Settings) pane

The right pane, or Query Settings pane, is where all steps associated with a query are displayed. For example, in the following image, the Applied Steps section of the Query Settings pane reflects the fact that we just changed the type of the Overall score column.

As more shaping steps are applied to the query, they're captured in the Applied Steps section.

It's important to know that the underlying data isn't changed. Rather, Power Query Editor adjusts and shapes its view of the data. It also shapes and adjusts the view of any interaction with the underlying data that occurs based on Power Query Editor's shaped and modified view of that data.

In the Query Settings pane, you can rename steps, delete steps, or reorder the steps as you see fit. To do so, right-click the step in the Applied Steps section, and choose from the menu that appears. All query steps are carried out in the order they appear in the Applied Steps pane.

Advanced Editor

The Advanced Editor lets you see the code that Power Query Editor is creating with each step. It also lets you create your own code in the Power Query M formula language. To launch the advanced editor, select View from the ribbon, then select Advanced Editor. A window appears, showing the code generated for the selected query.

You can directly edit the code in the Advanced Editor window. To close the window, select the Done or Cancel button.

The Advanced Editor is available in both the new and the legacy experience. The preceding screenshot shows the legacy experience. Some Advanced Editor controls and display options differ in the new experience. For more information, see New Power Query experience known limitations and workarounds.

Saving your work

When your query is where you want it, select Close & Apply from Power Query Editor's File menu. This action applies the changes and closes the editor.

As progress is made, Power BI Desktop provides a dialog to display its status.

When you're ready, Power BI Desktop can save your work in the form of a .pbix file.

To save your work, select File > Save (or File > Save As), as shown in the following image.

Related content

There are all sorts of things you can do with Power BI Desktop. For more information on its capabilities, check out the following resources:

• What is Power BI?
• Use Power Query to transform data
• New Power Query experience known limitations and workarounds
• Data sources in Power BI Desktop
• Connect to data sources in Power BI Desktop
• Tutorial: Shape and combine data with Power BI Desktop
• Perform common query tasks in Power BI Desktop