cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ronnoc
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
Brian_M
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
Brian_M
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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

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.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.