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.
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 ReportingCovering 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 |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |