Power Query for Excel: Load Settings Minimize Wasted Memory
Here at AKA Green Beacon, we are excited about helping our customers leverage Power BI. We’ve found that there are some tricks of the trade in the real-world use of the tool that will help maximize the capabilities of the solution.
We reviewed the online Getting Started Guide to start working through the examples. If you have also done this, you may have noticed a few things by the time you got through the section for the first tool: Power Query. First, it appears that the UI has changed a bit since Microsoft published the Getting Started Guide, changing the location of some settings – such as Load Settings. Second, without heeding the instructions in the examples to adjust the Load Settings, you may run out of memory before even finishing the examples (especially with the 32-bit version of Excel, and somewhat limited memory such as in my situation).
Short of upgrading to the 64-bit version of Excel or upgrading your device, smart use of Load Settings in Power Query can make a significant difference in the user experience, and the size of the data sets from which you can pull pertinent subsets of data. You can define default Load Settings to be used for every new query, as well as the Load Settings for each individual query.
Default Load Settings
By default, new queries will automatically be loaded to a new worksheet, or directly to the Data Model when loading multiple queries at a time. This can utilize a large amount of memory, especially with queries you are not ready to work with yet, and can also slow down the query editing process. You may want to disable loading to either a worksheet or to the Data Model by default, effectively loading only the connection and preview rows to edit the query.
To change the Default Query Load Settings, choose Options from the Machine Settings section of the Power Query ribbon or in the File Menu of the Query Editor.
After loading a new query with settings like those above, the query will show in the Workbook Queries list with Load is Disabled listed.
You can still edit, merge or append queries as normal while load is disabled. You may notice that for queries with only a connection (not loaded locally), is that there is no Table Tools contextual ribbon tabs available for the Query – but these features can all be accessed by right-clicking on the Query in the Workbook Queries pane.
Choosing and Changing Query Load Settings
Individual queries can be loaded to Worksheets or the Data model by accessing the Load Settings for a specific query. There are many ways to access these settings for a query:
While not available for queries with Loading Disabled, this location is handy for unloading loaded queries, or loading a query to another location.
Other Helpful Hints
- Loaded or even loading queries can have their Load Settings changed to release memory, or stop an in progress load.
- You can see how much you have cached, and adjust the max cache allowed in the Power Query Options (just under Default Query Load Settings). You can also clear your current cache from here!
- Regardless of cache limitations, the 32-bit version of Power Query has a data processing limitation of approximately 1GB. The 64-bit version will only be limited by available virtual memory.