skip to Main Content

Post by Rob Collie

Transitioning from Power Pivot in Excel to Power BI

Can Excel Pros Make the Jump?

For 5+ years, Excel was the ONLY “place” where we could get our hands on the awesome power of the DAX engine (aka Power Pivot) and the M engine (aka Power Query).

It wasn’t until recently that we were given ANOTHER place, in the form of Power BI Desktop, where we could use those incredible analytical engines.

And even today, Excel remains REMAINS the best STARTING place for the world’s tens of millions of Excel Pros (who I have long defined as “anyone who uses PivotTables, VLOOKUPS, and/or SUMIF multiple times per week.”)  We’ve seen this over and over again, both with other tools like Tableau and now with the new Power BI:  these Excel folks are quite wary of new tools, leading to widespread poor adoption.

So, given that there’s a sizeable number of humans using the Excel version of these tools, and that the Excel version is by far the best “on ramp” to the new languages of DAX and M, it’s a natural question…

…how hard is it to pick up Power BI Desktop if you are already competent with Power Pivot?  (and perhaps also with Power Query?)

But First…  WHY Make the Jump at All?  SHOULD We?

Don’t EVER tell me that I HAVE to use a new tool. That equals “huge disruption to my workflow” in my mind and I don’t have time for such nonsense. Instead, let me discover that I WANT to use that new tool, to IMPROVE and extend my EXISTING workflow. -any Excel Pro

I am 100% sympathetic to the Excel Pro stance of resisting new tools. Ich bin ein Excel Pro myself, as I’ve said before, and in the early days of Power BI Desktop’s existence, I kept it at arm’s length.

 

New tools are “scary” to me when my existing tools WORK, and work well.  Most people aren’t all that fond of technology, and don’t get excited about a new tool just because it’s new and shiny.  That’s true for me, too, even though I worked at Microsoft for 13+ years.  (But at Microsoft, I was generally the exception – most of my colleagues voraciously devoured new tech).

As long as my existing tools work well, a new tool generally comes along to serve someone ELSE’s agenda, not mine.  The software vendor’s agenda.  IT’s agenda.  Not my agenda.

And the Excel-based version of Power BI (aka Power Pivot, with its newer sidekick Power Query) has worked VERY WELL – for me personally, for my colleagues here at PowerPivotPro, and most importantly, for our many clients.

So why even LOOK at the new Power BI Desktop?  There are 3 primary reasons:

The Three Biggest Reasons Why Power Pivot Users Can Benefit from Power BI Desktop

The Three Main Reasons Why Excel Power Pivot Users Benefit from Power BI

  1. Diverse Modern Visuals:  Power BI offers a wider variety of charts than Excel – an ever-growing list in fact.  Furthermore, these visuals don’t have a 30-year history to them, so they feel “more modern” than Excel, even though Excel has made significant strides in that department lately.  And multi-interactive “dashboards” where clicking any one element filters the other elements are simple in Power BI versus “requires a lot of elbow grease” in Excel.
  2. Superior Mobile Experience – yes, Excel has also made great strides here, but fundamentally, Excel (and Web Excel) are PC-oriented applications, and don’t “translate” all that well to mobile devices.  Power BI has purpose-built apps for displaying dashboards on specific devices.  The people who consume you work will love this.  It’s a monster sales pitch for how valuable YOU are.
  3. Quickest path to a “server” – in the past, publishing your work to the web (and mobile devices) required an expensive and complicated SharePoint install.  For those of us who are cloud-friendly, we can be up and running with a free (yet fully functional) Power BI publishing site in mere minutes.  In many cases, you don’t even really need to tell IT you’re doing it.  Heh heh.

So How “Learnable” is Power BI Desktop for Excel Power Pivot Folks?

Enough of the “Why,” let’s talk about the “How.”  If you’re already familiar with the Excel-based Power Pivot/Power Query world, how “foreign” is Power BI Desktop?

I’ve been waiting awhile to perform this analysis even for myself.  But the latest public version of Power BI desktop added a few critical missing pieces, and it’s go time.

Download, Install, and Launch:  Simple

  1. Download from here
  2. Run the installer (the MSI file)
  3. Launch Power BI Desktop from the Desktop/Start Menu

OK, it’s a Ribbon-Based App.  We Understand Those.

A lot like Excel…

Power BI Desktop

It’s a Windows App with a Ribbon, Just like Excel

And the most obvious starting point is Get Data, just like it would be in Power Pivot…

Power BI Desktop Get Data

Getting Started: It is Pretty Obvious What to Click First

Get Data = Power Query (but just not named that)

If you’ve  used Power Query in Excel, Power BI’s list of data sources should look quite familiar:

Power BI Desktop Get Data

Basically the Same List as Power Query.
(But Even if You’ve Never Used Power Query, This Isn’t Going to Cause You Any Trouble.)

I chose Access, because I want to step through my usual teaching script.  I browse for the file and get this:

Power BI Desktop Get Data

Next Step is a Lot Like Power Pivot’s “Import from Database” – Allows Multi-Table Import, Preview of Each Table, Filtering, Etc.

After Import…

Once you’ve loaded some data, the “whitespace” occupying the left side of the app is still blank. This is where you’re going to assemble your Dashboard(s), which we Excel folks can think of as our pivots and charts.

Just like in Excel/Power Pivot, the field list IS now populated:

Power BI Desktop Immediately After Data Import

Field List, Reporting for Duty!

But Seriously, SHOW ME THE DATA!

One of my “turnoffs” with Tableau is that it seems to stubbornly refuse to show me the tables of data that I’ve loaded.  Until this most recent build, Power BI Desktop also lacked a Data View, but that was not due to some religious, visuals-centric silliness like in Tableau’s case.  It was just “we haven’t had time to build that part yet.”

Power BI Desktop Now Has Data View!

We Now Have a Data View, Which is Very Important in My Experience
(It’s simply impossible to write some calculations, and almost always impossible to debug,
w/out seeing the data)

Data View is CRUCIAL, But Since It’s New, It Still Needs some TLC

My First Nitpick: making us use the field list to switch between tables in Data View may seem like an obvious and elegant choice, but forcing me to sacrifice multiple columns of displayable data is not the best tradeoff.  I encourage the Power BI desktop team to bring back sheet tabs as the navigation method here, because unlike in Dashboard view, the field list is NOT something intimately involved with my activities in Data View (ex: no drag and drop gestures), and the real estate does make a difference.

Second Nitpick:  I can’t yet filter in Data View.  I assume this is still coming, because it’s a crucial navigation tool during debugging.

But please don’t take those nits as an indictment!  I am still super, SUPER happy to see Data View appear in the tool.  Excel pros, rejoice.  I was worried we wouldn’t get Data View this soon.

Is there a Diagram View?  Yep!

The other important view in Power Pivot is Diagram View, and Power BI absolutely *does* have an equivalent:

Power BI Desktop Also Has Relationships View, which is Diagram View Reincarnated

Look Familiar?  Just Click the “Relationship” Button (Highlighted)

Nitpicks with Diagram, I Mean, Relationship View

Again, I am suspecting/hoping that these are in the plans, just not built yet:

  1. I can’t drag/drop to create relationships in Relationship View.  Super nice feature that will be missed until it shows up here.
  2. In fact I can’t do seemingly ANY editing in Relationship view yet.  Can’t delete tables, rename tables, etc. – this is actually encouraging, since it seems to suggest that “read only” was their first milestone, and “editable” is still to come.  So we’re likely to soon see drag/drop relationship creation too, I’d think.

OK, so How DO You Create Relationships?

First off, you know that “Autodetect relationships” feature that has been lurking around Power Pivot since 2010?  It’s back, and it actually performs relationship detection during import.  So the three relationships pictured above were auto-created, I didn’t have to do anything other than import the data.  I’m cautiously optimistic about this, but will need more real-world experience.

Second, there’s a Manage Relationships button on the ribbon, and it leads to a familiar-looking friend:

Power BI Manage Relationships

Displays the Existing Relationships, Just Like in Power Pivot

What happens when you click “New…” however is a wholly-unexpected surprise:

Power BI Create Relationship Dialog is MUCH Better than the Power Pivot Equivalent

OMG OMG OMG!  This!  THIS is an example of UX Design in its Highest Form.
(Seriously, I want to write lovesongs about this dialog and everyone involved in its creation.)

One of my longstanding concerns about the Power BI team goes something like this: in software circles, there are Engine Teams and Experience Teams.  Engine teams give us massively complete and robust things like SQL Server and the DAX engine.  Experience teams give us beautiful, easy-to-learn and fun-to-use things like the iPhone.  Teams like Excel sit somewhere in between.  I hated Power View primarily because it reflected an Engine team (the SQL folks) underestimating the myriad little details that go into building good User Experiences (UX).  When it takes me 20 minutes to notice that there IS indeed a button to increase font size…  yeah.  I just can’t.

I’m not trying to be mean here!  Engine teams always underestimate the difficulty of building good UX, and Experience teams always underestimate the difficulty of building good engines.  It’s a cultural shift for the SQL team to become an Experience team, and it doesn’t happen overnight.  We can forgive the Dark Ages (ahem, Power View) if they give way to a Renaissance.

Anyway, this dialog is a beautiful example of the tide turning. “What’s the big deal Rob,” you ask?  “It’s just a silly little thing, and we already had one.”  Well that’s my point.  No detail is too small when it comes to UX.  The old “four picker” relationship dialog forced me to go on the basis of column names alone.  I never got to see the two tables’ DATA during relationship creation, EVER (no matter how I did it.)  That led to a lot of canceling-and-flipping-between-tables to make sure I knew which two columns actually match.  This new dialog finally lets me do it without leaving the dialog and coming back.

That they even took the time to redesign the existing “4-picker” relationship dialog is an Amazingly Good Sign.  Attention to detail, folks.  It’s what made Steve Jobs great.

But now that I’ve expressed my heartfelt love, let me pick some nits with the Advanced section of this dialog.  Terminology is the enemy of understanding.  And we’ve got some serious terminology here:

Power BI Advanced Relationship Options: Cardinality and Filter Direction

Yes, BI Pros May Know the Word “Cardinality,” But Excel Folks File it Under “WTF.”

Once Cardinality Sets In, There is No Known Treatment. The Binoculars are Merely a Placebo.I’m chuckling right now, because when I teach classes, I ALWAYS teach my students the meaning of the word “cardinality.”  Not because it’s important, AT ALL, but because it makes us sound smart.  I explicitly tell people that if they throw this word around a lot, people will be impressed but won’t understand.  And here it is in the UX.  (The other “smart” word I teach is the word “scalar.”  Both words are taught as a means of adding levity, which is a fancy way of saying, they’re jokes.)

I actually think the meaningless word “Type” would be a better choice here.  And there should be a little “i” in a circle so I can click or hover to get an explanation.  Words like this just scare people without adding anything.

High Cardinality Means Lots of Unique Values. But Perhaps a Religious Figure Too? I Sure Hope People Using Search Engines Find this and Are Very, Very ConfusedOK yeah, this IS under Advanced.  But Excel people will look here, and we want Excel people to not feel like they are missing some sort of magic decoder ring or secret handshake of the Royal Society of BI.

Next, what is the meaning of “cross filter” in this case?  I don’t know.  That’s the term I coined for the performance problem in Excel Slicers.  We also have a DAX function called ISCROSSFILTERED.  The waters are muddy here.

Maybe Cross Filter is exactly the right term, technically, to use in this dialog. I am not smart enough to know. But it begs the question whether there are different kinds of filtering aside from direction.  It raises unnecessary questions.

So how about “Filter Direction” instead?  That sounds good, and the choices could be “One Way (from one to Many)” and “Two Way.”  I think those would be more accessible to the masses.  Like I said, these are nits, but in good UX, all the nits get attention.

Lastly, I was expecting to see “Many to Many” in the dropdown list, but try as I might (I even tried linking two Data/Fact tables to each other), I couldn’t get that option to appear.  More on this as I learn more.

sshot-10

I was expecting to also see “Many to Many” in the list.
(But admittedly my data just might not have been the right fit to “trigger” that option)

Moving on to Formulas!

In Power Pivot, we have Measures and Calculated Columns.  Both are easy to find and add in Power BI Desktop:

image

Clearly-Discoverable and Convenient Buttons for Measure and Calc Column.
Both Use the Same (and Much-Improved!) Formula Editor

I don’t even have any nits here.  It’s all good.

And, of course, it’s all still DAX, so if you’ve been using Power Pivot, you already just know it.

sshot-13

Check Out the Paren-Matching on the New Editor!  Hubba Hubba!
(My heart is all aflutter over nice touches like this)
(Ooh and we don’t have to do the := arcane dance anymore, just = …  Nice!)

Editing from the Field List!

This is a nice touch, and in many ways is better than the equivalent Excel experience:

Editing Formulas in Power BI Desktop is Pretty Convenient from the Field List

Doesn’t Matter Whether it’s a Measure or Calc Column:
Just Click it and Edit in the Formula Bar

One Last Trip Back to Nitpick Corner

Just a few more things I noticed before I wrap up in a decidedly positive manner.

  1. On my computer, the field list’s scroll bar is impossibly narrow and hard to “grab.”
  2. When I’m in Data View, there’s a search box in the field list (yay!), but it’s NOT there when I’m in Dashboard view (why?).  We would benefit from it both places.
  3. If I accidentally put a measure under the wrong table, or just subsequently decide I want to move it, I don’t know if there’s a way to do it.  (I didn’t find one.)
  4. I find the wall-to-wall use of greyscale a bit fatiguing.  I would like a bit more color – both because it would make the tool more fun to look at and use, but also because I think some extra contrast would help users spot the things they need.

“Wait!  You Didn’t Talk About X!”

That’s right, a lengthy “review” with nary a mention of visuals!  Does the Funnel Chart deliver the goods?  Are the dashboards sufficiently interactive?

Yes, I am leaving that for later, but honestly, I’m pretty sure those things are already Pretty Darn Good.  The visuals are getting a lot of attention for sure, and my long-simmering worry was that the basics would get botched.   Specifically, whether it would be Alien to the Excel crowd, and specifically, Alien to the Power Pivot crowd.

THAT is the #1 worry I’ve been carrying, but my recent time with the Desktop has me feeling MUCH better about it.

My Verdict is in…

I Think Power Pivot Users Can Transition to Power BI Desktop With Little Difficulty

One last word about the nitpicks:  for perspective, I have LIVED with, no, LIVED IN, the Power Pivot and Power Query toolset for the past 6 years.  And to boot, I’m an ex designer of software for Microsoft with a penchant for detail.  For me of all people to pick up a tool that was developed on a rapid-iteration schedule, and that is intended to parallel that entire toolset, and find only ten small-ish things to complain about, is a pretty strong endorsement.

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

This Post Has 65 Comments
      1. Thanks for the feedback. We have heard this ask a few times now and I’ll make sure it is on our roadmap. Technically, it is a hard challenge but the Power BI team is always up for a tough challenge.

        1. Pedram, this is a complete non-starter for me in my world as a consultant. Right now I have 4 clients that might be able to use PowerBI, but I don’t want to ask each of them to set me up with one of their email addresses. That’s something I just don’t want to try to manage: years of random email addresses.

          I’m genuinely fascinated by PowerBI and want to dig into it, but any solution starts with the ability for me to be collaborate with the client, turn it over to them, and make modifications if they call me months or years later.

          Then, there are things I’d love to build and share with the public. Hopefully share-ability will be coming soon. It’d be nice to find out what the fuss is all about.

        2. Thanks! I have been impressed with the new Power BI Desktop, and this functionality would make it so I can distribute the dashboards to my clients.

  1. Nice overview Rob! Thanks.

    My only reservation about fully moving into Power BI Desktop for designing is that the only ouput is through PowerBI. The model can’t be used inside regular Excel files for exploration or other purposes that help automate workflows, analysis, and reporting.

    I know Avi has pointed out this shortcoming and I’ve voted on his feedback at the Power BI feedback forum.

    In my experience, Power Pivot and Power Query are incredible beneficial as tools that serve as an intermediary step in a process, often for validation or documented support of an accounting entry or analysis of some kind that draws upon a “huge” data set, not just presenting a final “product” for decision maker consumption.

    This use of the tools requires output into our tried and true Excel files.

    I’m a believer in the product the direction MS if moving for sure!

    1. In April I talked with someone who was representing Power BI. She told me that shareability is a need that they are aware of and it’s on their to-do list.

      So, I’m torn: how much time do I spend learning something that is useless to me as a freelancer? It’s like nursing a Lamborghini that’s sealed inside a steel box. “WHEN AM I GONNA BE ABLE TO TAKE MY FRIENDS FOR A DRIVE??”

      I don’t like falling behind everyone else on the Power BI learning curve. But I’m glad I haven’t spent the last 4 months fiddling with something that still can’t be taken out on the road.

      1. Thanks for the feedback. We have heard this ask a few times now and I’ll make sure it is on our roadmap. Technically, it is a hard challenge but the Power BI team is always up for a tough challenge.

  2. Great! So all we still need is to be able to connect to the PowerBI-hosted model from an Excel pivot table (external data source, just like SSAS) and then I’m sold lock, stock and barrel. Until then, why would I lock myself out of the most basic and fundamental data exploration tool?

    1. ruve1k, I could not agree more. What I am surprised is that this ask has not gotten much attention. As an Excel user, that is #1 for me. I submitted a PowerBI Forum Idea for the same Ability to connect Excel to Power BI Data Model and create Pivot/Charts. Please vote for the idea and also encouage others to do the same.
      In general Excel users need to get more vocal and get involved in the Power BI discussion. I had shared a few of my Excel Power BI ideas here http://ppvt.pro/votePowerBI

  3. Looks like a great tool to me, especially since it is free for the “non-business” version. But, although it is great to VISUALIZE data, you can no longer visualize DATA like you can with Pivot Tables.

    1. It does have a pivot table equivalent now called matrix. Lacks all of the advanced features of pivots at the moment but I’d expect most of that will fill in over time especially as excel folks like u increasingly provide feedback 🙂

      1. I don’t think you can call a “matrix” a pivot table equivalent. The “matrix” is just one more visualization type — which was only made available at General Availability (last on the list). I think that shows that it’s supposed to serve a very different purpose.

        It boggles my mind that MS invests fortunes on a new BI offering but can’t deliver the most basic and fundamental BI exploration tool, namely, the pivot table or cube/model browser (i.e. slice & dice, pivot, drill-up/down, sort, filter, calculated members, asymmetric layouts, etc.)
        One strength of MS BI was that you were already inside of Excel when using PowerPivot or connecting to SSAS. This allowed for copy/paste, GETPIVOTDATA, CUBE functions, use inside of a traditional Excel-based model, etc. But with PowerBI that has been ripped away from us. So not only have they not invested in making the Excel pivot table a better cube/model browser, they have excluded it entirely from their new BI offering. !?!?

        I would hope that they have something in the works, but nothing of this sort has been communicated so I’m not holding my breath.

        1. My thoughts exactly. Pivot Table is a star performer. With dashboards MS is just playing a catchup game with Qlik and Tableau and forgetting the jewel of the crown viz Pivot Tables. There is no equivalent of a Pivot table either in Qlik or in Tableau or in any other tool. Pivot table is absolutely brilliant and ignoring it is the biggest mistake MS is making.

          Secondly I also don’t understand the hurry in which Powerbi Desktop is coming in for GA when so many features which we are used to are missing. Why not reach a stage at which it is at least comparable in features to Powerview, Tableau or qlikview and then release ? Customers tend to see this as a hurried and a ‘me too’ gimmick rather than a really serious effort to bring out an outstanding tool

          1. We hear you. We don’t ignore it.

            PivotTables are amazing. It took Excel 25 years to create an almost perfect tool. Tens of millions are using it and loving it.

            I fully agree that Matrix is not even close right. But it will get better. Excel provides us a brilliant blueprint to follow. Gaps will be filled, just give us some time.

          2. Amir, is it so difficult to enable Excel to connect to the model hosted on PowerBI? Then you would be able to explore the model with an Excel PivotTable just like you can when you connect to SSAS Tabular or PowerPivot on SharePoint.

          3. @ Amir Netz: With respect, while Tens of millions are using it and loving PivotTables, they are far from “near perfect”. As are Slicers that connect them. Good, yes. Perfect, not even close.

            Want to filter a PivotTables based on an external range? Too bad.
            Want to use VBA to filter a Slicer to just one item, or to sync a Slicer with another Slicer? Too bad.
            Want to store your PivotTable settings so that you always get the PivotTable you want when you create a new one? Too bad.
            Want your PivotTable to automatically adopt the formatting of the source? Too bad.
            Want to know what PivotField a user just filtered on? Too bad.
            Want a handy summary of how all the PivotTables and Slicers in your dashboard interact? Too bad.

            Tens of Millions of people use a tool that has frankly some bad UI design.

            Too bad.

            Some of these things can be addressed with VBA. But most of these things require iteration through an entire PivotField in order to achieve. Got 20k items in your PivotField? Too bad.

        2. No. It is not that hard to enable Excel to connect to a model hosted in Power BI. This is certainly something we are looking at with great interest.

          1. “Gaps will be filled, just give us some time.”

            Amir – I am confused….so you had 25 Million Excel users sending you mails urging you to release Power BI ???

            This is the problem that has plagued MS from 2003 onward – There seems to be a great urgency to get a half baked product out of the door and then fix it over a period of time….by collecting feedback from end users about shortcomings that you are already aware of !!!

            Some classic cases : Excel 2007, Windows Vista, Windows 8.*, Surface 1/2, Windows Phone

            Thanks to this perceived urgency Excel users now suffer – Power Query is first updated in Power BI – and later in Excel

            On a side note

            Do you know if we can insert a “Text Box” in a Power BI Dashboard – you know to a write a small note like “Numbers here are based on the following assumptions ……”

            Oh hold on ….I know -in some time you are going to provide an “Export to Excel button” so that we can make these tweaks

            Power BI to me seems to be a huge waste of effort to recreate a tool that will eventually have the “Export to Excel Button”

          2. Amir, that is the best piece of news I have heard in a long time!! I had heard conflicting answers earlier (or maybe that was my poor interpretation).

            I know the Power BI has a lot on their plate and managing expectations is hard (everyone thinks their needs should get #1 priority). But I am heartened by your statement and will await expectantly for the release that enables such functionality to Excel to connect to Power BI hosted Data Model.

          3. Sam, as a general rule, I try (and sometimes fail, admittedly!) to be super friendly when I want something from someone (in this case, better functionality relative to – and interoperating with – Excel), and they are listening and saying that they are working on giving me precisely that.

            We don’t need to “win” the argument and tell MS that they are incorrectly deaf to our needs. We just need them to give us what we need. They are human beings, and so almost by definition, we get more sympathy when we are nice than when we jump their cases. (Doubly so if we jump on them precisely WHEN they turn and talk with us openly.)

            This is a bit of an outlier, to have such senior members of the team dropping by to talk to us. Let’s make them feel welcome to continue doing so. It will help everyone. We are all on the same team, at least to the extent we are able to communicate productively.

          4. @Amir and Rob

            Sorry about that sarcastic comment about – did not mean it to come off the way it did.

            Was trying to say that you already have the worlds best Data tool
            with a billion plus users using it and with Excel 2016 integrating Power Query and exposing its object model to VBA – all that is needed is to have PQ + PP + PV + PM in one Tab on the Ribbon – Call it PowerBI – and make sure it is available every flavor of Excel (Pro , Pro Plus , Home and Student etc etc) and then Excel becomes your Power BI Desktop

          5. @Amir – also have an “Egg on the face moment” regarding the comment about the Text box – could have sworn it was not their in the earlier release ! or may be i was not looking with an open mind 🙂

  4. Rob, great job demystifying this topic. I may just give it a play. Can I suggest one of the next topics be the “How to share your fancy BI dashboard with your less BI savy colleagues”

    I’m still stuck in “Sharing Excel file land” and would like to be deported.

  5. I agree that Power BI Desktop seems ready to go…even with the little quirks you describe Rob. But it’s only output is PowerBI and that’s where the problems are now. Refreshing data often doesn’t work. You have to import the .pbix file from PowerBI instead of publishing it directly from the tool anytime you have a data model change, and that destroys all the dashboards you’ve built. The apps have severe limitations (at least the Android app that I was testing with) such as no filters. It seems PowerBI Desktop is ahead of the curve and waiting for the rest of the PowerBI solution to catch up. I hope it catches up real soon because my small-business clients are going to love this!

    1. Tom, did you try the July version of Power BI Desktop? It has the ability to directly publish to PowerBI.com (“Publish” button in the ribbon). Furthermore, you can also overwrite & replace previously published PBIX datasets & reports, meaning that your dashboard tiles remain working and update with the new data (assuming you didn’t make fundamental changes in the data model and deleted tables that those dashboard tiles were dependent on).

  6. Great overview Rob.

    I have been using Power BI Designer since it was first released in “preview” mode and I have to say, the changes made to coincide with it’s GA are great and far more extensive than any of us could have hoped for.

    Clearly it still has a way to go and there are a few things in Desktop that I think are inferior to the Excel equivalents – for instance, I much prefer the diagram view in Excel. I find it much more user-friendly and less “IT looking” (if that makes sense) and of course, as you have mentioned, the Desktop version lacks a lot of functionality.

    Power BI Desktop also lacks that most essential tool of any BI product – the “Export to Excel” button! The addition of this function alone would probably quieten the most vocal of critics. Let’s hope MS doesn’t make us wait too long to see it..

    Whilst I fully understand the arguments on the Excel side, I think that Desktop is destined to become the no. 1 choice for a whole range of users needing to make sense of their data (especially when that “Export to Excel” button appears)!

  7. Rob, thanks a ton for this overview. Very helpful! It’s great to be at the genesis of something significant, as I think we are here, though the shifting sands are making the landscape a little blurry. As things settle, I would love to see a piece on the merits of Power BI vs. Tableau (which you touched on here), Qlik, Domo, Birst, etc. You know, just a quick but comprehensive analysis comparing 4 or 5 competing technologies. 🙂 Do you have a quick comment on the fundamental difference between Power BI and ‘those other guys’? Does it come down to, “Power BI is cheaper, and has better integration with Excel”?

  8. Rob,

    Thank you for putting together this post on Power BI; it helped me get focused while starting with Power BI Desktop and Power BI dashboards.

    There is a lot that I like about Power BI. I especially like the Power BI team’s response to user requests and all that was added to the GA release in July. I also appreciate the leveraging of Power Query and DAX skillsets that I developed working with Excel 2010 and 2013.

    To me, the greatest benefits of Power BI are centered around making it easy to capture data and quickly create mobile visualizations that enable users to get their critical information at a glance.

    After reading this post I have a more complete picture of what we now have, and together with the nitpcks and user comments, I have a working concept of where Power BI will be going.

    Strange to feel something akin to a belief in the future of a relatively new Microsoft product, but on the other hand, I am so convinced MS has to “pull this one off” to gain credibility in mobile, that I am OK with believing this time around…

    1. @Thomas,
      “I especially like the Power BI team’s response to user requests”
      Its just the Power Query Team that usually responds – PowerQuery is a truly agile product.

      The Power Pivot + Power Map + Power View are just like the Traditional Excel Team – entangled in red tape + slow + not willing to change

  9. I like Power BI desktop as it is an option for those without excel 2010 or higher.
    Also you can flip back and forth between using power query or power pivot formulas.

    Can’t wait till I can access power pivot model

  10. Can PowerBI desktop get data from the cloud (OneDrive), not local storage synced to cloud. Wish to have all the files (data source and workbook) online so the dashboard could be independently refreshed from any device with internet connection. I will essentially need PowerQuery to shape the data before feeding to PowerPivot model, and so it seemed I will need the PowerBI Desktop.

    1. UPDATE: Oops, didn’t see Sam’s response below. Will try that out, looks nerdy but cool.

      en, that is a good question. I know when on PowerBI.com you click “Get Data”, one of the options is “OneDrive” but when using Power BI Desktop I don’t see that (you can connect to locally synced files of course).
      If you keep your files in a central location, Power Update is also a good option to run refresh. Check it out. http://ppvt.pro/pwrupdate
      Power Update runs the refresh locally, instead of in the cloud. So it works differently than PowerBI.com refresh which runs in the “clpud” and requires access (typically via a Gateway) to local resources.

  11. Is there a way for me to get both the visuals of Designer and still allow me to pull from or use my analysis in Excel/PowerPivot? I understand that I can do these Excel PowerPivot things from Designer, but why should I have to if I already have models in Excel/PowerPivot? Are these two going to come together at some point?

  12. @en

    Off course itcan – Its just not officially supported – There seems to be some friction between the Power Query team and the Onedrive Team – so till it gets resolved you can try the below

    Try the below

    let
    Source = Excel.Workbook(Web.Contents(“https://onedrive.live.com/download?resid=AF0F671FC64E8122!1640″),true,true),
    mData = Source{[Item=”DATA”,Kind=”Sheet”]}[Data]
    in
    mData

  13. I wonder if Microsoft ever figured out how many people got onto PowerPivot and the entire suite just because it was latched onto Excel. That in itself was a big crowd puller to the self-service BI environment that Microsoft was hedging against as it’s offering to compete with the likes of Tableau. But that being not available with the new designer model, I wonder if Microsoft can still attract the same amount of NEW users to it’s self-service BI environment?!

    The BIG QUESTION: Is Microsoft going to latch the PowerPivot add-in with Office 2016?

    1. Hey guys – I think we should make it really clear: The Power BI team loves Excel. Period.

      Great Excel support in Power BI is a top priority item for us. We are viewing Excel support in a very broad way. From basic scenarios like import and export to Excel or rendering high fidelity Excel reports directly in Power BI to much more advanced scenarios like connecting Excel PivotTables directly to the models in Power BI and providing the Power Pivot and Power Query experiences in Excel.

      We actually have a full Power BI dedicated team with the mission to make Excel and Power BI be fantastic together. They are reading these comments. Reading the blogs. Reading the user voice. They are listening to you and they are moving fast to make you all happy.

      1. Amir, Thanks for providing some insight as to what is on the PowerBI Team’s radar.
        Would it be possible to share a road map of some sort outlining which specific items the team is currently working on and what they plan to work on in the near future, as well as providing a rough timeline for when specific features or capabilities might be available?

  14. I also expected to see ‘many to many’ in the drop down when creating a relationship. I believe the classic pattern in Power Pivot for many to many relationships is still needed. However, instead of writing calculate([measure],table1,table2,table3) etc, you can set the relationships to be bi-directional and the measures you write just work without having to supply this funky filter context. Still learning this as well but that’s my initial understanding.

  15. So I was already to swallow the Kool-Aid, downloaded file, download realized I needed 64bit (why can’t PQ updates know this? – just sayin’). Did the double click, BOOM – must have at least IE10. My work environment requires actually IE9 as some vendors, Oracle being one, require that level of IE to work with their finance tools or at least our implementation of it.

    Any work around ideas?

    1. Is it possible for you to use IE10 and emulate IE9 for the Oracle work? (In Internet explorer, hit F12 or select settings -> F12 developer tools). There is a drop down that let’s you emulate other browser versions, including IE9. Admittedly I haven’t tried it with Oracle, but I wanted to pass this on in case it helps!

      Drink up!

      1. Thank you for the reply. I tried that actually before I had IT come up and o the older version as it would not work. No biggie, installed onto my what I call the Collie special, a $300 Dell I got at Walmart (Rob boasts being able to run PP on a pretty low end machine).

  16. We keep trying Power BI Designer, now Desktop, and the product is making a lot of progress in a short time. There is a lot to like about the current interface. Unfortunately, every time we try it for a project, we hit a roadblock that will not let us use it. Each time we get closer, but it’s still not there yet. We don’t have Office 365 or Power BI (with gateway) yet, so publishing to SharePoint is a “must have” for us still. We also need views like PivotTables, and the Table and Matrix visualizations won’t quite do it. We need better drill downs and formatting like PivotTables. We need conditional formatting on values. We need to be able to change font sizes. We know you are working on these, and can’t wait to see these improvements. Thank you for the monthly updates!

    We were also hoping PBID would be easier to deploy in a corporate environment, and while easier than Excel 64-bit, the IE11 dependency still creates a lot of friction for deployment. I can’t have anyone just install any of these tools without a 5 minute discussion about other software dependencies. The PBID discussion is easier, but still a barrier –one mostly solved by people bypassing the official IT channels and upgrading their IE on their own. Keep up the good work though MSFT! We like where you are going with this, especially the support for D3! We would love a demo of how take our own D3 and integrate that into PBID.

  17. Last night, Kirill and I were at a PowerBI meeting. I came away with the sense that PowerBI’s fundamental philosophy isn’t in the easy creation of dashboards, and being able to pull data from hither & yon. That’s secondary to team collaboration and reporting.

    I felt like PowerBI is not a tool for businesses that don’t have a large team, a whole lot of data constantly moving around, and a constant need for ad hoc reports.

    1. What kind of meeting, Oz? (Who was speaking and giving this impression – don’t have to name names, just who they worked for and what role.)

      I have my suspicions but want to confirm them 🙂

      1. It was a meet-up group, and I was the only one in the room who didn’t have a regular job. So, maybe I was left with my impression because of the audience and what was relevant to them.

      2. I walked away with a similar feeling, actually… The focus seems so heavy and so much work has been done on MS part on the “work groups” and “content packs”… Yet it feels like like “extras” compared to one of our biggest “non-starters” (quoting Oz here 🙂 of not being able to share reports with our clients without having to create an email for ourselves on each of their domains. But we are remaining optimistic as it looks like this idea is currently very close 2nd on the list of Power BI suggested improvements: https://support.powerbi.com/forums/265200-power-bi/filters/top

  18. A great overview! Note you cant create hierarchies in Power BI Desktop! I havent explored if the reports will drill down if you import a model with hierarchies from Excel. This is a very useful feature in Power View

    1. PowerQuery in Excel has a scripting language of its own, informally called “M”, and has no integration with VBA. You can write a lot of advanced code with M, but M has limited access to Excel and does not have access to the full Excel object model. I assume you can have VBA start a PowerQuery but I’ve not tried it.

      Power BI Desktop is an entirely separate application from Excel and has no VBA interface. It also uses M and is functionally equivalent to PowerQuery in Excel.

      Is there a particular need you have with PowerQuery and VBA?

Leave a Comment or Question