Power BI Analyze in Excel – What You Need to Know

Microsoft announced another awesome Power BI feature recently.  This Power BI Analyze in Excel feature was requested by Avi Singh from PowerPivotPro.com and was heavily supported as a requirement by the community. You can read more about how you can influence future releases at the bottom of this post.  But let’s get straight into this great new feature.

Power BI Analyze in Excel is available for the free service offering as well as the paid service. Note however if you want to upload a workbook and share it with someone else in your organisation so they too can Analyze in Excel, you will need the paid service.  But don’t despair- Power BI Pro is cheap and well worth the money.

Power BI Service

The Power BI Service is a modern version of SQL Server Analysis Services fully hosted in the Cloud by Microsoft.  What that means to you is you can have a powerful server to host your Power BI workbooks and distribute your reports without having to spend hundreds of thousands of dollars getting it all set up.  You can get started today for free with just a few clicks. https://powerbi.microsoft.com

Once you have your Power BI workbook loaded in the Cloud, you can even share a fully interactive version of the workbook publicly using the Publish to Web feature (demo’d below).  Note you should only do this with non-confidential data as this approach will make your data available publicly over the Internet.

You can view the Power BI report published on web here.

What is Analyze in Excel?

Once you have your data loaded up in Power BI, the world somehow just seems better.  Sometimes however you just want to get into the data and analyse it using good old fashioned Excel Pivot Tables.  Now can do that with the new Power BI Analyze in Excel feature.  To do this, you need to do the following:

• Log in to the Power BI Service with your account
• Navigate to the report in question in the left hand panel of the Power BI Service.
• Click on the ellipsis next to the report name (shown as 1 below)
• Click Analyze in Excel (2 below).

Updated Data Connector

The first time you do this, you will be prompted to download some updated software that is required for this feature to work.  You can prompt this manually from the drop down menu in the top right hand corner of PowerBI.com as shown below.

Make sure you select the correct version for your version of Excel.

• 32 bit version of Excel = Install x86 shown as 1 below
• 64 bit version of Excel shown as 2 below.

Once you have installed this, you can click the “Don’t show this again” box (shown as 3 above) to prevent this dialogue appearing each time you go through this process.

One thing to be aware of. After you click “Don’t show this again”, you can get to the download box again by clicking the downloads button (shown as 1 below) and then selecting “Analyze in Excel updates” (2 below).  However when you do this, it currently ONLY DOWNLOADS the 32 bit version regardless if you have 64 bit or not.  I have logged a bug with Microsoft and will remove this comment from this blog once the problem is fixed.

Building a Pivot Table

After you click the Power BI “Analyze in Excel” button, a small ODC file will be downloaded to your PC.  Keep an eye out for the download in your browser – mine is shown below.

When I click on the above ODC file, I get warning message as shown below.  Just click Enable.

After you click “Enable”, you will get a new blank Pivot Table connected to the Power BI Service as shown below.

Now you have a new blank workbook connected to your Power BI data model in the cloud.  You can build out a Pivot Table to analyse your data like any other Pivot Table, with a few minor differences.

If you take a close look at the Pivot Table Fields list on the right, you will see that there are now 2 types of field groups.  The first type (shown as 1 below) are the measures stored in your tables in Power BI.  These “measure tables” are indicated by the Sigma symbol.  The second type (shown as 2 below) are the actual tables (and columns) from the data model.  You select the Values for your Pivot Table from the measures tables (1) and the Rows/Columns/Filters/Slicers from the actual tables (2).

Now you just build out your Pivot Table as normal as shown below.

Thin Workbooks

When you save this file, you will notice something really special.  This is a “Thin Workbook”, in my example it is just 24kb (shown below).

The data does not live in the workbook – it is only in the cloud.  The only data that is in the workbook is the data that is visualised inside the Pivot Table(s).  But there is a cost to this approach – you must have a live connection to the Internet to be able to interact with the data in the Pivot Table.  Personally I think this is a small price to pay for all of the benefits, however if you want to interact with your workbook in a location that doesn’t have an Internet connection, then this is something you will need to be aware of.

Also note that now the data is in the cloud there will be a small amount of latency (delay) when clicking on the Pivot Table compared to a local workbook on your PC.  This will be most noticeable for small workbooks that are almost instantaneous on your PC.  These small fast workbooks will move from being “instantaneous” on your PC to being “sub second” in the cloud – which is not that bad in my view.  If you have very large workbooks on your PC (say 300MB +), or if you have 32 bit Excel on your PC, then you may actually notice an overall improvement with these thin workbooks.  This is because the processing of the cube is now pushed to the Power BI Service which has lots of powerful processors to complete the task.

Distributing Thin Workbooks

Now that you have this “Thin” Excel workbook, you can distribute the Thin Workbook to other users and they can also interact with the data live directly in the Excel workbook connected to the cloud.  You don’t need to distribute the ODC file, just the updated and saved Thin Workbook.  There are some pre-requisites for this to work, including:

1. You will need to send them the Thin Workbook once the connection has been established (of course).
2. They will need their own Power BI Service Account.
3. They will need access to the shared data via the Power BI Service, so you will need to share the Power BI report with them too from within Power BI.
4. They will need to install the updated “Analyze with Excel update” described earlier.

I have tested distributing this Thin Workbook to a Mac user with Excel 2011 and unfortunately it didn’t work. I haven’t tested it with Excel 2016 for Mac but would like to hear from anyone who does.

Better Than Export to Excel

Rob Collie has a favourite joke.  Qn. What is the 3rd most common button in all BI tools?  Ans. Export to Excel (3rd after 1. OK and 2. Cancel).  But Power BI Analyze with Excel is so much more than “Export to Excel”.  The reason of course is that you don’t have to take a copy of your data to work with it.  You create a live link to the data source and hence when the data is updated in the future, your “Analyze with Excel” workbook will also update.

Cube Formulas Work Too

If you like using Cube Formulas, the good news is they will work too.  When you type a cube formula (like shown below), you will be given a choice of data connections (1 being the Power BI service and 2 being the data model in the current workbook).

So that got me wondering… Can I have 2 data models for a workbook, 1 in the Cloud and a second in the workbook?  The answer is yes.  I have 2 cube formulas below, the first is coming from the data model in the current workbook and the second is coming from the Power BI service connected to the same workbook.

Common Errors

New Versions of Connector

The connector is being updated all the time.  If you see this error (or any other connection error for that matter)

The connection failed because user credentials are needed and Sign-In UI is not allowed

The first thing you should do is make sure you have the latest version of the connector installed.  You can find the latest update online at the Power BI Service as shown below.

Forbidden Error

If you have multiple Power BI accounts, you may come across the following error “The HTTP server returned the following error: Forbidden.”  In fact some users that don’t have multiple accounts have also experienced this error.

The issue is caused by “swapping” between accounts and potentially the login process trying to log into the wrong account.  There is a manual work around to fix this problem.

1. Navigate to the ODC file you downloaded.
2. Right click the ODC file and edit it in Notepad.
3. Find the section that starts with <odc:ConnectionString>
4. Add the following text immediately after this string
   Plain text

   Copy to clipboard

   Open code in new window

   EnlighterJS 3 Syntax Highlighter

   User ID =

   User ID =

   User ID =

   name@youremailaddress.com;

5. Save the file, and then double click to open it again.

You should now be taken to the correct login screen to give you access to the data. I had this very issue myself when I first tried to use this feature.  I logged a thread at community.powerbi.com and got immediate help from the Microsoft Power BI support team to help me resolve the issue.  If you are not a member of the Power BI community, you are missing out – sign up and get involved.

Hopefully a more elegant solution will be delivered in the future – I’m sure it will.

Initialization of the data source failed

You may  see this error, particularly the first time you try to do this.

“Initialization of the data source failed. Check the database server or contact your database administrator. Make sure the external database is available, and then try the operation again. If you see this message again, create a new data source to connect to the database.”

This is normally related to you not having the correct data connector installed for your version of Excel.  Make sure you have installed the correct (64 bit or 32 bit) data connector as covered earlier in this post.

How to Influence Future Power BI Updates

Power BI is rapidly improving each week as the crack Microsoft Power BI team focuses on delivering the new features that its customers most want.  If you want to influence what features get built out next, then do what Avi did and create an idea at http://ideas.powerbi.com/ .  Or you can simply look at other people’s ideas and vote for the ones that you want most.

If you want to help me with one of my passions, you can vote for this idea here.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/12464280-individual-custom-links-for-pinned-live-pages

What I think is really needed is a way to build a proper “web page like” menu structure so you can build out Power BI with a “web browsing experience”.  The current approach of pinning tiles is OK in limited scenarios, but is not really “complete” enough to deliver a comprehensive reporting tool.  I think we need a proper menu/navigation editing solution like in SharePoint where the curator can build out a proper menu structure to deliver the reports to users in a custom and meaningful way.