Office 2016 is on the verge of being released, and although Power BI is the cool new thing Excel 2016 has added several new BI-related features too. What is also interesting – and less well publicised – is that several of the BI features in Excel 2016 have been rebranded. Specifically:
- Power Query (which is now no longer an add-in, but native Excel functionality) is not called Power Query any more, but “Get & Transform”. It has also been squeezed onto the Data tab, next to the older data import functionality:
- Power Map is not called Power Map any more, but “3D Maps”
- Power View is still Power View, but as John White points out here it is no longer visible on the ribbon by default, hidden from new users, although it’s easy to add the Power View button back onto the ribbon. Power View in Excel 2016 is unchanged from Power View in Excel 2013. Read into this what you will.
- Although Power Pivot still has its own tab on the ribbon (and has finally got a space in the middle of its name), there’s also a “Manage Data Model” button on the Data tab in the ribbon that is visible even when the Power Pivot add-in has not been enabled:
Clicking this button opens the Power Pivot window. There’s a subtle distinction between Power Pivot the add-in and the Excel Data Model (which is the database engine behind Power Pivot, and which is present in all Windows desktop editions of Excel regardless of whether the add-in is enabled or not) that has existed since Excel 2013 and which is generally unknown or misunderstood. The fact this button is titled “Manage Data Model” rather than “Power Pivot” is telling. - All the add-ins now have the collective name “Data Analysis add-ins” and can be enabled with a single click:
So, clearly Excel has moved away from branding all its BI functionality as Power-something. My guess, informed by various conversations with various people in the know, is that this has happened for a couple of reasons:
- The ‘Power’ prefix was intimidating for regular Excel users, who thought it represented something difficult and therefore not for them; it also made it look like this was functionality alien to Excel rather than a natural extension of Excel.
- Having separate Power add-ins led to a disjointed experience, rather than giving the impression that all of these tools could and should be used together. It also made comparisons, by analysts and in corporate bake-offs, with other competing tools difficult – were the Power-addins separate tools, or should they be considered a single tool along with Excel?
- Previously there was a lot of confusion about whether these add-ins are anything to do with ‘Power BI’ or not. Up to now, depending on who you talked to, they either were or weren’t officially part of Power BI. Now there is a clear distinction between Excel and Power BI, despite the close technical relationships that remain.
The new names certainly address these problems and on balance I think this change was the right thing to do, even if I was quite annoyed when I first found out about them. There are significant downsides too: for example, changing the names means that several years of books, blog posts, articles and conference presentations about Power Query and Power Map now won’t be found by new users when they search the internet for help. Similarly, it won’t be obvious to new users that a lot of content is relevant for both Power BI Desktop and Excel. Now that the Power Query name has been de-emphasised, why should anyone looking at my old blog posts on that subject know that what I’ve written is still relevant for Excel 2016’s “Get & Transform” and Power BI Desktop? What would I call a second edition of my Power Query book, if I wrote one, given that Power Query exists only as the relatively nondescript “Get & Transform” in Excel 2016 and “Get Data” in Power BI Desktop?
I’m still not in love with the name change for Power Query. My concern still stems around the fact that this tool, while integrated with Excel, has it’s own coding language, and therefore needs a name so that users can search for help. Searching for help with Excel is going to land you Excel’s formulas, not Power Queries. And “M” is hardly a discoverable term either. Re-brand fine, but give the tool a name like “Excel Queries” or something to distinguish it from MS Query and Excel. Without that it’s going to be a nightmare for a user to get help on the subject.
Sometimes I have to force myself to see the positive in changes like this…
True. Adding a space between Power Query was already bad enough from a search engine perspective …
I feel the pain about Power Query’s name change as well.
On the other hand it could open up eyes to the “transform”-part that is much undervalued so far in my eyes. I’d very much like to see a dedicated tab in excel for that in the future 🙂
These are great observations Chris. You have done an amazing job staying ahead of the learning curve on Power Query and “M”. Perhaps you can call it “Excel 2016 Data Querying: Out with the old (Power Query) and in the with the new (Get Data)”
I think it’s a good change. Excel is Excel and Power BI is Power BI. Clear and simple. Yes ofcourse its a pity that a lot of info can’t be found anymore, but it’s only a matter of time before new stuff will be available. Looking forward I think its a good decision.
Frenk: To me, the fact that Excel is Excel and Power BI is Power BI is part of the problem. And Chris, I’d say that it’s not just the prefix that’s intimidating for regular Excel users, but the effectively new UI of that new functionality. They can change the name to “Fluffy Duck”, but that functionality is still going to be alien to 99% of Excel Users rather than a natural extension of Excel.
And right there is a big thorny issue: even with a name change, these tools represent a growing digitial divide between traditional Excel users and BI Jedi that Microsoft are not doing enough to bridge. A name change alone won’t bridge it.
Say I’m a fairly unsophisticated user of Excel by the standards of most readers of this blog. I can do VLOOKUPS, maybe the odd SUMPRODUCT, and I’m a real gun on creating and using PivotTables on data that lives in Excel. And say I want to mash together some identical tables that came from 20 different business units containing budget information, and serve it up as a PivotTable. I can’t do it. Even if I have this very sophisticated PowerStuff installed. Because this very sophisticated stuff isn’t exactly childsplay to use…even of the very minor thing I want to achieve: mash some data from within Excel into a PivotTable in Excel.
While the data model on the surface looks like an unsophisticated user could mash together different tables, in practice they can’t get the result they want with the easy-to-use Data Model alone, because there’s no UNION ALL type of relationship. Or say they don’t want to mash together identical templates, but instead they have some transactional sales data that had product ID, and quantity sold, but no price. And in another table they have ProductID and Price. And they need to work out how much money they made. Again, they can join these two tables using the Data Model, but they can’t use that to create a calculated column Price*Quantity. For that, they need to open the very sophisticated PowerQuery. But they’re not an overly sophisticated user. At the same time, this kind of thing isn’t an overly sophisticated thing to ask of Excel, and its something that users have to do all the time. Most of them probably do it manually. Or with hundreds of thousands of formula calls.
And here’s my point: your average user needs to be able to do no-brainer things in Excel in a no-brainer fashion.
Don’t get me wrong: I’m not asking that MS dumb down the tools or make the UI for these tools so easy to use that skilled practitioners find them nearly worthless. But I’m reminding you to keep an eye on what your less sophisticated users can do. The best outcome is to get them using these sophisticated new tools to meet their relatively unsophisticated requirements without them even realising that they are doing so.
How? A couple of simple options in the ribbon, for starters:
• Unpivot Data
• Pivot from multiple tables
• Consolidate Tables.
Yes, they can do this kind of stuff with PowerQuery. But PowerQuery is PowerScary. It is not something that the relatively unsophisticated user is likely to get to grips with. Ergo it’s not something that probably 95% of Excel users worldwide could easily come to grips with.
Over at the Office Blogs post https://blogs.office.com/2015/08/26/helping-business-analysts-take-full-advantage-of-excel-2016-and-the-new-power-bi/ they say:
I’d suggest that if you are one of the hundreds of millions of people who use Excel as their analysis and reporting tool today, you should find it easy to mash together simple data from within Excel, and serve it up within Excel. But I think it’s highly likely that you won’t find it easy to use the Power BI service and Power BI Desktop to do this.
We need much more than a name change. Again, the best outcome is to get a much larger percentage of the Excel User Base using these sophisticated new tools to meet their relatively unsophisticated requirements without them even realising that they are doing so.
There’s a marketing team in Redmond that has no idea how the product works or who uses it.
Initial PowerQuery and “M” language were a beautiful extension of Excel functionality and I think they should be definitely distinguished in Excel. I have created a planning solution using PowerQuery and it works as a kind of easy-to-use emdedded ETL tool and not necessarilly desined for users interference with the “M” code but rather the solution quickly developed in Excel file and shared with others for automatic data updates with predefined business logic.
I think there is ongoing discussion with defining where the distinction line lies between so-called “self-service” BI and professional BI environment. And more emphasis needs to be put on developing data-modellling skills for users than hiding the distinct and powerful functionality which actually implements a new concept of working with data in Excel.
Hi Chris, I’m a big fan of your books. A bit off topic, but is there any chance that a new edition of “The BISM Tabular Model” will come out that incorporates the SQL 2016 Tabular changes?
Nothing has been agreed, but I think there’s a very good of a new edition next year
The next name change could be Acquire & Chop, who knows? This will confuse the novice users trying to migrate from one version to another in Excel.
good thread,
trying to get a little bit more sophisticated here, but neither google nor bing or support.powerbi.com has any answers for this Power BI Desktop error message: “Can’t display the visual. See details”.
i know my finance users will have none of this if it’s not working in Excel.
thx, greg
PS Gartner seems to agree (by putting a lot more words about it): http://www.gartner.com/technology/reprints.do?id=1-2MI0JEC&ct=150903&st=sb
Hmm, hard to say – sounds like a bug. Maybe there’s something wrong with the Power BI installation?
My personal take:
1) It’s confusing to have two groups on the data tab to get data. If you’re a user that hasn’t been following the plot, which group do you use? Deprecate the Get External Data Group (hide in command well).
2) “Get & Transform” is a meaningless term. Get & Transform what? Should be renamed “Get & Transform Data”
3) Print books are useless for software that is changing monthly (Power BI, Power Query & Office 365 post 2016 release). The books are near obsolete before being printed. Only eBooks that can be regularly updated (and where new content can be added without having to worry about stupid page limits) make sense.
4) Most likely, Power View will go away and the functionality will be built into Excel Pivot Charts – where it belongs.
5) Only man left standing will be Power Pivot, and already most of the functionality on this tab appears elsewhere. Think about it. So far, in Excel 2016 we no longer need to use the Power Pivot tab to manage the data model, detect relationships (this last one since Excel 2013), or create a measure. It’s easy to move the remaining commands on the tab elsewhere. Instead of Power Pivot (which is confusing because people still refer to the data model as Power Pivot), simply rename the Power Pivot for Excel dialog box to Data Model Manager, and in the task pane, retitle from Power Pivot Fields to Data Model Fields.
6) The “Get External Data” group should disappear from the data model manager Home tab.
All good points Colin. To go off on a slight tangent about #3: certainly that was the problem my Power Query book had, but I’m not sure continuously updated ebooks are viable. Writing a book takes a long time and you get no direct financial reward from it; updating/extending an existing book is also a lot more effort than you would expect. Historically, tech authors (like me) would invest a couple of months writing a book, and then once they were done put it to one side and make their money from the consultancy and training work that the book brought in. If I had to spend my time rewriting a book every month, even after all the up-front effort of writing it, I don’t think that would leave enough time to be able to capitalise on it; the same goes for any other highly-structured content like video training. So my feeling is that one of the downsides of regularly-updated software is that there will be fewer books written overall, and all we’ll have is shorter pieces of content like blog posts.
Chris, I would agree with your points, having authored the Excel Quick Reference for Dummies book over several Excel versions. Even a small book like that takes a lot of effort to do right (for little financial gain). And it’s not just adding new stuff. If there are UI changes (which occurs in every version of Office), you have to review the entire book to make such screen captures and procedures for accomplishing a task are up to date.
I was describing an idealized situation rather a practical one in point (3) I suppose. Your last sentence is quite telling – I can hardly disagree. One key advantage of books is that topics are organized into logical sections (chapters). I suppose that one scenario is to use blogs posts as a fill in between book editions. However, even blog post are subject to obsolescence, after sufficient time passage and frequent software updating!
Perhaps we could go back to calling it Microsoft Mashup. 🙂
Either that, or you could change your title from “Power Query for Power BI and Excel” to “Get Data for Power BI and Get & Transform for Excel”.