Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCheck out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
7 | |
5 | |
5 | |
5 | |
4 |