skip to Main Content

Post by Rob Collie

image

Another Excellent Step:  Power BI Desktop Now Converts Excel Power Pivot Workbooks

Download the Latest Version of Desktop and Try It

I know, I know.  I’ve been talking about Power BI Desktop a lot lately. But to be fair, I had written NOTHING about it until very recently, so there was a backlog of sorts to be cleared.  I feel like that “behind-ness” has now been adequately addressed by posts here and here, with additional posts here and here by Avi and Andrew, respectively.

But now, they’ve added something significant that you likely find quite interesting:  you can take your existing Power Pivot workbooks and convert them to Power BI Desktop (PBIX) files.

Download the Latest Power BI Desktop

Brings in Data Sources, DAX, Relationships, Queries, Power View…

power bi desktop version diagram

All of These Tables, Relationships, Calc Columns, and Measures
Were Created in Power Pivot for Excel 2013

I don’t have time at the moment to test everything out, but on the surface it appears to all be working.

I’ve tried briefly importing Power Queries and Power View sheets, and both worked.

It does NOT do anything with your “regular” Excel sheets, so no pivots, cube formulas, etc. get converted.  But this is to be expected.  PBI Desktop would have to BE Excel in order to do that.

What Doesn’t Work:  2010 Workbooks (Yet?)

I tried loading a 2010 Power Pivot workbook and got an unfriendly error:

Annotations are allowed on Script Command objects only for Tabular databases with CompatibilityLevel equal to 1103 or higher.

Probably Should Just Say “2010 Workbooks Aren’t Supported Yet”

I have to think that support for 2010 workbooks is in their plans, though.  Power Pivot 2010 is still VERY popular, and one of the primary reasons that Power BI Desktop exists is in order to circumvent the “we aren’t running the latest Excel version” problem.

Step in the Right Direction.  Additional Steps Remain.

1) We need to be able to go the OTHER way as well.  Yeah, PBIX models need to be convertible into Excel files.  Yeah, I know that’s super difficult for the engineering teams.  Here’s an idea:  just give us a converter utility, don’t try to retro-port such capability back into Excel itself.  If it can grab the Power Pivot and Power Query stuff and create a workbook with no visuals, that’s great.  And if it only works for Excel 2013+, OK.

2) We need the ability to connect Excel directly to PowerBI.com models.  Avi has been banging this drum loudly.  He will continue, and for good reason.

3) And oh yeah, 2010 import support as mentioned above.

Then the circuit will be complete.

Rob Collie

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 27 Comments
  1. Rob, as always, great stuff! Hey, I’d like to take this opportunity to ask you a question. Does PowerBI have a 64-bit and a 32-bit version? I cannot recall a prompt to that effect when I installed PowerBI. I would need the 64-bit version since I need to work with large files. I’ve colleagues who have Office 32 bit (the current corporate standard). Could they install PowerBI 64-bit alongside their Office 32 bit installation? Thanks!

    1. On the Microsoft PowerBI download page are both 32 & 64 bit installers so can do both. When you go to the PowerBI webpage and just click on the “Download” button it looks like it figures out if you are running 32 or 64 bit windows and downloads the appropriate msi.

  2. Yay! This change is what persuaded me to start using PowerBI, and publishing a dashboard. Still haven’t figured out sharing the dashboard. I’m using the same organization domain, but no email is coming through…

    1. My sincere apologies, Fred. My previous comment was unclear. I meant to say: “Not only cube formulas or pivots are NOT imported but NEITHER hierarchies NOR KPIs.

  3. I’ve done a clean install from ver 2.25.4095.1301 but upon booting Pwr BI, I keep receiving the same error below:

    Error Message:
    An internal error occurred in the Microsoft Internet extensions

    Uninstalled and reinstalled to no avail. Had to revert back to previous version.

    Anyone else having this problem?

  4. Here is what I want. A stand alone Power Query/Power Pivot “model” document that both Excel and Power BI can consume. Like a tabular model for those of us that don’t have access to to SSAS

  5. Do you know that you can *already* connect Excel 2013, 2010, and 2007 to Power BI? Just connect to Analysis Services specifying the info you see in the lower right corner of DAX Studio (it is “localhost:” where the number changes at every execution). Totally unsupported by Microsoft, kill the connection as soon as you close Power BI Desktop, but useful to test a Power BI model with a decent Pivot Table… 🙂

    1. Could you explain this a little more for the less technically inclined? Does this mean you can actually pull up the field list in excel and build a pivot table from the Power BI desktop model? The model that is saved on your computer (don’t even need to upload to Power BI?)

      I tried doing this and got an error message… is it supposed to be entered as localhost:spid ?

      1. Sorry a few parts of the message with angular brackets were removed.
        You have to connect the pivot table to Analysis Services, and as a server name you have to write “localhost:12345”, where instead of “12345” you have to type th enumber that you see in the bottom-right corner of the DAX Studio window, which displays the exact values to use. This number changes every time you start Power BI Desktop. It works only with Power BI Desktop and not with Power BI service (on the cloud).
        Of course, you have to install DAX Studio. Open Power BI Desktop ,then open DAX Studio, connect to Power BI Desktop and see what is the right connection string.
        Please remember: THIS IS NOT SUPPORTED! Use it at your own risk.

        1. @Marco – Many Thanks – I tried it and it works brilliantly

          This has enormous potential – Now the PBX file (which seems to be a CUBE File) can become the “Middle Ware” and we can use

          a) The 1 step ahead version of Power Query present in Power BI Desktop
          b) The 2 step ahead version of Power Pivot – with supports Bidirectional relationships and the rich DAX editor of the Power BI Desktop

          And Excel can becomes the Reporting front end just to show the Pivot Table and the visualizations

          The only thing to remember is before refreshing the pivot – once again open the pbx file – open DAX studio – connect – find out the new localhost number and update this number is the connection properties of the Pivot Table and then refresh the pivot

          But I guess this is a temporary phase – MS has promised the famous “E2E” button – Export to Excel 🙂 in Power BI Desktop as well !! and I am hoping along with it a more straight forward way to connect to data model in Power BI Desktop

          Can you share the code that you use to find out the local host number – then we can use VBA and do this ourselves

          1. I just tried something and the results are AMAZING

            a) Excel File with Fact Tables(3) and Dim Tables(7) – 20MB

            b) Pbix File connected to the Excel File with – Some Transformations + Reasonably Complex Data Model + Dax Measures – 8 MB

            c) Excel File with a Pivot Table connected to the Pbix File – 15 kB !!!!!!! – yes kB

            Refresh – Lightning Fast – Now we dont have to worry about the Data model getting corrupted

            This also means We can still go on using a 32 Bit Excel – PBI Desktop is 64 bit (matches the bitness of the operating system) – so therefore can handle large data

            @Rob – Does Power Update support pbix files ???

        2. Wow, thanks for sharing Marco (and Sam for drawing my attention to this). It works a treat! And my testing shows it is very performant!

        3. Thanks for this Marco. Another way to see the port number is to open a command window and enter >NETSTAT | FIND “[::1]”
          Results in this type of listing..
          TCP [::1]:52769 W5E012895PR30A:52770 ESTABLISHED
          TCP [::1]:52769 W5E012895PR30A:52771 ESTABLISHED
          TCP [::1]:52769 W5E012895PR30A:52773 ESTABLISHED
          TCP [::1]:52769 W5E012895PR30A:52776 ESTABLISHED
          TCP [::1]:52770 W5E012895PR30A:52769 ESTABLISHED
          TCP [::1]:52771 W5E012895PR30A:52769 ESTABLISHED
          TCP [::1]:52773 W5E012895PR30A:52769 ESTABLISHED
          TCP [::1]:52776 W5E012895PR30A:52769 ESTABLISHED

          and the most common port number is the one you want.. ie in this case 52769

          The servername is then localhost:52769 for connectivity.

          So getting a copy of your PBI Desktop data model across into Excel is doable. In Excel use PowerPivot-> Manage -> Data -> From Database -> From Analysis Services … to get the ability to use a DAX query to bring in your data model. Or just in Excel use the

          It works also for Report Builder 3.0 connections so you could leverage your PBI Desktop model to create reporting services content too.

          Maybe the PBI Desktop tool can replace Excel as the mashup tool of choice for self-service bi development work even at the enterprise? License only allows for one copy per premises though? How do they compare with regard maximum data model size? 10M for Excel i think. Don’t know for PBI Desktop

  6. You guys are the smartest people I know! Anyway to get Marco and Sam’s trick to work across a network? It works brilliantly on the local computer!

  7. Just want to confirm, importing a PowerPivot data model into Power BI from Excel works only with Excel 2013 or above. Correct?

    I’ve tried multiple times to import models from Excel 2010 and received an error, “CompatibilityLevel equal to 1103 or higher”. Turns out “CompatiblityLevel 1103” refers to Excel 2013.

  8. After having worked with Power Pivot for a while, I decided that I would use Power BI exclusively for a client. However, after only a couple of hours of modeling, I realize that the GUI within Power Pivot is better in some ways, for example you get a better overview of measures. Do you know if it is possible to import Power BI files to Power Pivot?
    Thanks for a great blog!

  9. Rob, really appreciate all your blogs
    quick question, do I have to rewrite all my DAX formulas when I import my PowerPivot model into Power Bi as well as reconnect all the tables?

Leave a Reply

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