Count number of times a value appears in multiple columns

[Tim123]

I'm new to Power Query, so was hoping to get some help with the following:

Say I have a table "Snacks" that records which snack my children eat each day:

Date	Alice	Brandon	Charlie
Jan-1	Apple	Banana	Pear
Jan-2	Apple	Banana	Pear
Jan-3	Pear	Apple	Pear
Jan-4	Apple	Apple	Banana
Jan-5	Pear	Banana	Banana
Jan-6	Apple	Banana	Banana

I would like to know how many times my children have eaten each type of snack:

	Alice	Brandon	Charlie
Apple	4	2	0
Banana	0	4	3
Pear	2	0	3

Any suggestions on how to do this with Power Query?

Note that the list of snacks isn't fixed (e.g. Alice might decide to eat Grapes on Jan-7).

[jgeddes · Accepted solution]

Try this...

Select 'Date' and unpivot other columns

Group Rows by Snack and Name, counting the rows.

Pivot the Name column

Sample code...

let
    Source = Table,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Date"}, "Name", "Snack"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Snack", "Name"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Name]), "Name", "Count", List.Sum)
in
    #"Pivoted Column"

[Tim123]

Exactly what I was looking for, thanks!