Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
heidibb
Helper IV
Helper IV

Count non-blank cells across a range of columns

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?Capture.JPG

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

@heidibb

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

9 REPLIES 9
OwenAuger
Super User
Super User

@alicek 

I would suggest something like this in place of the above code, using List.Select to select the empty strings:

=List.Count(List.Select(Record.FieldValues(_), each _="" ) )

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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 !

OwenAuger
Super User
Super User

@heidibb

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

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?

@jtolmen 

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

@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. 

@alicek 

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?


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors