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

35b36f3f 286a-45a9-8a58-cab1b47064d4 on 06 Jul 2020 00:11:04

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

What I am finding difficult to understand when on the data view on power BI I can copy table and paste into excel. Is there no way instead of copy and paste I could just export the data to excel ? I am using power bi desktop

Avi Singh's profile image Profile Picture

8175331a 3c5d-4d74-b43c-82e26abe741e on 05 Jul 2020 23:34:11

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

Would love this feature from PowerBI desktop

Avi Singh's profile image Profile Picture

79bdc3b3 cbf8-49bf-8bf1-0efa7a7acac4 on 05 Jul 2020 23:20:15

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

Can this be added to Power BI Desktop?!?

Avi Singh's profile image Profile Picture

348df01b 753c-4e73-88df-47a9deb3f535 on 05 Jul 2020 22:44:07

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

Hi, we use analysis services, cubes and power bi without office 365. What we would like to be able to do (which almost seems possible) is to create a report on excel, upload that to power bi and still have the data refresh and connected to the cube (in effect just display the excel report in power bi)

Avi Singh's profile image Profile Picture

eb7eab43 c03e-433c-b4a3-7f8838eaa45c on 05 Jul 2020 22:42:43

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

Will, it seems like the Analyze in Excel ODC option connects to a different cube as it is only showing one row per work item (corresponding to the latest revision of the work item). If you create a report in PowerBI you get several rows for each work item (each row corresponding to a different work item revision).

Avi Singh's profile image Profile Picture

3703fe8a 53a5-4dab-8bfd-f69b907ebf6f on 05 Jul 2020 22:39:12

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

Sorry but it's only a very limited feature so far : the PBI model does not export to Excel (ie in an PowerPivot model) so it cannot be mixed in Excel with an external souce, and data connexion is not working for values (it only allows lines & column display ... which is very limited)
Is it planned to be improved soon ?
Thanks

Avi Singh's profile image Profile Picture

c07de935 6844-449a-83bc-4d0e0dd094f8 on 05 Jul 2020 22:37:01

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

hasn't this idea been considered?
What alternatives do we have...

Avi Singh's profile image Profile Picture

dfe2a078 a52a-4526-b51a-acc5fb74e437 on 05 Jul 2020 22:34:19

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

Hi. It still won't let me refresh Pivot Table report that has a live connection to OLAP Multidimensional Cube in Power BI. It says, that the refresh is not supported. Power BI reports are working fine with the same live conncetion.

Avi Singh's profile image Profile Picture

0d378a9d 4c90-4eff-98a9-0b1965b40375 on 05 Jul 2020 22:26:25

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

This is far to be usefull. After we download the ODC file, we cannot analyse in Excel because of the Error "The field that you are moving cannot be placed in that PivotTable area", that is said by Microsoft as "Error By Design".
We cannot use the aggregations in Excel, as it's only allowed to be put on Rows and Columns. If there is a workaround, please let me know.

Avi Singh's profile image Profile Picture

21df3308 5778-408e-8809-dfd1db3a6154 on 05 Jul 2020 22:25:15

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

Is it possible to connect without downloading the connection to your PC? Just the way you used to connect to a Power Pivot (Data > Other Sources > Analysis Services > URL of your data set).