As many of you would already know, 32 bit applications do not always play well with their bigger sibling, 64 bit applications.
There are lots of “under the hood” files and applications that are always designed to be either 32 bit or 64 bit – not both. In the case of Microsoft Office, you must choose either 32 bit or 64 bit across the entire Office Suite. You can’t for example have 64 bit Excel and 32 bit Outlook, PowerPoint etc (read more about it here). This is unfortunate because:
- The ONLY Office application that can benefit from 64 bit is Excel running Power Pivot.
- Many (most) IT departments do not install Office 64 bit (for the same reason I mention above – there are often many conflicts with Addins and applications).
…but you want to have 64 bit Excel because you want to be a Power Pivot Ninja.
So what to do? Well the best thing is to try to get your system upgraded to 64 bit Office. If that is not possible, then there is a work around that I explain below.
Note: You must have 64 bit Windows and Admin Rights to proceed. If you don’t have Admin Rights, your IT department may be happy to install 64 bit Power BI Desktop for you as an alternative to Office 64 bit. Also note that this worked for me, but I can’t guarantee it will work for you. Use caution and I recommend you create a system restore point before proceeding.
Power BI Desktop 64 Bit
It is possible to install Power BI Desktop 64 bit on a computer that has 32 bit Office installed. There are a few things you may need to do however to make it work properly (depending on your config), but I explain those things below. The high level process is:
- Install Power BI Desktop 64 bit
- Install the 64 bit data providers you need (such as the Access Database Engine) so you can import the data you need. Power Pivot relies on these data providers to connect to the relevant data sources.
Once you have 64 bit Power BI Desktop installed and running, you can then also optionally use Power BI Desktop as a local 64 bit server and direct connect to it from Excel 32 bit by following the instructions and template provided in my blog article here.
Download and Install Power BI Desktop
After downloading the file, just go ahead and install it in the normal way.
Try to Import Data in Power BI Desktop
After installing, run Power BI Desktop and try to install some data (say from an Access file like this one). When you try to import data using Access, you will most likely get an error message like this.
But don’t panic, just follow the instructions below.
Download the Data Providers you need
If you have 32 bit Office on your computer, then any data providers you have on your PC will also be 32 bit. These will not work with 64 bit Power BI Desktop and you will need to install the 64 bit versions as well as retaining your 32 bit versions.
Standard Install Process
- Download the data provider you need (AccessDatabaseEngine.exe 64 bit version in this case here)
- Try to install the file. If all goes well, you are done and you can start using Power BI Desktop.
However in some PC configurations, installing 64 bit data providers when there are also 32 bit data providers installed can be harder than you would think, because Microsoft can explicitly try to prevent you from doing this. If this happens, you can force the install of the 64 bit data providers by following the steps below:
Install in Passive Mode
Edit Dec 2017: Warning. I strongly suggest you set a restore point before proceeding with this process. While it seems to work fine for most people, I am aware of increasing numbers of individuals who have had a problem with this approach. Some have had Office 2010 and it is also possible that there have been changes made to the way the software installs -I can’t be sure. Suffice to say however that you should create a restore point and also proceed at your own risk. You have been warned.
- Copy the install file to a location on your PC that is easy to access from the command line. I put mine in C: root folder
- Click the windows button and type run and press enter
- type c:AccessDatabaseEngine.exe /passive into the command line box and click “OK”. Of course you should use the correct path to your file.
- The files will install successfully in the background while suppressing warnings and errors.
- It’s a good idea to reboot your computer after the install.
You may need to repeat this process for any other data providers that you need. The easiest thing to do is just start using Power BI Desktop until you find you have a problem, then find and install the new 64 bit data providers you need to solve each problem you come across.
Time to Test
Once you have installed the new data providers, it is time to test it out. Open Power BI Desktop 64 bit and try to connect to data coming from an Access Database. You can download the Adventure Works Access database here if you need a sample to test.
Use Power BI Desktop 64 bit as a Server on your PC
Take a look at my blog post from a couple of weeks ago. If you follow then instructions on the other blog post, you will be able to open Power BI Desktop 64 bit, open a large PBIX workbook, and then use Excel to connect directly to Power BI Desktop as a server. You can also migrate your existing Excel workbooks that are too big for 32 bit Excel to Power BI Desktop (File\Import\Excel Workbook Contents) and then start to use Power BI Desktop as a server (you will have to recreate your visualisations however).