cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate II
Advocate II

Recreate excel pivot table in powerBI

I am new to powerBI and confused about the process of recreating pivot table data from excel in desktop powerBI.

 

here is the table as it lies in excel:

tabletorecreate.PNG

 

It seems to me from expirimentation and time spent browsing these forums that the only two possible outcomes in recreating such a table in desktop powerBI are as follows:

 

1. table style - clunky and completely unflexible format

tableformbi.PNG

 

2. Matrix style - no expandable/collapsible features for rows, not a compact form by any means and so many unnecessary totals that can be turned on/off but not customised in any way. Additionally I am unable to set %media and %agency to be row values like in the original excel pivot table.

matrixformbi.PNG

 

If there is something vital about powerBI I am missing I would appreciate a pointer in the direction of some informational references. I find it hard to believe that the functionality for powerBI could be so limited when it is based on the technology of powerpivot and excel which are (and have always been) very capable of performing these simple tasks and format flexibility??

 

All responses are appreciated. Cheers

1 ACCEPTED SOLUTION
Responsive Resident
Responsive Resident

Welcome!

 

Currently Power BI doesn't 'do' pivot tables.  The idea is likely to be coming soon as the idea here is marked as started 15 October 2016.  

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/6881220-integrate-powerbi-with-pi...

 

If you definitely need pivot tables you have a couple of options.  The first is to do your model in Power BI Desktop, publish to the cloud and then click the 'Analyse in Excel' (you might need to install a plug in) 

 

https://powerbi.microsoft.com/en-us/documentation/powerbi-service-analyze-in-excel/

 

The other is to pin an excel file directly in Power BI.

https://powerbi.microsoft.com/en-us/documentation/powerbi-publisher-for-excel/

 

I would add your vote to the pivot table idea above, and you'll receive notification when it is completed. You can expect features that are started often roll out within a month or two.  I've also found that features like this are released early and 'partially complete', so it may not do everything you expect from excel pivot tables right out of the gate. They generally improve over time.

 

You will find that Power BI is missing some features and so it won't yet be right for all of your business requirements, but it will excel in others. It can be a bit of a challenge figuring out in which scenarios it fits, especially as it is improving so rapidly.

 

 

Hope that helps!

View solution in original post

1 REPLY 1
Responsive Resident
Responsive Resident

Welcome!

 

Currently Power BI doesn't 'do' pivot tables.  The idea is likely to be coming soon as the idea here is marked as started 15 October 2016.  

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/6881220-integrate-powerbi-with-pi...

 

If you definitely need pivot tables you have a couple of options.  The first is to do your model in Power BI Desktop, publish to the cloud and then click the 'Analyse in Excel' (you might need to install a plug in) 

 

https://powerbi.microsoft.com/en-us/documentation/powerbi-service-analyze-in-excel/

 

The other is to pin an excel file directly in Power BI.

https://powerbi.microsoft.com/en-us/documentation/powerbi-publisher-for-excel/

 

I would add your vote to the pivot table idea above, and you'll receive notification when it is completed. You can expect features that are started often roll out within a month or two.  I've also found that features like this are released early and 'partially complete', so it may not do everything you expect from excel pivot tables right out of the gate. They generally improve over time.

 

You will find that Power BI is missing some features and so it won't yet be right for all of your business requirements, but it will excel in others. It can be a bit of a challenge figuring out in which scenarios it fits, especially as it is improving so rapidly.

 

 

Hope that helps!

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors