Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am trying to create a column that basically counts the number of columns for each row that are not blank. See image. The value i'm looking for would be 4 for the top few rows since ther are 4 columns that are not null.
Is there a way to do this in query editor?
Solved! Go to Solution.
Yes, if you click Custom Column on the ribbon, in the dialog box enter this in the formula section:
=List.NonNullCount(Record.FieldValues(_))
Regards,
Owen
Amazing, thank you @OwenAuger !!
I tried to combine what you sent above with the List.Range, because I need to count the number of blanks in the first five rows. So I tried various combinations:
List.Count(List.Range(Record.FieldValues(_), 1, 5, each _=""))
List.Count(List.Range(Record.FieldValues(_), each _="", 1, 5))
List.Count(List.Select(Record.FieldValues(_), 1, 5, each _=""))
List.Count(List.Select(Record.FieldValues(_), each _="", 1, 5))
None of them worked... do you think it's possible to do? Perhaps I need to nest a List.Select within a List.Range or vice versa?
Whoops, nevermind, got it! I had to nest them. Just in case this ever helps anyone else, here is what the final formula looked like:
List.Count(List.Range(List.Select(Record.FieldValues(_), each _=""), 1, 5))
Thanks @OwenAuger !
Dear
Thank you for sharing the custom column DAX for List.NonNullCount(Record.FieldValues(_)) is working but 178,232 row showing blank out of 672,746 Rows. i cross-checked the information available. i can not why its showing blank. can you please help me to solve this problem
Regards
Munish Bawa
What if I don't want to include every column in the count, what if I only want to count columns 2 - 8?
If you want to select columns by position, you could wrap List.Range around Record.FieldValues. For example, this would be the Custom Column Formula in the Custom Column dialog box:
=List.NonNullCount(List.Range(Record.FieldValues(_),1,7))
Regards
Owen
@jtolmenand @OwenAuger thanks for your help on this - it also helped me!
Instead of null, my cells are blank. Is there an equivalent M formula for that?
I can't make them null instead of blank, because I am also creating a custom column that concatenates their values. If I make any of the cells null, the whole cell becomes null for some reason.
If you are wanting to count nonempty strings, you could write something like this, using List.Select:
= List.Count( List.Select( Record.FieldValues(_), each _ <> "") )
Does that give you the expected result?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
83 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |