PQ - Count word in record

[sandy666]

Count word in record


Source

Colour1Colour2Colour3

redredblue

blueredgreen


Count "red"

Colour1Colour2Colour3CountRed

redredblue
2

blueredgreen
1


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

Colour1Colour2Colour3CountRed

redredblue
1

blueredgreen
1


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