Chapter 41: Enhancing Power Query Productivity
4141
Views not only provide you with cleaner, more user-friendly data, but they can help stream-
line your data model by limiting the amount of data you import.
Letting your back-end database servers do some crunching
Most Excel analysts who are new to Power Query have a tendency to pull raw data directly
from the tables on their external database servers. Once the raw data is in Power Query,
they then build transformation and aggregation steps as needed.
Why make Power Query perform transformations that the back-end server could have
handled? The reality is that back-end database systems like SQL Server have the ability to
shape, aggregate, clean, and transform data much more efficiently than Power Query. Why
not utilize their powerful capabilities to massage and shape your data before importing
into Power Query?
Instead of pulling raw table data, consider leveraging server-side functions and stored
procedures to perform as much data transformation and aggregation work for you as pos-
sible. This reduces the amount of processing Power Query will have to do and will naturally
improve performance.
Upgrading to 64-bit Excel
If you continue to run into performance issues, there is always the option of getting a bet-
ter PC. Better, in this case, means moving to a 64-bit PC with 64-bit Excel installed.
The 64-bit version of Excel can access more of your PC’s RAM, ensuring that it has the sys-
tem resources needed to crunch through bigger data sets. In fact, Microsoft recommends
64-bit Excel for anyone working with data models made up of millions of rows.
Before you start installing 64-bit Excel, however, you’ll need to consider a few things:
■ (^) Do you already have 64-bit Excel installed? To check, click File ➪ Account ➪
About Excel. A dialog box will activate showing either 32-bit or 64-bit at the top of
the screen.
■ Are your data models large enough? Unless you’re working with large data mod-
els, the move to 64-bit may not produce a noticeable difference in your work.
What’s large? A general rule of thumb is that if you have workbooks that use the
internal data model and have a file size upwards of 50 megabytes, you would defi-
nitely benefit from an upgrade.
■ (^) Do you have a 64-bit operating system installed on your PC? 64-bit Excel will
not install on a 32-bit operating system. You can find out if you’re running a 64-bit
operating system by entering “My PC 64-bit or 32-bit” into your favorite search
engine. You’ll get loads of sites that will walk you through the steps to determine
your version.
■ Will your other add-ins die? If you’re using other add-ins, be aware that some
of them may not be compatible with 64-bit Excel. You don’t want to install 64-bit
Excel just to find out that your trusted add-ins don’t work anymore. Contact your