Recreate excel pivot table in powerBI

[ronnoc]

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:

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

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.

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

[Brian_M · Accepted solution]

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!