Count number of times specific value appears in each row across columns whose name contains

[k1s2]

How do I count the number of times the cell value = "High" across column names ending with the text "Rating"?

I think the column filter could be done with something like

List.Transform(List.Select(Table.ColumnNames(source), eachText.EndsWith(_,"Rating")))

...but no idea how to count across them.

[k1s2]

Thanks - that seems to work nicely.  Do you have time to explain how it works?

In particular, the (x) => is a mystery to me

[spinfuzer]

Take any custom column you have created before.

Say something like =[Column1]

You'll notice in your code you got something like each [Column1].  each is a function.

each [Column1] is the same as (_) => _[Column1].  It is called syntax sugar and it is a way to make code look cleaner where possible.  You will notice in any custom column you create, you can put an underscore in front of the colum names and you will get the same result (e.g. type _[Column1] and you'll get the same result as just [Column1]).

Alternatively, instead of each [Column1], you could type it as (a) => a[Column1] and you'd get the same result, but you must type a in front of Column1 in this case.  When you use each, the underscore is optional.

Since the each function has already been used and refers to the elements in the current row, you need to define a different function to refer to the elements in the current list if you a function parameter (e.g. List.Select's 2nd parameter is selector as function).

https://learn.microsoft.com/en-us/powerquery-m/list-select

[spinfuzer · Accepted solution]

You can add a custom column with a formula like below.

[ 
    a = List.Select(Record.FieldNames(_), (x) => Text.EndsWith(x, "Rating")),
    b = Record.SelectFields(_,a),
    c = List.Count(List.Select(Record.FieldValues(b), (x) => x = "High"))
][c]