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.
Hello!
Thanks
I don't know how you'd do it in DAX, but in Power Query, you could first Group By Employee Number, use a Count aggregation (let's name it "RowCount" and an All Rows aggregation (let's name it "Details", and then add an Index (beginning at 1) to each of the nested tables, and subtracting 1 from each Index value where the count of rows is 1. So after the Group By step:
Table.TransformColumns(PriorStepOrTableName, "Details", each Table.AddIndexColumn(_, "Count", 1, 1), Int64.Type)
Now you can expand the column, but your single row value will be 1 instead of zero. So now you can go
Table.AddColumn(PriorStepOrTableName, "CorrectCount", each if [RowCount] = 1 then 0 else [Count])
--Nate
Good to go!
Covering 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 |
---|---|
101 | |
49 | |
19 | |
12 | |
11 |