Power Query - count text in 4 columns

[rsj88]

Hi,

i have the below dataset of 3 columns i need the 4th column (custom colum) to count the how many times "red" appears in total.

I tried List.Cotainsall but this is just true and false.

Colour1	Colour2	Colour3	Custom Column
red	red	blue	2
blue	red	green	1

Thanks again

 

[anvg]

Hello.
Variant

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

Regards,

 

[rsj88]

Hi

Thanks this code is perfect.

if i want to tweak this to only count columns Colour2 and Colour3 would this be possible?

Thanks

 

[sandy666]

you can try with Custom Column: =List.Count(List.Select(List.LastN(Record.FieldValues(_), 2), each _ = "red"))

 

[rsj88]

Bingo! Perfect thanks again!

 

[sandy666]

You are welcome