Count the numbers of columns that are not null

[Mat40220]

Helo developers,

I would like create new column 'Result' for have the same output like my exemple.

Count the numbers of columns are not null.

Exemple :

Column 1	Column 2	Column 3	RESULT
2	null	null	1
null	6	7	2
null	null	null	0
4	7	10	3

Thanks for your returns ! 

[WayneC]

Is there any reason why this would return an answer of 1 when all columns counted are null ?

example: (foreman.1.2 is column 8 when referencing the formula below):

results:

formula:

"Count_Foreman", each List.NonNullCount(List.Range(Record.FieldValues(_),8,4)))

This returns the correct number for all "NonNull" counts, but when all four columns are null, it returns "1" every time.

[Mat40220]

Thank you so much @marcelsmaglhaes, your are the boss !

Have a nice day !

[AnkitKukreja]

Hi @Mat40220 

You can add a custom column in the Power Query editor and use the below formula.

List.NonNullCount({[Column 1],[Column 2],[Column 3]})

[marcelsmaglhaes]

Hey @Mat40220 ,

Without think too much, I believe you can solve using this measure:

RESULT =

VAR __COUNT1 =  COUNTBLANK('Table'[Column1])

VAR __COUNT2 = COUNTBLANK('Table'[Column2])

VAR __COUNT3 = COUNTBLANK('Table'[Column3])

RETURN

3 - (__COUNT1 + __COUNT2 + __COUNT3)

Try that and tell me if it works.

Best Regards, 
Marcel

[Mat40220]

Thank Marcel,

But i want write the solution in power query (with M language)

Do you have an idea ?

[marcelsmaglhaes · Accepted solution]

Hey @Mat40220 , 

You can use the List.NonNullCount how the @AnkitKukreja said.

The code is 
#"Added Custom" = Table.AddColumn(YOUR_PREVIEW_STEP, "RESULT", each List.NonNullCount({[Column1],[Column2],[Column3]}))

Best Regards,
Marcel