Background Think back to the year 2012 when Microsoft introduced us to SQL Server Analysis Services Tabular models. It was new and exciting. The Excel gurus who had been using PowerPivot since 2010 were sneering at us like we were…
I’m going to keep today’s post short and sweet. The #1 missing feature in Power BI is overwhelmingly clear to me, #2 isn’t close, and addressing this should be quite simple for my friends and former colleagues at Microsoft. “Help…
All I Did Was Import my Excel 2016 Power Pivot Workbook (Then Create the Chart) Just Checking… In last week’s post, I emphasized that I was using a new DAX function, PRODUCTX, that doesn’t appear in Power Pivot 2010 or…
By Avi Singh [Twitter] Friends, it was a joy again to host our free monthly webinar Excel to Power BI. As usual, we spent more than an hour on a lengthy Q&A session after the presentation. You can find the…
Post by Rob Collie
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.
Brings in Data Sources, DAX, Relationships, Queries, Power View…
Guest Post by Andrew Todd
Before this (and still for the users of Excel+PowerPivot), you could force a relationship filter to flow “uphill” and implement many-to-many relationships. However that was done inside your DAX measures (click to read a detailed how-to). Now, with Power BI Desktop, that can be done automatically via bi-directional relationships. Andrew shows us how, using some Dynamics CRM data…
Note: Download the example .pbix at the bottom of this this post, which includes the above dashboard, example bi-directional relationships setup and showcasing other new features in Power BI Desktop
Lookup tables in PowerPivot are like reservoirs holding torrents of instructions poised to break free at the click of a slicer tile. When a user clicks on a slicer connected to a lookup table, they open a flood gate and instructions are unleashed to flow downhill to data tables.
In Power BI Desktop, filters can defy gravity! Not only can filters flow downhill from the reservoir into the data tables… they can also flow uphill from data tables to lookup tables! Those instructions flowing uphill into the lookup tables can then spill over to data tables on the other side of the lookup table!
Driving Sales Activity Metrics from the Back Seat
With filters flowing uphill, the filters from the data table side of the relationship can be sent back to the lookup table. In a sense – those filters can actually flow right through the lookup table and down to data tables on the other side! The lookup table itself is filtered and the context ‘splashes’ over to the other data tables.
Post by Rob Collie
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?
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.
By Avi Singh [Twitter]
In our last webinar the #1 question and confusion seemed to be around Excel versus Power BI Desktop (formerly called Power BI Designer). The thing is, both tools are really part of an ecosystem. And the biggest confusion was around understanding how these tools fit into the overall landscape.
Here is the picture we would build to, but do read through so you understand how we arrived here and also find out how you control the future direction of Power BI (seriously, you do!).
Standalone Tool for End-to-End BI
Rob did an excellent head to head comparison of Power BI Designer versus Excel. Just read that, but I’ll add some commentary.
If you were to use a single tool for BI – getting data, building a data model and building charts/reports – we have firmly established that Excel is the world’s best data tool, period and that is primarily due to it’s internal network effect and that it is easy to adopt.
Excel is the World’s Best Data Tool, Period
Power BI Desktop is a very slick tool, with all different facets meshed together beautifully without any seams showing. Unlike Excel, where due to the “Add-In” nature Power* tools always feel a little clunky, not to mention cases where they go totally awry. But in spite of all that, it is hard, no impossible, to build a tool that can supersede Excel, or replace Excel.
However that is not quite a fair comparison. Because Power BI Desktop is primarily built for PowerBI.com.
Power BI Desktop for PowerBI.com
Power BI Desktop is primarily an accompaniment to PowerBI.com. It is essentially an “authoring tool” for PowerBI.com. Its true purpose is to build something that gets published to PowerBI.com.
You would almost never use Power BI Desktop standalone. Well you may, but understand that it is not really built for that purpose.
With these two combined – Power BI Desktop and PowerBI.com – they outshine Excel in the BI department. With the fluid development environment of Power BI desktop for authors…and the rich, easy to share, mobile ready visualization platform of PowerBI.com.
However that is not a fair comparison either, since Excel models can be published as well.
Post by Rob Collie
This is Power BI Designer, a New Product from Microsoft, and It’s Relevant to ALL of Us.
Major Seismic Activity out of “Mount Redmond”
Some of you may have seen it already, but just in case you haven’t: in recent months, Microsoft has been touting a preview of what it calls Power BI Designer. You can download it here for free, and install it right next to all of your other tools, TODAY.
I’ve held off, a LONG time, in writing about Designer. Because I wasn’t yet sure what to make of it. I’ve been wary of it, critical of its existence. It’s taken many, MANY off-the-record conversations with my former colleagues at Microsoft, and a lot of reflection, but now I am ready to talk about it, and even endorse it – with a few caveats near the very end of this post.
My Start Menu is Getting Crowded with Data Tools:
Excel 2010, Excel 2013, Power BI Designer, and Power Update
Designer is Clearly… Familiar to Us
Once you install it, you immediately start noticing some similarities to things we know and love:
Same Basic List of Data Sources We See in Power Query
And then the ribbon has some old friends for us as well…