How to count duplicate values in M

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.