I wasn't sure if this question belonged in a Microsoft Office community or this Power BI community. I chose here. I really tried searching for an answer on Google, and while there were some close answers, nothing satisfied me.
I need to pick an Office package, and I'd like to include Exchange Online (Microsoft's alternative to Google Apps/Gsuite). I only see Office 365 Business Premium as an option. Unfortunately, it doesn't include the bi tools, like Power Pivot. Office 365 ProPlus does, but it doesn't offer Exchange Online. Now for my question.
Is Power Pivot, Query, View, and Maps needed when PowerBI Desktop is available for free? I can't figure out what I would get with the Power add-ins within Excel that I won't be able to get in Power BI Desktop.
(I know I could subscribe to Office 365 ProPlus and subscribe to Exchange Online, but then I'm paying $16 a month. This isn't for business, so I want to minimize my expenses.)
There are certain reasons you should use powerpivot.
1. PowerPivot models can be imported into the PowerBI Desktop and SSAS Tabular. So, You can prototype your model in PowerPivot and can import the model to SSAS Tabular without recreating any new calculations.
2. The second benefit is you can load your Query data to either Excel Worksheet or Datamodel. Two options.
3. PowerPivot has its own set of data connectors which you can also use to load data to data model.
4. You can prepare pivot table reports on top of your powerpivot model.
As of today Power BI desktop is not a replacement to Modern Excel (Power Pivot-Query-View-Map)
PowerBI Desktop has the following Limitations
1- Lack of printing
2 – pbix cannot be protected, if you share a report anyone can open it and modify it, basically there is no read only mode.
3- Export to Excel is limited to 30000 records ( there are woraround but still)
4- No pivot table, matrix visual is very limited.
5- Very basic charts capabilities, and lack of customizations
Item 4,5 will be fixed this year, I have no doubt about it, as Microsoft is allocating a lot of resources to close the gap with the competitors (tableau and qlik)
Item 1,2,3 ; are not technical limitations but commercial decisions, so we don’t know what will happen ?? Because potentially there is a risk to cannibalise other products (access, Excel)
But for me, Excel has one killer feature that will never be available in Power BI desktop: cube formula
There is a third way, but it is not supported by Microsoft, Use Power BI Desktop as a backend and Excel as a front end, you can see the detailed here