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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Lorenz33
Helper III
Helper III

Need to count columns in a row that are not null

I am working with this table in Power Query editor:

 

Vehicle ID

Vehicle 1

Vehicle 2

Vehicle 3

1

Toyota

Chevrolet

Honda

2

Ford

null

null

3

Mazda

Dodge

null

 

I need to create a custom column that will count the instances per row that are not null from Vehicle 1 to Vehicle 3. For the above table the results should look like this:

 

Vehicle ID

Vehicle 1

Vehicle 2

Vehicle 3

Vehicle Count

1

Toyota

Chevrolet

Honda

3

2

Ford

null

null

1

3

Mazda

Dodge

null

2

 

I would click on custom column button to create the "Vehicle Count" column. What m query code should I enter to get the counts of not null values for Vehicle 1, Vehicle 2, Vehicle 3?

1 REPLY 1
edhans
Super User
Super User

Hi - use this formula, and I'll explain. It isn't as complex as it looks, but you cannot click to get this formula.

 

List.Count(List.RemoveNulls(Record.ToList(Record.RemoveFields(_, "Vehicle ID"))))

There are 4 parts. Let's start in the middle and work our way out.

Record.RemoveFields(_, "Vehicle ID) will turn the current row into a record, but omit the "Vehicle ID" column, so you will get something that looks like this. 

edhans_0-1675815687077.png

Record.ToList converts it to a list so we can really work with it.

edhans_1-1675815739269.png

List.RemoveNulls removes any values that are null.

edhans_2-1675815792141.png

And List.Count simply Counts them.

edhans_3-1675815823756.png


I did it this way as it will be mostly dynamic. If you add Vehicle 5, Vehicle 6, Vehicle 7, etc, it will still work.


If you are putting this in Power BI though, a better way would be to right-click the Vehicle ID column, Select Unpivot Other Columns and you get this:

edhans_4-1675815942590.png

Unpivoting automatically removes nulls. Now if you drop the VEHICLE ID column into a table, then create a measure that is just COUNTROWS(Vehicles), or whatever your table name is, it will return 2, 1, and 2 for 1, 2, and 3 respectively. In this case my 4th Id that was all null will be removed as it has no vehicles.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Kudoed Authors