Forum Discussion
How to count duplicate values in M
Hi
I have a table that looks like this:
| ID | PersonID |
| 1 | A |
| 2 | A |
| 3 | B |
| 4 | C |
| 5 | D |
| 6 | E |
| 7 | F |
| 8 | G |
| 9 | G |
| 10 | G |
I want to add a column that counts how many employments each PersonID has, e.g.:
| EmploymentID | PersonID | EmploymentsCount |
| 1 | A | 2 |
| 2 | A | 2 |
| 3 | B | 1 |
| 4 | C | 1 |
| 5 | D | 1 |
| 6 | E | 1 |
| 7 | F | 1 |
| 8 | G | 3 |
| 9 | G | 3 |
| 10 | G | 3 |
I want to do this in the Query Editor, so using M.
Thanks.
Hi bullius,
You need to do a group by the Person ID and then do a Merge of the Step before the Group by and the Group by result, this part you can do it by merging the table with itself and then changing the first table to the step you want.
See the M code for a input table in power query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWJVjKCs4yBLCcwywTIcgazTIEsFzDLDMhyBbPMgSw3MMsCyHIHsyzhLEMDCDMWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, PersonID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"PersonID", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"PersonID"}, {{"Count", each Table.RowCount(_), type number}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"PersonID"},#"Grouped Rows",{"PersonID"},"Grouped Rows",JoinKind.LeftOuter), #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Count"}, {"Grouped Rows.Count"}) in #"Expanded Grouped Rows"Step by step:
Regards,
MFelix
Hi bullius,
Using the option by MarcelBeug,
I have redone the M code:
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWJVjKCs4yBLCcwywTIcgazTIEsFzDLDMhyBbPMgSw3MMsCyHIHsyzhLEMDCDMWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, PersonID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"PersonID", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"PersonID"}, {{"Count", each _, type table}, {"PersonID.1", each Table.RowCount(_), type number}}), #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"ID"}, {"ID"}) in #"Expanded Count"So this one is for Marcel :D
Regards
MFelix
Thanks MFelix.
I would have done it a little bit different:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWJVjKCs4yBLCcwywTIcgazTIEsFzDLDMhyBbPMgSw3MMsCyHIHsyzhLEMDCDMWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, PersonID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"PersonID", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"PersonID"}, {{"AllRows", each _, Value.Type(#"Changed Type")}, {"Count", each Table.RowCount(_), type number}}), #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"ID"}, {"ID"}) in #"Expanded AllRows"You missed the video part between 0:30 - 0:50 where I replace type table.
A disadvantage (or bug or design error or issue) of operation "All Rows" in Group By:
all column types of the nested tables are reset to "Any", which you can see after expansion.That's why I always replace type table with Value.Type(step name) where step name is the same step name as the first parameter of Table.Group.
This is all explained in this video fragment, which is actually a part of a playlist of 3 videos about Value.Type.
9 Replies
Hi bullius,
You need to do a group by the Person ID and then do a Merge of the Step before the Group by and the Group by result, this part you can do it by merging the table with itself and then changing the first table to the step you want.
See the M code for a input table in power query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWJVjKCs4yBLCcwywTIcgazTIEsFzDLDMhyBbPMgSw3MMsCyHIHsyzhLEMDCDMWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, PersonID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"PersonID", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"PersonID"}, {{"Count", each Table.RowCount(_), type number}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"PersonID"},#"Grouped Rows",{"PersonID"},"Grouped Rows",JoinKind.LeftOuter), #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Count"}, {"Grouped Rows.Count"}) in #"Expanded Grouped Rows"Step by step:
Regards,
MFelix
Using Group By with (additional) operation "All Rows" is equivalent with merging 2 tables.
You can use Group By.
Coincidentally I answered a similar question with a video.
In the video, a minimum is returned instead of a count, otherwise itfits your requirements,
There's almost certainly a more efficient way to do this than the below, but you can:
- Duplicate your query
- Group the rows in the duplicate by person ID, including a column that counts the number of rows
- Join this back into your original query
edit - just remembered that I do something quite similar in one of my reports and duplicating it isn't necessary at all, you can do similar to steps 2/3 above, but reword the grouping step so that it creates a new table, so something like
NewTable = Table.Group(#"Previous Step", {"Employee ID"}, {{"Employment Count", each Table.RowCount(_), type number}}),
ImportNewTable = Table.Join(#"Previous Step", "Employee ID", NewTable, "Employee ID")
