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

[k1s2]

How do I create a DAX formula for a caculated column that counts the number of times the cell value = "High" across column names ending with the text "Rating"?

(also seeking a Power Query solution in that subforum:https://community.fabric.microsoft.com/t5/Power-Query/Count-number-of-times-specific-value-appears-i...)

[k1s2]

Thanks for your reply, 

I tried unpivoting, all except the unique ID column, then filtered 'Attribute' for column names ending in "Rating", then filtered 'Values' for "High", then grouped by unique ID and got a new 'Count' column...

...but lost all the original columns. How do I get them back along with the new Count column? 

[Greg_Deckler]

@k1s2 I was talking about a DAX solution. You unpivot and then write the appropriate DAX.

[k1s2]

After I unpivot in Power Query, what would be the way to count using DAX?  

I have ~400 columns, of which 114 end in the word "Rating", the values of which sometimes contain the word "High".  I'm looking to avoid building a DAX measure that has to names each of the columns individually.

[Greg_Deckler]

@k1s2 Generally you would unpivot those columns. Or, you could use MC Aggregations: Multi-Column Aggregations (MC Aggregations) - Microsoft Fabric Community

Otherwise, in Power Query, I would do what you did in terms of transformation as a custom function and then use that function in a custom column.