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

a3309361 99be-ea11-a812-000d3a8ddfb2 on 05 Jul 2020 22:23:59

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

It's easy for Microsoft to suppor to excel connect to local files *.pbix. --Change the value of the listenOnlyOnLocal property of msmdsrv.exe to "false".It all depends on whether Microsoft would

Avi Singh's profile image Profile Picture

a3309361 99be-ea11-a812-000d3a8ddfb2 on 05 Jul 2020 22:23:59

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

https://blog.oraylis.de/2016/02/connecto-pivot-to-power-bi-desktop/

Avi Singh's profile image Profile Picture

1a8f23c0 48da-46b5-b023-4c290fcae5c0 on 05 Jul 2020 22:23:48

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

Hi Will,

I tried Analyze in Excel with SSAS On-Premises and it is not working. I am getting error Unsupported Dataset.

Thank you

Avi Singh's profile image Profile Picture

cd4f2ca5 2c79-4fdb-876e-de66a5e01c69 on 05 Jul 2020 22:22:47

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

How about being able to mix and match pivot tables and other powerbi visuals in the same report or dashboard?

Avi Singh's profile image Profile Picture

000e72de 4925-4ee0-a684-f106bf6b2956 on 05 Jul 2020 22:22:43

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

Would it be possible to do this with local files *.pbix ?

Avi Singh's profile image Profile Picture

d5cc988e 47bd-45dd-a1c0-b60d413d1022 on 05 Jul 2020 22:21:58

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

when i try to add some field to values in the pivot table

a message appears: the field you are moving cannot be placed in that area of the report"

i verified and is a numeric field everithing is all right i dont know why

please help

this feature is really usefull

Avi Singh's profile image Profile Picture

8022f40d bf87-4f49-9e32-fdba347f1678 on 05 Jul 2020 22:21:38

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

When will excel connect to an on premises *.pbix file instead of using the cloud?

Avi Singh's profile image Profile Picture

24d1dacc 3327-408d-a4f2-a576791414af on 05 Jul 2020 22:18:59

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

Thanks for this!
I think this a mayor improve, at least for the company that I work.
This feature really transforms Power BI from a dashboard report tool to a SSAS Cube on cloud.

Avi Singh's profile image Profile Picture

de396671 5db3-4348-abdc-666a53b976cc on 05 Jul 2020 22:18:27

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

Thanks for adding this much needed feature. It would be great if we could publish the resulting Excel report to One Drive so that we can surface it in Power BI as an interactive (i.e. slicers/filters) Excel report. Currently when I try this the refresh does not work. Given that this is fully web based I would have thought this was possible without any connectors?

Avi Singh's profile image Profile Picture

adbd91d0 2200-48da-86c0-c47b2024e1d2 on 05 Jul 2020 22:18:23

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

Just "legalize" connection to .pbix file without DAX studio. Do we need extra topic?