Power BI Analyze in Excel – What You Need to Know

Level: Beginners

Microsoft announced another awesome Power BI feature recently – Analyze in Excel.  This 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.

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.

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 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).

image

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.  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.

image

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.

image

Building a Pivot Table

After you click the “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.

image

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

image

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

image

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).

image

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

analyze in excel

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).

image

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.

L2WD banner ad

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 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).

image

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.

image

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.

image

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
    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.

Share?

Comments

  1. I wonder how the uptake of PowerBI will be in the corporate world for large scale use given that many companies including my current employer may not be on the latest office versions yet.

    Perhaps it needs to be included by default in deployments so users can get their hands on it in the workplace otherwise it will continue to all be done in Excel.

    • I think MS is doing everything possible to stop this being an issue. The stand alone Power BI Desktop version is independent of the Office version and of course so is the cloud service. I think the biggest issue is IT departments with O365 accounts preventing access, but there are ways around that if you create another domain to use.

      • Well, It’s almost stand-alone. They still have that nagging issue with reading in Access database files that depend on the AccessDatabaseEngine(2010) 64-bit or 32-bit.
        This is the case even when I had Office 2016 64-bit Access installed… I still had to go download and install the AccessDatabaseEngine(2010)

        If you have 32-bit Office (Access) installed, it won’t let you (normally) install the 64-bit engine. Same with Excel PowerPivot!
        They’re smart guys over there in Redmond, but why can’t they come up with a workable fix for this? (I’m no O/S developer, but it seems like they could just include a copy of the database engine needed -under another name, if necessary – so that it truly was independent of the installed Office version.

        • Totally agree Chris. I update this blog post to reflect that it should be possible to do what you have described, but I have never done it https://exceleratorbi.com.au/problems-importing-access-excel-2010-sql-server-2012/

          Can you confirm that you have indeed done this? IE installed Power BI desktop 64 on top of a 32 bit Office environment? Did you have to do any tricks like I describe in this linked post to make it work, or did it install out of the box?

          • Yes, I can confirm that I have installed PowerBI Desktop 64-bit over an Office 32-bit environment. I didn’t have any problem doing the install, and in fact, everything was working great until we needed to load data from an Access database.

            I was able to make use of the work-around that your link describes (loading the 64-bit database engine using the /passive flag). I’m comfortable doing that, but it isn’t something that I’d recommend to our typical corporate user (who depends on the 32-bit Office environment), as it caused some of the “normal” applications to stop working… I don’t recall the actual error messages I was getting. The solution was that I had to go into the Task Manager and disable the 64-bit/re-enable the 32-bit software. It was way too much jumping through hoops! (And, since my timecard uses 32-bit Office, it didn’t take me long to realize that this was only good as a temporary fix!)

            The interesting thing was that once I loaded the data base into PowerBI 64-bit, that I could save the .pbix file andall of the graphs, charts, etc. could then be loaded intact into the 32-bit version of PowerBI… thanks to the high data compression. I just couldn’t [Refresh] the data, since it was too much for the 32-bit PowerBI to deal with.

            As far as I can tell, if PowerBI 64-bit could read Access files (in a true stand-alone fashion) when 32-bit Office was installed, that there we be looking at near-Nirvana!! 😉

  2. Even, if you have just one Power BI account, you may come across the error “The HTTP server returned the following error: Forbidden.”

    @Matt: thanks for logging a thread to find a workaround for this bug.

    @Microsoft: Be fast, but first be right!

    • Good point on “right, then fast”. My guess is MS was on a promise of delivering shiny things to announce to the Data Insights Summit, and then had committed for a March release of all these things. Maybe the QA was the but that missed out this time.

  3. Thanks for the article, Matt!
    I did go out and get all of the “Analyze in Excel” features working as you described. A 1.7 GB Access database pared down to a 37.8 MB .pbix file, and when I saved the “linked to PowerBI workbook it’s just 1.1 MB on my hard drive! WOW!

  4. Great post Matt, was excited to start using it, but unfortunately I’m getting this error message<

    “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.”

    Any idea on why that might be?

    Cheers,
    Nick C

  5. PowerBI Desktop 64-bit with Office 32-bit installed on your machine:

    If you have Office 32-bit installed, installing the AccessDatabaseEngine_x64.exe /passive will allow you to connect to Access databases from PowerBI Desktop 64-bit. However, it causes your Office 32-bit applications to attempt a “Repair” every time you launch them!!
    Is anyone aware of a true co-existence solution?

    So far, my only “fix” is to uninstall the Microsoft AccessDatabaseEngine under [Control Panel | Programs] once I’m done with loading the data. (But, I have to go through this whole fiasco again to do a data Refresh in PowerBI Desktop! Arrrgh!!)

    • This is interesting. I wonder why it does this when you have 32 bit office and install 64 bit connectors in passive mode, but why it doesn’t try to “repair” when you have 64 bit Office and the 32 bit connectors in passive mode. Have you allowed the “repair” to complete to see what happens?

    • I’m not clear what you are asking. Analyze in Excel does use Excel to connect to the Power BI Service already. What do you mean you want to “load some data to the data model”? If you want to do that, you can either refresh the data model in the service, or if you need a different design you need to do it in Power BI desktop and then republish. Maybe I have missed something, not sure.

Leave a Reply to Nick C Cancel reply

Your email address will not be published. Required fields are marked *

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x