Four Must-Know Options if You’re Using Power Query

Over the last year, Microsoft has added a few options to Power Query that you should definitely embrace (if you’re working extensively with Power Query). These options alleviate some of the annoying quirks in Power Query. I’m especially grateful for the option to turn off that damned Type Detection feature.

In any case, here is my list of must-know options if you’re using Power Query.

Setting a Default Load Behavior

If you’re working heavily with Power Pivot as well as Power Query,

chances are you load your Power Query queries to the Internal Data Model a majority of the time. If you find that you always load your queries to the Data Model, you can tweak the Power Query options to automatically do that. Open Power Query options and select, Data Load under Global, and then choose to specify a custom default load setting. This enables the options to Load to the worksheet by default or Load to the Data Model by default.

 

Preventing Automatic Data Type Changes

One of the more recent additions to Power Query is the ability to automatically detect data types, and proactively change data types. This “Type Detection” is most often applied when new data is introduced to the query.

For instance, the screenshot below shows the Query steps after importing a text file. Note the Changed Type step. This step was automatically performed by Power Query as part of its type detection feature.

 

Although Power Query does a decent job at guessing what data types should be used, data type changes that can cause unexpected issues – especially when you’re working with lots of numbers that are really identifiers or textual in nature. I frankly find the type detection feature annoying. If data types need to be changed, I’m more than capable of figuring that out myself. I personally would rather handle data type changes without the help from Power Query’s type detection feature. So…I happily turn this feature off. In the Power Query options, look for Data Load under Current Workbook, and then uncheck the option to automatically detect column types and headers for unstructured sources.

 

Disabling Privacy Settings to Improve Performance

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’d like to categorize the data privacy levels of each data source. Most of us deal solely with organizational data, so the privacy level settings do little more than slow down queries and cause confusion.

Fortunately, there is an option to ignore privacy levels. Select Privacy under Current Workbook, and then choose the option to ignore privacy levels.

 

Disabling Relationship Detection

When building a query and choosing Load to Data Model as the output, Power Query will, by default, attempt to detect relationships between queries and create 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 then load the result into the Data Model, a relationship is 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.

Select Data Load under Current Workbook, and then uncheck the option to create relationships when adding loading to the Data Model.

5 thoughts on “Four Must-Know Options if You’re Using Power Query

  1. sam

    “Fortunately, there is an option to ignore privacy levels. Select Privacy under Current Workbook, and then choose the option to ignore privacy levels.”

    Unfortunately – PQ does not “Remember” the setting for the workbook.
    So if you set to ignore privacy and send the file to some one else within the organisation – PQ will again default back to “Combine data according to ……”

    This is a real pain and a show stopper for deploying applications built on PQ

    The only solution so far – is to use Excel 2016 – and then write VBA code that says

    ThisWorkbook.Queries.FastCombine = True

  2. datapig Post author

    sam: wow…I did not know this. That is truly unfortunate. So if these settings don’t travel with the workbook, then I guess these settings are saved in the registry somewhere?

  3. sam

    Another pain point about ignore privacy is if the workbook is protected – this setting cant be changed

Leave a Reply

Your email address will not be published. Required fields are marked *