You can normalize your data by bringing it into Power Query and then unpivoting the data
Here is the Mcode to make that happen
Code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Item", "Day:"}, "Attribute", "Value")
in
#"Unpivoted Other Columns"
If you want to take it to the next step and do the calculation in Power query then
Code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Item", "Day:"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "Red")),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Item", "Attribute"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows"