Forum Discussion
bullius
Helper V
8 years agoHow 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 emp...
MarcelBeug
Community Champion
8 years agoThanks 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.
MFelix
Super User
8 years ago