Forum Discussion

philbloggs's avatar
philbloggs
Copper Contributor
Nov 29, 2021
Solved

Pivot table issue

Apologies- this may be the wrong forum for this type of question but I'm tearing my hair out. The problem - I have a flat excel workbook that has within it data similar to the structure below:   ...
excel
  • Wyn Hopkins's avatar
    Wyn Hopkins
    Nov 29, 2021

    philbloggs

    Detlef_Lewin is rereferring to using Power Query to Unpivot the data first,  this then converts your multiple columns into a 2 columns as per the attached file.   This is then ideal for Pivot Table use.

    Click inside your data and turn it into a Table (using Ctrl T)  then go to Data - From Table/Range (or it may say from Sheet depending on your version of Excel)

    That opens Power Query

    Then Right CLick the first column and choose Unpivot Other then click Close and Load To... drop down and choose Data Model connection only 

     

     


     

Wyn Hopkins's avatar
Wyn Hopkins
MVP
Nov 29, 2021

philbloggs

Detlef_Lewin is rereferring to using Power Query to Unpivot the data first,  this then converts your multiple columns into a 2 columns as per the attached file.   This is then ideal for Pivot Table use.

Click inside your data and turn it into a Table (using Ctrl T)  then go to Data - From Table/Range (or it may say from Sheet depending on your version of Excel)

That opens Power Query

Then Right CLick the first column and choose Unpivot Other then click Close and Load To... drop down and choose Data Model connection only 

 

 


 

  • philbloggs's avatar
    philbloggs
    Copper Contributor
    Nov 30, 2021

    Wyn Hopkins  That's really helpful. Thanks for taking time out to answer such a rookie question.

     

    Phil

Resources