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!

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

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,

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 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 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.

Connect Excel to a Power BI data model | Microsoft Fabric Community

14 Replies

Anonymous
Not applicable
8 years ago
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
  Impactful Individual
  8 years ago
  Hi Anonymous 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.
  - Anonymous
    Not applicable
    8 years ago
    webportal,
    
    I can't think of any other methods to achieve the requirement.
    
    Regards,
    Lydia
Anonymous
Not applicable
7 years ago
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
  Impactful Individual
  7 years ago
  Anonymous 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
    Regular Visitor
    6 years ago
    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/
Watsky
Solution Sage
6 years ago
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
Anonymous
Not applicable
6 years ago
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
  Impactful Individual
  6 years ago
  No issues here.
  You need the Power BI connector for Excel add-in.

Forum Discussion

Connect Excel to a Power BI data model

14 Replies

Related content

Power BI DeskTop Connecting to Excel

Fabric Warehouse Connection in Excel

Fabric Warehouse Connection to Excel

Connect Sharepoint excel to Power BI for autorefresh

Connecting Excel in SharePoint to Power BI