PQ - Count word in record

Count word in record

Source
Colour1	Colour2	Colour3
red	red	blue
blue	red	green

Count "red"

Colour1	Colour2	Colour3	CountRed
red	red	blue	2
blue	red	green	1

Code:

---

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    addRedCount = Table.AddColumn(Source, "CountRed", each List.Count(List.Select(Record.FieldValues(_), each _ = "red")))
in
    addRedCount

---

Count "red" in last 2 columns

Colour1	Colour2	Colour3	CountRed
red	red	blue	1
blue	red	green	1

Code:

---

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    CRed = Table.AddColumn(Source, "CountRed", each List.Count(List.Select(List.LastN(Record.FieldValues(_),2), each _ ="red")))
in
    CRed

---