Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 !
Solved! Go to 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
Regards,
Marcel Magalhães
Microsoft Power BI Official Partner
MCT | Certified PL-300 Power BI
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.
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]})
Hey @Mat40220 ,
Without think too much, I believe you can solve using this measure:
Regards,
Marcel Magalhães
Microsoft Power BI Official Partner
MCT | Certified PL-300 Power BI
Thank Marcel,
But i want write the solution in power query (with M language)
Do you have an idea ?
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
Regards,
Marcel Magalhães
Microsoft Power BI Official Partner
MCT | Certified PL-300 Power BI
User | Count |
---|---|
134 | |
68 | |
68 | |
54 | |
52 |
User | Count |
---|---|
207 | |
95 | |
64 | |
61 | |
57 |