Need to count columns in a row that are not null

[Lorenz33]

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?

[edhans]

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. 

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

List.RemoveNulls removes any values that are null.

And List.Count simply Counts them.

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:

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.