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

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

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

Please allow users to connect to the PBIX file stored on a local drive or shared folder - Just allowing users to connect to Power BI.com has limited uses

Avi Singh's profile image Profile Picture

65a98445 53c8-4fb0-bafa-0fa3942a14e4 on 05 Jul 2020 22:18:15

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

I downloaded and installed the 32-bit driver as prompted, but I'm getting the following error when I try to open the ODC file: http://imgur.com/D1eFZcY

Avi Singh's profile image Profile Picture

a1e8f7ba b28f-49aa-ac1b-10e02fb3c0a2 on 05 Jul 2020 22:18:13

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

Remember to download the correct driver according to your system - it defaults to 32 bit / the 64 bit is located in the feature pack:
https://www.microsoft.com/en-us/download/details.aspx?id=51613

Avi Singh's profile image Profile Picture

90118097 7fae-42de-ba9c-83b83d862b93 on 05 Jul 2020 22:18:12

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

Just to echo what a few people have already said, this is a useful addition but only help those people working in the cloud. What we would like to see next is a way to connect via Excel to an on prem pbix file.

Avi Singh's profile image Profile Picture

028b0aad b0c4-48e0-8ce4-514ade883697 on 05 Jul 2020 22:18:10

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

What version(s) of Excel?

Avi Singh's profile image Profile Picture

4e2ed990 e62a-4956-94cf-e2ae7078a9e9 on 05 Jul 2020 22:18:10

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

Getting a connection to the cloud data is a great start but it would also be good if Excel could connect to a By connecting Excel directly to a .pbix file (like on your C: Drive) so that you have an instant connection to on premises data.

Avi Singh's profile image Profile Picture

b39cb521 94b5-48f8-81ff-9cf730d3b8f7 on 05 Jul 2020 22:18:10

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

I think it's important that Excel should be able to connect to the on-prem analysis services when hosted in PowerBI.com, not just the PowerBI data model

Avi Singh's profile image Profile Picture

ff1a350d b114-43dd-a9bc-52605ffc5043 on 05 Jul 2020 22:18:10

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

this does not appear to be working for myself and several other users. Any guidance on failure to open datasource?

Avi Singh's profile image Profile Picture

f1ab0820 263c-e811-a959-000d3a1be90a on 05 Jul 2020 22:18:10

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

With this feature we can now have an enterprise conversation with our customers. PowerBI lacked the fundamental reporting capabilities that ervy customer requires. The current tables / pivot tables just don't cut it but now we can bring Excel into the conversation and keep the data governance teams happy as Excel is connecting to a centrally managed model. If we could close the loop by publishing live Excel reports back to PowerBI.com then we have a complet story. i realize that this is possible with Excel 2016 but it would be great if this also worked with earlier versions of Excel. Overall great job and i look forward to seeing the Microsoft BI story continuing to mature.

Avi Singh's profile image Profile Picture

90118097 7fae-42de-ba9c-83b83d862b93 on 05 Jul 2020 22:17:52

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

This is exactly what's needed to drive wider adoption of Power BI