Skip to main content

Power BI

Completed

Ability to connect Excel to Power BI Data Model and create Pivot/Charts

Vote (942) Share
Avi Singh's profile image

Avi Singh on 23 Jul 2015 08:43:23

Just the way we are able to upload a Power Pivot Model to SharePoint and then connect an Excel workbook to the model hosted on SharePoint (See http://www.powerpivotpro.com/wp-content/uploads/2015/07/SharePoint_PowerPivot_ToExcel.png) ...
...In a similar manner we should be able to connect Excel to a Data Model hosted on PowerBI.com.

Bonus Points: If users can also connect to a Power BI Desktop file (.pbix) using Excel. This would perhaps more for testing or ad-hoc use. Similar to how when building an SSAS Tabular Model in Visual Studio, you can click "Analyze in Excel" to open an Excel window connected to the model you are working on.


As an Excel user, I would love to use the cutting edge Data Modeling capabilities in Power BI Desktop (bi-directional relationships and new functionality rolled out each month).

However, while the new Power BI offers great visualization options, Excel gridheads like me, just NEED to be able to create Excel Pivots off of the Power BI Data Model.

If this could be enabled for Power BI, that would be very useful.
- Either by connecting to a Power BI Model (.pbix) hosted on PowerBI.com
(This would be similar to how I can create Excel Pivots connected to a PowerPivot Model uploaded to SharePoint)
AND/OR
- By connecting Excel directly to a .pbix file (like on your C: Drive)

Without this functionality Power BI feels like a **One Way Journey**. Once I go from Excel/PowerPivot to Power BI Desktop, the only visualization options are those on Power BI.

I may be looking at it from the wrong lens (still warming up to SaaS model of BI), but in my mind the "Data Model" should be agnostic of the visualization layer/tool. After building my data model, I should be able to connect any viz tool of my choice (pretty much). And that model already is in place for Power Pivot - by hostin your model on SharePoint or on SSAS Tabular server you can pretty much use the viz tool of your choice - Excel Charts/Pivots, Power View (within Excel or SharePoint), SSRS and other Microsoft and Non-Microsoft Viz tools.

Whereas with Power BI, I feel like I would be locked in to the visualization experience on PowerBI.com. The whole visuals project (https://www.github.com/Microsoft/PowerBI-visuals) is great but not for everybody. Plus, if I am using a specific viz tool, I want to be able to continue using that. For many users that tool is Excel :-)

Administrator on 28 Mar 2016 22:14:44

I'm very pleased to say this has been completed! Click the ... menu on a dataset in Power BI's navigation pane and you'll see an option to 'Analyze in Excel' which will open an ODC file that will build a Pivot Table in Excel. There are many ways we can keep improving this feature so please create more suggestions on the topic! Thanks!

Comments (62)
Avi Singh's profile image Profile Picture

93d9381c e3c0-4407-be25-3166ecc65b75 on 05 Jul 2020 22:15:32

RE: Ability to connect Excel to Power BI Data Model and create Pivot/Charts

Yess!
Please Microsoft, hear us!!!!

Avi Singh's profile image Profile Picture

1de3ef84 645b-40dd-8545-3d1367df9acc on 05 Jul 2020 22:14:42

RE: Ability to connect Excel to Power BI Data Model and create Pivot/Charts

Absolutely FANTASTIC - Power BI Will finally take OFF-
Guys can you also give a way of encrypting the PBIX file with a Password - After all its just a CUBE so should not be very difficult

Avi Singh's profile image Profile Picture

cfff4322 26af-44ef-9a97-e2c2c504e99c on 05 Jul 2020 22:14:22

RE: Ability to connect Excel to Power BI Data Model and create Pivot/Charts

Oh thank god that they are starting this.

Without the ability to tap the data model from Excel, Power BI desktop is hard to justify vs. just using Power Pivot.

So why not just use Power Pivot and be done with it? Mainly because of the 64 Bit issue. As a corporate user, I only have access to 32 bit excel and have had, ahem, "performance issues." My understanding is that 64 Bit Power BI desktop will alleviate some of this.

Please, to the Power BI powers that be, have this be such that one can tap the Power BI model just as one would the Power Pivot model, complete with cube formulas, etc.

Avi Singh's profile image Profile Picture

a020250b afab-450a-ac4c-a581aa5b3418 on 05 Jul 2020 22:13:37

RE: Ability to connect Excel to Power BI Data Model and create Pivot/Charts

Power Query (in Excel and PBI Desktop) should be able to connect to the data model as another type of source.

Avi Singh's profile image Profile Picture

a232515e c220-4aca-8d88-dd3850fb8edd on 05 Jul 2020 22:13:20

RE: Ability to connect Excel to Power BI Data Model and create Pivot/Charts

I think I know why this not happening any time soon, it would kill Access completely

Avi Singh's profile image Profile Picture

37c9ec07 3db7-e711-8117-c4346bb5988c on 05 Jul 2020 22:12:56

RE: Ability to connect Excel to Power BI Data Model and create Pivot/Charts

Agreed, a show stopper for many customers. Even a simple "Export To Excel" button on a visualisation would allow us to get what we need. Why does almost every application in the world have an Export to Excel button except a major MS BI offering ;)

Avi Singh's profile image Profile Picture

cfff4322 26af-44ef-9a97-e2c2c504e99c on 05 Jul 2020 22:12:44

RE: Ability to connect Excel to Power BI Data Model and create Pivot/Charts

Why is this one of the top ten 'ideas' yet not flagged as under review, planned, or started?

Avi Singh's profile image Profile Picture

5c3fb84f 9065-40fb-8b09-1227db152771 on 05 Jul 2020 22:10:57

RE: Ability to connect Excel to Power BI Data Model and create Pivot/Charts

I wish there were a way I could use all 20 of my UserVoice points to vote for this feature

Avi Singh's profile image Profile Picture

484cf794 ac94-4c33-95f5-c77281b29f35 on 05 Jul 2020 22:10:30

RE: Ability to connect Excel to Power BI Data Model and create Pivot/Charts

Totally agree. Upvoted +3

Avi Singh's profile image Profile Picture

77734b04 a87c-4629-a82e-8cf557b0d85f on 05 Jul 2020 22:10:12

RE: Ability to connect Excel to Power BI Data Model and create Pivot/Charts

I did a serach for this capability today and found this thread. I agree its an essential feature. I have been using PowerBI desktop lately and have often felt constrained by the fact that the flexibility of Excel was not available to me. Jo Lima - you said there was an unsupported way - please let us know how. Thanks.