0

I've built a matrix with item names as the rows and item ingredients as columns. The ingredients are a list of all ingredients across all items but not every item will contain all of the ingredients so there will be a lot of empty cells.

Example:

Ingredient1 Ingredient2 Ingredient3 Ingredient4
Item1 3 2
Item2 2 2 3
Item3 2 2
Item4 3

If possible I'd like to build out a pivot table using nested rows which would be the column names and how many of each, excluding null cells.

Desired output:

Item1
  Ingredient1   3
  Ingredient3   2
Item2
  Ingredient1   2
  Ingredient2   2
  Ingredient4   3
Item3
  Ingredient2   2
  Ingredient4   2
Item4
  Ingredient3   3

I can't find a way to make the column names add to the pivot as row labels. They always show up as the value of the field associated with the Item row. Is there an easier way to do this?

nodal
  • 1
  • 1
    In fact, this transformation is not called Pivot, but UnPivot. Try to do it with Power Query. Select From table on the Data tab, select everything and run the UnpivotOtherColumns command (just select the desired command from the context menu) – JohnSUN Jan 20 '22 at 08:35
  • Perfect. Thank you both! – nodal Jan 21 '22 at 06:25

0 Answers0