Connect Excel to a Power BI data model

[webportal]

Hello,

Is there any way to connect Excel to Power BI Desktop and importing the Data Model to Power Pivot?

With Power BI Publisher for Excel it is possible to connect Excel to Power BI Service and get a live connection, but the data is contained within a Pivot Table. I need to maintain a specific spreadsheet-like layout and it is complicated to create formulas linking to a Pivot Table.

Thanks for helping!

[Anonymous]

I'm interested to know about this as well.

Just the other week when I went to the get data tab there was an option to connect directly to my PBI model, however, today that option seems to have disappeared. 

Has any body else encountered this issue. 

[webportal]

No issues here.
You need the Power BI connector for Excel add-in.

[Watsky]

This doesn't work?

File -> Import -> Power Query, Power Pivot, Power View (In February Release) or

File -> Import -> Excel Workbook Contents?

https://youtu.be/hPmnyt_PeHk

[PhuongTN]

Hello,

@webportal  I'm looking for an answering to the same problem and wondering if you found a solution yet? I would greatly appreciate it if you can share your solution.

Thanks,

[webportal]

@PhuongTN  as far as I know there's no solution for this.

You may connect Excel to Power BI Service only and work from there.

Hope this helps.

[EpicLeo879]

This is key for me in leveraging the powerhouse dynamic duo that power BI and Excel create. While I am not aware of any drag and drop method all you need is the data connection (created when the pivot table loads to excel from PBI) and cube functions in your formulas to get to your data outside of the pivot table and pbix file. In all of my complex excel models / reports, I use this method to pull in all the data I need from my PBI models.
Cube functions can look intimidating at first but they are actually very intuitive once you write a few. And with the beauty of excel, you can write one and drag it down the row or column and have it populate the measure or metrics according to different cell variable (each date, product category, etc.) Also, the data connect to the PBI file keeps the excel model / report updated with every refresh.

This article introduced me to cubevalue formula ( way back when PBI was Power Pivot), it’s the function you will use most to pull your data out of your PBI and into excel. The post references PowerPivot, but it’s one in the same for PBI.
https://www.excelcampus.com/cubevalue-formulas/

[Lidou_Cand]

Hello

I have the same problem on my project.
From Excel, I have an extraction of a Power Bi report. However Excel obliges us to have this extraction in a pivot table.

I wish to have this extraction in a simple table.

How did you manage to get around this obstacle?

[PhuongTN]

@Lidou_Cand 

Hello there,

I was able to extract the data into a tabular format. Here's how you would do it:

>> Pull all of the fields into the Rows well

>> Keep the pivot table selected, to up to Design ribbon > Report Layout > Show in Tabular

>> Design ribbon again > Report Layout > Repeat All Items

>> Design ribbon again > Subtotals > Do Not Show Subtotals

>> Design ribbon again > Grandtotals > Off Rows and Columns

>> Design ribbon again > uncheck Row Headers

After those steps you should have a nice tabular formatted table of data. However, you will still see the collapse buttons on the cells. To get rid of these, I just copy and paste (values) onto another worksheet. 

I hope that helps!

[Lidou_Cand]

Hello @PhuongTN 

Thank you for your help.

That works well.

Is there a way to automate these actions ? 

[PhuongTN]

@Lidou_Cand 

Try using macros in Excel. That's the only way I can think of. I don't know too much about macros but I think simple steps like that can be done very easily.

[webportal]

Actually, I've used Power BI connector for Excel add-in with Excel CUBE functions and was able to manipulate the layout of the data in Excel without the Pivot table.

Not so difficult, after all!

😁

[v-yuezhe-msft]

@webportal,

Please follow the guide in the blog below to connect to Power BI Desktop data model from Excel.

http://biinsight.com/connect-to-power-bi-desktop-model-from-excel-and-ssms/

Regards,
Lydia

[webportal]

Hi @v-yuezhe-msft thank you for your help.

The post describes a method to connect Excel to PBI Desktop, but the data is available in a Pivot Table only, which is basically the same that Power BI Publsher for Excel Add-in does.

[v-yuezhe-msft]

@webportal,

I can't think of any other methods to achieve the requirement.

Regards,
Lydia