skip to Main Content

UPDATE 3/29: Recorded a brief demo video of this feature, embedded below. Analyze in Excel is reported to be working for some free accounts but not all (does not work for me). Does work for Paid/Pro accounts. If you use multiple accounts you may need this workaround. Read Aviv Ezrachi’s comment.

Let me start with a proclamation of victory. “Analyze in Excel” is the best news that I heard at the MS Data Summit. Why is it such a big deal? Let me come to that, after I wrap up my proclamation.

It was July 22, 2015 when I submitted the idea for Excel to be able to connect to Power BI data sets (data models). Many of you rallied around that, voting for the idea, keeping it in the top 10 for long periods. Power BI team listened, and James Phillips delivered the announcement in his keynote that this functionality, “Analyze in Excel” is coming to Power BI within the next 30 days.

It is a victory for the community, but it is also a victory for the Power BI team. It is a victory, that we have a product team within Microsoft which really listens to customers. It is a victory that they have fostered a community which works alongside the Microsoft team to improve the product and is fueling the spread and adoption of Power BI.

Why is “Analyze in Excel” for Power BI a big deal?

Stay with me for this one.

Excel => Power BI => ?

As a Modern Excel enthusiast (Modern Excel = Excel + Power Pivot + Power Query = Magic!), I found myself hesitant in embracing the new world of Power BI. Many of those inhibitions have shed away as Microsoft has continued to innovate and deliver an outstanding experience with Power BI. But I could not get over the feeling that going from Excel to Power BI felt like a one-way street.


Excel to Power BI felt like a one way street

You could upload/import an Excel Power Pivot model into Power BI desktop or onto PowerBI.com. But then you could not get it back. You could not get it back in Excel. In the first blog post on the site (yes the very first) Rob called us gridheads, and that we are.

We are Excel gridheads, and no matter how fancy a visualization suite of tools you deliver to us, we often cannot think unless we see data laid out in an Excel grid.

Excel users like using Excel, and now Power BI allows you to continue to do that.

Excel => Power BI => Excel

If you author a data model in Excel, you could publish it to PowerBI.com. But now (ok, soon) you can also then go to PowerBI.com click “Analyze in Excel”, and get an Excel Pivot table connected to the Power BI.com hosted data model.


From Power BI, click “Analyze in Excel” and get an Excel Pivot Table connected to your Power BI Dataset

This completes the round trip, bringing us full circle.


“Analyze in Excel” on Power BI brings us back to Excel

Wait a second? If I started off in Excel why the heck do I need to push it to Power BI to only then connect to it again using Excel.

“Avi you betrayed us! You swine! You let us waste our precious Power BI idea votes on this ridiculous feature.”

Before you start flaming me, hear me out. This was never about one person. This was never about the “author”, who creates the Excel / Power Pivot model. This is about the “consumers”. Yes, plural – consumers. 10s/100s/1000s of them. Power BI helps bring the data to everyone.

Excel => Power BI => Data Democracy!

With Analyze in Excel, once you publish your data model to Power BI, the users can consume it using a tool of their choice. And it’s not a single choice answer.Sometimes you may want a quick glance at the Power BI dashboard, other times you may want to work with the same data set in Excel.


Hundreds of users can consume the Power BI published data model,
using Power BI reports and dashboards or via Excel

Thanks to the Power BI Team, especially Aviv Ezrachi, Dany Hoter, Arina Hantsis and other Israel Development Center team members – for sharing this vision with the Power BI community and delivering (soon) this feature in Power BI. You can learn all about Analyze in Excel feature in the video below. You can also find all the videos from the MS Data Summit at their MS PowerBI YouTube channel.

….

MS Data Summit Sentiment Analysis

When I first heard about the Power BI desktop tool (then called Power BI designer) I felt very conflicted. Okay that’s an understatement. I felt, “Here we go, Microsoft team has done it again”. They have no idea that they already ruled the world of BI (via Excel). And here they go again building a new product, undermining an existing (very successful) one. I brought this up in discussion forums and was assured by the Power BI team, that they would not leave Excel behind. That Excel was part of the future vision. But talk is cheap, furthermore Microsoft hasn’t set very good precedents when it comes to matters such as this.

But it hit me at the MS Data Summit, and hit me hard. As wave after wave of messages washed over me. Excel and Power BI used in a sentence together, over and over again. Session after session talking about Excel and Power BI together, how they fit, how the complement each other. Excel and Power BI showed side-by-side on countless slides. Here’s my favorite one…


This Power BI + Excel slide featured prominently in many sessions,
including James Phillips’ Keynote (Click to watch)

I talk to my kids about trust and how it is hard to gain but easy to lose. My daughter once lied to us about having brushed her teeth (hence losing our trust), so now she needs a “witness” when she brushes her teeth (till she slowly regains our trust, over time). Microsoft has had a checkered past in this regard (to put it mildly). The change around how Power Pivot was bundled in Excel 2013, felt like a gut punch, and it still smarts. Hundreds of angry/confused comments on that post are testament to that.

Trust is hard to gain, but easy to lose

But the Microsoft Power BI team, under the leadership of James Phillips has toiled tirelessly, not just to build an amazing product but also to build a community, and slowly rebuild that trust.

Each of you would have to decide for yourself. But as for me, they have won my trust… and my loyalty.

Power On!
Avi Singh

Avi Singh

Avi Singh has personally experienced the transformation and empowerment that Power BI can bring - going from an Excel user to building large scale Power BI solutions. His mission now is to share the knowledge about Power Pivot and Power BI.

This Post Has 58 Comments
  1. Will the recordings from the presentations at the summit be available? The Vulcans, dark matter, and crystal balls sessions was amazing!

    1. Hmmm…that one does not seem to be available. Rob Collie’s other talk is available and covered some common elements.

  2. Any details about what prerequisites are?
    I tried “Analyze in Excel” with Excel 2010 and opening the mydataset.odc file in Excel result was “HTTP server returned the following error: Forbidden”. Is this a lambada expression? Is Excel 2016 required?

    1. It works with Excel 2010 and Excel 2013 also. Just download right version of driver -> when you select “Analyze in Excel” you get “Install Excel updates” with 2 download links: “Install x86 (preview)” (with yellow background) and “install x64 (preview)” (with white background). If you use 64 bit Excel and download x86 driver you don’t get any warning during installation, but Analyze in Excel gives error. After installing x64 driver everything works OK.

    2. Dory, as far as I know, this functionality has NOT been rolled out in production yet.
      Yes, there are requirements but I am told it works from Excel 2007(?)-2016. Excel 2010 would DEFINITELY work. The main requirement seemed to be new drivers that you need to download (The text says SQL Server 2016 RC1 Driver). But when you click ‘Analyze in Excel’ PowerBI.com interface pops up a reminder with a link to download that driver. The linked demo shows all the steps you would go through.

    3. I’ve got the latest version of Excel and I’m getting the same exact error! Tried googling and can’t find anything. I really like the concept, it sounds a lot like the “thin client” talked about here before. If it works I can see using this a lot.

      1. Guys I really think, it’s not “Live” yet. Power BI has lot of moving parts. Somehow the bits to expose this functionality seems to have been pushed to Production but perhaps not the backend bits. I’m not part of the MSFT team, so just speculating. I would strongly suggest have patience and wait till you see an official announcement on the Power BI blog about this feature being enabled.

    4. “Forbidden” error in Excel indicate the user that try to open the ODC file and connect to Power BI Dataset doesn’t have permissions on the relevant Power BI Dataset that he try to connect to from Excel.
      To resolve this add the needed user permissions (either explicitly user or via group the user is part of) to the relevant Power BI dataset.

      Common case this error occur is when you share Excel workbook that was created by using “Analyze in Excel” (which has connection definition to Power BI Dataset) with other users that doesn’t have permissions in Power BI on the relevant Power BI Dataset.

  3. I’m still confused by the sharing. I saw the demo of pushing a Power BI solution to the web, but can I generate a solution for a client who then shares it with his clients such that all of us are varying degrees of co-authors with several email domains?

    I see all of this great stuff around Power BI and am still unclear if it can be shared like an Excel workbook in DropBox.

  4. Installed the required update first. Tried to Analyze Data in Excel 2013. Still no success as Excel says theres no connection to the db…..

  5. Great post Avi. Do you know if Analyze in Excel will work in Excel Online? Pivot tables are a great “visualization” that Power BI doesn’t do well yet and I could see the benefit of putting a central data model in PowerBI.com and then sharing a connected Excel Online workbook with other users.

    1. Good question Tim (“Do you know if Analyze in Excel will work in Excel Online?”)
      I’ll try to check with the MS Team and see if I can get an answer

      1. If it does work in Excel Online, then I wonder if you could hardcode your credentials into the Excel file and only allow people to use the Excel Online Web version of the file (no download capabilities so your credentials wouldn’t be exposed). Then you could share the Excel Online file with whoever you want. If they could still interact with the file and pull data from PowerBI.com, it might be an easy way to share visualizations with all of those “small, disenfranchised folks who’ve been ignored by the establishment” (according to Oz).

  6. As I understand correctly, “Analyze in Excel” can be accessed via report submenu and therefore can be used by report owner or by Pro users via Organization content pack report sharing. Free users can analyze only their own datasets as they cannot access shared dashboards reports submenu?

    1. What is a content pack?

      If I build a solution for a client and her team, then several months later they need me to build in additional functionality, is it as easy as Dropbox where
      1. When I’m done building the solution I leave the folder and
      2. When they need me to make modifications they re-invite me to the folder?

    2. Marti, you are correct. I’m not too excited about that part. Supposedly “Analyze in Excel” is a free feature. It is – but only for your own data set. Which is pretty pointless scenario in my mind (or less useful). The useful scenario is ability to share with 10/100/1000s of users within your Organization – to enable “Analyze for Excel” in that scenario, Organizational Content Packs are the way to go.

      Organizational Content Pack (Paid/Pro feature) are another way of sharing PowerBI content.

      Typically you share Dashboard, users get to see the Dashboard, can click through to the underlying Report and can ask questions via Natural Language Q&A. But besides the built-in interactivity in the Report/Q&A, users cannot really change any element or build their own reports. (They also cannot click “Analyze in Excel”).

      Via Organizational Content Pack – you can package up Dashboard/Reports/Datasets – but users would be able to modify the Dashboard/Reports.

      Oz, what you are asking can perhaps best be done using Groups in Power BI. Groups give you functionality similar to what you describe. If you get added as a Member in the Group, you would get “Read/Write” access to everything inside. When you get kicked out, you would lose that. Check that out, see if that works. That only works within domain though, not cross domain.

      1. The cross-domain issue is a deal-breaker. I was hoping that all the “push to web” stuff also meant the cross-domain issue was solved.

        This is so disappointing and I feel like I’m falling behind. People are learning and using PowerBI. I see all the cool stuff that’s going on. But the collaboration is a non-starter for small businesses.

        1. A lot of small businesses don’t have an internal data person. So, they rely on outsiders to build data solutions.
        2. A lot of them use gmail as their email domain. PowerBI won’t allow signups via gmail.

        Maybe the BI world needs a Donald Trump who’ll inject turmoil on behalf of the small, disenfranchised folks who’ve been ignored by the establishment. Hmmmm …

        1. Oz, good point there. It does make things very hard for consultants. It sets up a barrier where the client has to set up our account on their domain before we can meaningfully help them with Power BI. The ability to share with external users was a user submitted idea. Very popular and MSFT team did act on that. But really more needs to be done there for these cross domain scenarios to work. Another Power BI idea campaign?

    1. This was my first thought as well. The assumption that the majority of Power BI users want to, or are even able to work in the cloud just isn’t true in my experience. What I was ideally hoping for was an Excel ‘get data’ option that would allow connection to both a pbix file model, and also a cloud published model.

  7. Hi and thanks everyone for your feedback thus far. I would like to share some info on the ability to Analyze in Excel.
    The feature is released as of last Friday
    It is a Power BI feature, i.e. it starts from PowerBI.com. Hitting Analyze in Excel creates a thin ODC file with the connection string details to the selected Power BI dataset for you to download.
    Before opening the ODC file in Excel you must first install the Excel update we provide through PowerBI.com
    Once you have the latest Excel update, you can connect Excel, any version of Excel starting with Excel 2007, to a PowerBI dataset.
    Excel on-line experience – saving the workbook with a Pivot Table and slicers etc. and then consuming it through Excel on-line will only show the last saved state of the data, no ability to refresh, nor slice and dice the data through Excel on-line, yet.
    You can find more info on the feature through our dedicated support page https://powerbi.microsoft.com/en-us/documentation/powerbi-service-analyze-in-excel/
    Hopes this helps

    1. @Avive
      When will we be able to do the same for the Power BI Desktop (pbix file)
      – This is the “killer” feature we having been waiting for. Having this in Power BI alone does not help a “business user” – It again falls back in to the “IT Domain”

    2. @Avive: This is an awesome feature, and great that it got relased so quick. Some of us are still getting the :Forbidden error when opening the .odc file. Could this be because the model is not built according to the requirements outlined in your article, or is it a backend error from the server?

  8. @Oz – “The cross-domain issue”. When I worked as a consultant, I would normally receive a local user account for my work on site. Would something like that work with Power BI? i.e. the organization hiring you can give you a user in a group used for AuthN/AuthZ with their Power BI tenant, this way you are allowed to make changes to the Power BI artifacts, and allow it to be consumed by everyone else in that group. For security reasons this user password would be updated once your job is done.
    If this is off target, I would love to set up a call to hear about the need and what we may do to support it.

    @Sam – “Just introducing this in Power BI does not help a lot”, please elaborate on the need, I’m not sure I understand what’s missing and why is Analyze in Excel no help to you – thanks

    1. @Avive, it’d be great to arrange a call. It sounds like it might be a solution for a client to set up the PowerBI and add me as a user, rather than setting me up with one of their email addresses. You’ve got me curious.

    2. @Avive – We need to connect to the Data Model in the Power Bi Desktop File (.pbix) – Is “Analyze in Excel” coming to Power BI Desktop

      1. Hi Sam, please clarify for me, are you asking to be able to do Analyze in Excel of a data set in a pbix file?
        or are you asking if we are working on Analyze in Power BI desktop too?
        Both are interesting, if its the first you are referring to, does this mean you are looking for a full solution without using PowerBI.com at all?

        1. @Avive -I am saying if we create a pbix file using Power PI Desktop – we should be able to connect to the Data model stored in the PBIX file from Excel

          1. @ Sam – Analyze in Excel for pbix is not on our plate at this stage, I need to think some more about what this means, what scenario it serves and weather it resonates well with Power BI concepts.
            Is this a canonical scenario for analysts/consultants, is there a user voice ask about it? I’m not sure I found one, can you point me to it please – thanks

          2. @Avive, the pbix thing was actually in the original user voice ask in the “bonus points” section: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/8984308-ability-to-connect-excel-to-power-bi-data-model-an

            So it’s difficult do know how many people were actually voting on the pbix feature.

            However, there is a separate “ask” that Marco Russo created: https://ideas.powerbi.com/forums/265200-power-bi/suggestions/7345565-power-bi-designer-api

            He also wrote a blog post about why he asked for it: http://sqlblog.com/blogs/marco_russo/archive/2015/08/31/power-bi-desktop-excel.aspx

          3. @Avive As a consultant I would argue that if there’s a proven use case for ‘Analyze in Excel’ with cloud based models then there is implicitly a use case for the same with on prem. My understanding is that the current BI roadmap is targeted at bringing more parity of functionality with the two different deployment/distribution scenarios. I think this is the correct approach, as there is clearly still a huge demand for ‘on prem’ solutions.

          4. @Avive – Here is the scenario
            I am a business user

            I DO NOT want to depend on IT for my business reports

            PowerBI.com therefore – is ruled out

            In the past If wanted to create a Database where I store my data and create the business logic – I would use Access – build my queries etc. etc.

            Access unfortunately is dead – MS does not have a clear road map for it

            With Power Query and Power Pivot built in to Power BI Desktop -it now becomes the new Access

            But it is two steps away from becoming the Desktop Database for the millions of business users

            Step 1: Allow Excel to connect to the Data Model in PBI Desktop file (PBIX) – stored on the local drive or a share folder

            Step 2 : Enable file encryption to the PBIX file (even a weak encryption like that in Access is fine)

            Connecting to the Data Model in PBIX is already possible from Excel – It is just that it is not officially supported

            You can try the following

            1. Open a PBIX file
            2. Open Excel
            3. Open DAX Studio and connect to the PBIX file
            4. Make a note of the local host no appearing at the bottom left corner It should look like localhost:xxxxx
            5. In Excel go to the Data Tab – From Other Sources – From Analysis Services
            6. In the Next Dialog in Server Name type localhost:xxxxxx – Next – Next – Finish
            7. And voila – you have a Pivot Table

            Now the problem is the localhost number keeps changing every time you open the pbix file
            So this is not a sustainable solution

            All Microsoft has to do is officially support it !!!

          5. @Avive – Additional use case….. Company ABC Ltd are not currently doing BI in the cloud, due to legal, culture, or some other reasons. The users want to use Power BI, and feel the sensible way to guy is V2 (.pbix based) rather than V1 (Excel based). The business users very much appreciate the ability to build simple models and expose the data via dashboards in Power BI Desktop. However, they are unable to live without pivot tables, as they feel they are a much better fit for data exploration and analysis. Unfortunately they are currently unable to work in this way, discounting an unsupported hack using DAX Studio. Worst case scenario, the users are then pushed into the arms of another vendor (probably Tableau) which allows them to build dashboards and do data analysis all in one place. All the logical arguments against going down this route fall on deaf ears, as MS just don’t allow them to work in the way they want.

          6. @aviv

            i work in a construction company and for contractual reason, we can not share any data with our client using the cloud, so power bi is not an option, and even if it was, people still want Excel.

            and i am using the exact scenario described by Sam, if only you guys fix the ip port, it is frustrating that we have to use a hack to connect to Power Bi Desktop

  9. This is just a KILLER feature that nobody else (bi tool) is going to have. Sure… the ‘export to Excel’ button is ubiquitous… but the ‘open the data model in Excel and do whatever the hell you want!’ button is sssoooooooooo much better.

  10. We have an understanding of the reasons for the “Forbidden” error
    1) Common scenario – free users get forbidden
    We are working to fix that and allow Analyze in Excel for all users
    Currently it is open for pro users

    2) Advance case – if you have multi user names you are using with Power BI
    To resolve this case you should follow the workaround steps in this link http://community.powerbi.com/t5/Service/Anyone-got-the-new-Analyze-in-Excel-feature-working/m-p/24944#M6485
    With these you can sign in from Excel with a specific user (and sign out from prev user). This is supported by explicitly setting user ID property in the ODC file.

    We will update once the fix to allow free user is available
    Thanks all for helping us understand the issue! and for your patience for the full solution

  11. Hi @Avive,

    How exciting is this? Analyze in Excel now works for the ‘non-paying’ users of Power BI as well. Thank you for your contribution to fixing this. Just tested it and it works great.

    Chris.

    1. Yup same here. I’m still getting Forbidden error when using my free account. My paid account works. Did follow the suggestion in community article for the dual login scenario. That helped (in the sense that I got the login screen, which earlier was not showing up), but after logging in I get the forbidden error. Soon, I hope. Although I realize that to really leverage the feature you need Pro anyway – so that you can share via Content Packs/Groups – allowing others to Analyze in Excel.

  12. We fix the issue for free users and now “Analyze in Excel” is available and working also for free users.

    Please try and lets us know in case you still have issue

    1. Awesome! I just tried it and it works! I actually just opened the old .ODC file that I created before and it worked so the fix must have been on the server.

  13. @ David Lindley/mim
    We hear you, we will initiate a discussion around this requirement with the relevant folks, I cannot promise a thing at this stage, but I will be glad to reach out to you and further discuss the need and options to handle it. Please let me know if and when we can setup a call at https://www.linkedin.com/in/avivezrachi

    1. @Avive
      Is there any way in which we can connect. There is a Major Project on Power BI Desktop held up because of Inability to connect Excel to the Data model in a pbix file

Leave a Comment or Question