How to create a pivot table to represent categorical text?

Question

Hello,As seen in attached file (hopefully its attached)&lt;I have a table of 6 products showing the awareness of each product either as "aware", "not aware" or "blank".&nbsp;&nbsp;&nbsp;&nbsp;I am trying to make a pivot table as seen here&nbsp;Is there a way to do this as i can only seem to do it for one category at a time rather than all at once. Thanks!- on Excel PC Version 2405

riny_van_eekelen · Answer

james231660&nbsp;First of all you need to get rid of the empty rows in your data range. Then you need to unpivot (flatten) the data so that you end up with one column for Product and one for all the 'scores'. Then you can make a pivot table from that data set. The first two steps can be done in Power Query as demonstrated in the attached file.

peterbartholomew1 · Answer

I recognise that&nbsp;james231660&nbsp;'s query requires a pivot table solution.&nbsp; Despite that, I chose to examine the problem with a view to using an Excel 365 formula.&nbsp; I could follow&nbsp;Riny_van_Eekelen&nbsp; and flatten the data (TOCOL) before pivoting it (PIVOTBY).&nbsp; However, because array formulas can often work directly with data held as a cross-tab, I reverted to COUNTIFS to perform the conditional aggregation.&nbsp;&nbsp;= LET(
    COUNTIFλ, LAMBDA(response, LAMBDA(product, COUNTIFS(product, response))),
    aware,    BYCOL(Table1, COUNTIFλ("Aware")),
    unaware,  BYCOL(Table1, COUNTIFλ("Not Aware")),
    TRANSPOSE(VSTACK(Table1[#Headers], aware, unaware))
  )&nbsp;I chose to define the Curried Lambda function,&nbsp;COUNTIFλ, rather than using separate LAMBDA/COUNTIFS for 'aware' and 'unaware'.&nbsp;&nbsp;

Forum Discussion

How to create a pivot table to represent categorical text?

Share

Resources