Power Query: Querying Query Options

Welcome to our Power Query blog. This week, I look at the ‘Query Options’ screen and what it can do for me.

The ‘Query Options’ screen is on the ‘Data’ tab and may be found at the bottom of the dropdown when selecting ‘New Query’.

The screen gives me control over how my queries are created, either globally or in my current workbook, depending which tab I choose.  Taking it from the top, ‘Use standard load settings’ sets the default action for where to load queries to:

• For single queries, the default is to load to a new worksheet
• For multiple queries, the default is to load to the data model.

I can choose to specify the defaults instead, but in any case, I can override this when I am actually loading a query.

‘Fast Data Load’ can also be set for individual queries in the ‘Query Properties’ screen (for more on this, please refer to Power Query: Managing Many Queries.  Microsoft describes the fast data load setting below, which means it depends how quickly I need the information from my query and whether I am happy to let Excel hang in the meantime!

The ‘Data Cache Management Options’ basically show me the current size and limit of the cache and allows me to clear the cache and change the limit.  Caches generally speed up retrieval and this is no exception: copies of query preview results may be held here for faster access.  Whilst the maximum size can be controlled, it’s not recommended to go below 32 MB.

The ‘Query Options’ screen is mostly to do with how the Query Editor appears. The ‘Parameters’ section is interesting, and deserves more explanation. If I enable ‘Always allow parameterization…’, then I can use the functionality associated with parameters from my query without first creating a parameter. As a more experienced user, I switch this on, but someone who was going to stick to simple queries probably wouldn’t. For more on parameters, please see Power Query: Passing Through Parameters.

The next tabs are to do with protecting data. Requiring approval for new database queries is a sensible precaution, and warning users if they select a website that has not been approved as trusted or entered explicitly makes sense. I keep these default settings. I haven’t approved any ADFS authentication services, but if I did, they would appear here. Privacy levels are concerned with protecting my data sources, and require a complete explanation – for this reason I will not try to simplify it. Microsoft explain the levels and how to combine them here so that I don’t have to! I can choose to specify privacy settings globally and for my current workbook. I am not going to show the account tab, as that is part of my privacy settings…

Diagnostics can be used if serious problems are encountered in Excel. I have created a simple query just to show what is created in the trace file. Any open Excel session will write to trace files until the session that has enabled tracing is closed or tracing is disabled. The screen helpfully tells me where the trace files are kept by letting me access the folder. One of my trace files is shown below:

It is essentially a list of steps, but it’s used mainly to locate an error if there is a problem.

Finally, I can set the locale for my current workbook on the ‘Regional Settings’ tab. This influences how dates and times are interpreted as data is extracted and can be useful if I am dealing with data that has come in from the US. This only affects the query, not my Excel workbook.

In the screen above, I have imported from my US file with my locale set to English (United States), and Power Query recognises that I have dates, as shown by the assumed data type. I can now load my data.

As I have my location set as English (United Kingdom) in Excel, the date is now shown in the correct format for me.

On the screen above I have set my locale back to English (United Kingdom) and extracted my data again. This time, the date fields appear as data type text because Power Query has not recognised them as dates.

When I load to my spreadsheet, the dates are not shown correctly. So locale can be useful when I am using sources from other regions.

Want to read more about Power Query? A complete list of all our Power Query blogs can be found here.