Power Query - count null in specified columns

[JugglerJAF]

I have some data (dummy data screenshot below) from which I want to count the number of null values, but only in the 4 "rating" columns, I'm not interested in nulls in any other columns.

I've worked out how to count the number of NotNull values across all columns, but that's not giving me what I need. This would be so simple in Excel! Why is it so complicated in Power Query?!?

Can anyone shed any light on this problem and advise how to calculate the number of null values across all 4 of the "rating" columns? The results (reading down) should be 4, 0, 0, 2, 3

 

[SullyWYO]

The formula for getting a true/false on a null cell is " = null". The formula will return a 0 (false) or a 1 (true).

In the Power Query editor, go to Add Column > Custom Column

For each column you want to test, use this format ([column name] = null) and add them all together using + sign.

For example, you might use: ([Sales] = null) + (RatingQ1 = null) + (RatingQ2 = null).............etc. Each one that has a null value will return a 1, so the result will be the total columns with "null"

 

[JugglerJAF]

The formula for getting a true/false on a null cell is " = null". The formula will return a 0 (false) or a 1 (true).

In the Power Query editor, go to Add Column > Custom Column

For each column you want to test, use this format ([column name] = null) and add them all together using + sign.

For example, you might use: ([Sales] = null) + (RatingQ1 = null) + (RatingQ2 = null).............etc. Each one that has a null value will return a 1, so the result will be the total columns with "null"

Thanks for the info. I couldn't quite get into work as you stated, getting an error that + was not a valid operator to use with a TRUE/FALSE value, but I resolved it by converting TRUE to 1 with an IF command (might also work by multiplying the TRUE/FALSE by 1 to convert to a numeric value)