Forum Discussion

webportal's avatar
webportal
Icon for Impactful Individual rankImpactful Individual
8 years ago

Connect Excel to a Power BI data model

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...
Need Help
Anonymous's avatar
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's avatar
webportal
Icon for Impactful Individual rankImpactful 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's avatar
    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/
    • Lidou_Cand's avatar
      Lidou_Cand
      Icon for Helper III rankHelper III
      6 years ago

      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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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!