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

 

Lidou_Cand's avatar
Lidou_Cand
Icon for Helper III rankHelper III
6 years ago

Hello Anonymous 

 

Thank you for your help.

That works well.

Is there a way to automate these actions ? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      webportal
      Icon for Impactful Individual rankImpactful Individual
      6 years ago

      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!

       

      😁