10 Tips for Working with Power Query

All the Power Query queries that live in a particular workbook can be views in the Workbook Queries pane. Choose Data→Show Queries to activate the Workbook Queries pane.

In this pane, you can see some quick information about a query by simply hovering the cursor over it. You can see the data source for the query, the last time the query was refreshed, and a sneak peek of the data within the query. You can even click on column hyperlinks to peek at a particular column.

As you add queries to your workbook, your Workbook Queries pane may start to feel cluttered and disorganized. Do yourself a favor and organize your queries into groups.

The figure illustrates the kinds of groups you can create. You can create a group only for custom functions or a group for queries sourced from external databases. You could even create a group where you store small reference tables. Each group is collapsible, so you can neatly pack away queries that you aren’t working with.

You can create a group by right-clicking a query in the Workbook Queries pane and selecting Move To Group→New Group.

When dealing with a large table with dozens of columns in the Query Editor, it can be a pain to find and select the right columns to work with. You can avoid all that scrolling back and forth by choosing the Choose Columns command on the Home tab.

The dialog box shown opens, showing you all available columns (including custom columns you may have added). You can easily find and select the columns you need.

Every time you apply an action in the Query Editor, a new entry is made in the Query Settings pane, as shown. Query steps serve as a kind of audit trail for all the actions you’ve taken on the data.

You can rename your steps by right-clicking each step and selecting Rename.

A handful of columns in a dataset always make for fantastic reference tables. For instance, if your dataset contains a column with a list of product categories, it would be useful to create a reference table of all the unique values in that column.

Reference tables are often used to map data, feed menu selectors, serve as lookup values, and much more.

While in the Query Editor, you can right-click the column from which you want to create a reference table and then select Add as New Query, as shown.

A new query is created, using the table you just pulled from as the source. The Query Editor jumps into action, showing only the column you selected. From here, you can use the Query Editor to clean up duplicates or remove blanks, for example.

It’s always smart to reuse work wherever you can. Why reinvent the wheel when your Workbook Queries pane is full of wheels you’ve already created?

Save time by duplicating the queries in your workbook. To do so, activate the Workbook Queries pane, right-click on the query you want to copy, and then select Duplicate. As you can see, you can also duplicate custom functions.

If you’re working heavily with Power Pivot and with Power Query, chances are good that you load your Power Query queries to the Internal Data Model a majority of the time.

If you’re one of those analysts who always loads to the Data Model, you can tweak the Power Query options to automatically load to the Data Model.

Choose Data→New Query→Query Options to open the dialog box shown. Select Data Load in the Global section, and then choose to specify a custom default load setting. This enables the options to load to the worksheet or Data Model by default.

One of the more recent additions to Power Query is the ability to automatically detect data types and to proactively change data types. This type detection is most often applied when new data is introduced to the query. Although Power Query does a decent job at guessing what data types should be used, applied data type changes can sometimes cause unexpected issues.

If you’d rather handle data type changes without help from Power Query’s type detection feature, you can turn it off.

Choose Data→New Query→Query Options to open the dialog box shown. Select Data Load in the Current Workbook section, and then deselect the option to automatically detect column types and headers for unstructured sources.

The privacy-level settings in Power Pivot are designed to protect organizational data as it gets combined with other sources. When you create a query that uses an external data source with an internal data source, Power Query stops the show to ask how you want to categorize the data privacy levels of each data source.

For a majority of analysts, who deal solely with organizational data, the privacy-level settings do little more than slow down queries and cause confusion.

Fortunately, you have the option to ignore privacy levels.

Choose Data→New Query→Query Options to open the dialog box shown. Select Privacy in the Current Workbook section, and then choose the option to ignore privacy levels.

When you’re building a query and choosing Load to Data Model as the output, Power Query, by default, attempts to detect relationships between queries and creates those relationships within the Internal Data Model. The relationships between queries are primarily driven by the defined query steps. For instance, if you were to merge two queries and then load the result into the Data Model, a relationship would be automatically created.

In larger data models with a dozen or so tables, Power Query’s relationship detection can affect performance and increase the time it takes to load the Data Model.

You can avoid this hassle and even gain a performance boost by disabling relationship detection.

Choose Data→New Query→Query Options to open the dialog box shown. Select Data Load in the Current Workbook section, and then deselect the option to create relationships when adding loading to the Data Model.