Power BI Analyze in Excel - What You Need to Know ✅ - Excelerator BI

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

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

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

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

Power BI Online Training

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.

Super Charge Power Bi Ad 1

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

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 =

    [email protected];

  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.

33 thoughts on “Power BI Analyze in Excel – What You Need to Know”

  1. Hi Matt,
    I am wondering what level of permission user will need at workspace in order to analyze to excel data. currently. If I give user View role then I believe he/she won’t see dataset listed. also can I share download .odc file to other user within the organization for them to point their existing excel file to use new Power BI based .odc file. I want to move Tabular model from on prem to cloud, and then ask every one to switch their existing excel file which is pointing to on prem and now they will have to point to Power BI service. is there any easy steps available?

  2. Hi Matt,

    I have built a Power BI model which aggregates financial data (debits and credits). The model is deployed to a workspace with Premium capacity and exists as a golden dataset with a thin workbook. Performance Analyzer shows fine performance on the thin workbook.

    My accountant colleagues love Excel, so I gave them the Analyze in Excel link to the golden dataset. Happy days.

    However — I now am observing some very lengthy query times and Premium capacity is getting consumed to capacity regularly. I note that Usage Metrics reports do not seem to capture queries initiated via interactions from Analyze in Excel — they only show usage of the thin workbook. Premium Capacity Metrics show lots of “long running queries” on the golden dataset but I can’t reconcile these back to the Usage Metrics report so I am fairly confident they’re coming from Analyze in Excel.

    Do you have any advice for monitoring usage of Analyze in Excel or any rules of thumb for building models that are likely to have high usage via Analyze in Excel? My gut feeling is these accountants are “de-aggregating” data down to individual row levels which I suspect might be causing some long-running queries and not the best use of the model.

    Thanks for your great blog, it is truly the most valuable resource on Power BI I have found.

    1. I have a clear recollection asking Microsoft if Analyze in Excel was included in the metrics, and the answer was yes. Do you have any way you can validate that Analyze in Excel is being excluded? I would love to use it for a follow up to MS.

      I know that a pivot table uses MDX to generate queries, so this could definitely be an issue.

      Given you have premium, you should be able to use profiler to monitor what is happening https://blog.crossjoin.co.uk/2020/03/02/connecting-sql-server-profiler-to-power-bi-premium/

      You can also download the power bi log files and build your own model for analysis https://powerbi.microsoft.com/en-us/blog/the-power-bi-activity-log-makes-it-easy-to-download-activity-data-for-custom-usage-reporting/

      I have heard from others that these log files definitely include the Analyze in Excel events.

      1. Hi Matt,

        Thank you so much for your reply.

        Our IT team unfortunately doesn’t permit us to access Power BI log files, however the SQL Server Profiler did the trick perfectly.

        I can confirm having monitored usage via Profiler yesterday, that Analyze in Excel usage is not included in the standard report usage metrics report, which I guess in retrospect I can rationalise as the usage metrics are “report” usage metrics strictly for the thin workbook and Analyze in Excel is an alternative client which hits the dataset directly. Our Report Usage Metrics shows zero usage but Profiler showed we had hundreds of queries generated through Analyze in Excel.

        Thanks again for your response, really appreciate it.

  3. Hi Matt,

    I have many fields in my sql dataset that are calculated columns. I connect to this through Power bi and they show up with the summation symbol.
    When I analyse in excel and try to add them to the values component of the pivot table they are not accepted.
    I researched and found out that they I need to convert them to measures in power BI.
    But there are many columns 40+.
    Is there are solution to this?
    Also can’t group date into months, year. Date is a general format.
    Regards,
    Matt

    1. Yes, it only works with explicit measures (I’m surprised I didn’t state that in the blog, sorry). Yes, you can easily create the 40 measures with Tabular Editor (just a few clicks. https://exceleratorbi.com.au/introduction-to-tabular-editor-for-business-users/

      To group time periods, you need a calendar table. https://exceleratorbi.com.au/power-pivot-calendar-tables/

      In the menu above, take a look at the knowledge base. There is a lot of foundation information for when you are learning.

  4. Hi Matt – Craig Smith here – Do you know if Microsoft have any plans to fix the issue with Date formats when you ‘Analyse in Excel’? Currently no matter what date format we try the date is shown as a Text field in excel. It’s frustrating when you build visuals in the power BI service that rely on date slicers.

    I’ve built a dynamic aged Accounts Payable report that works nicely in the Power BI service but my users want to replicate the report in excel.

    Cheers,

    Craig

    1. Craig, do you by any chance know if this issue was resolved? I am running into the same challenges.

  5. Hi Matt,
    Do you know whether refresh all in Excel will in fact refresh the data model in the Power BI Service or merely refresh the view from Excel of the data model?
    Thanks

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

      1. Hi Matt, I think I’m looking for the same thing. from PowerBI it forces me to a pivot table… I want to translate the data in power query… how do I do that?!

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

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

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

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

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

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

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

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

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

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

          1. 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!! 😉

Leave a Comment

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

Scroll to Top