How to count duplicate values in M

[bullius]

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.

[MFelix · Accepted solution]

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

[MFelix · Accepted solution]

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 😄

Regards

MFelix

[MFelix · Accepted solution]

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

[MarcelBeug]

Using Group By with (additional) operation "All Rows" is equivalent with merging 2 tables.

[bullius]

Thanks for all your responses!

@MarcelBeug, could you give an example of the code that would include the "All Rows" operation?

[MFelix · Accepted solution]

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 😄

Regards

MFelix

[MarcelBeug · Accepted solution]

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.

[MFelix]

Hi @MarcelBeug,

You are correct I have made the change but copied the incorrect M code :D.

MFelix

[MFelix]

Thanks @MarcelBeug,

Always learning :D:D

MFelix

[jthomson]

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")

[MarcelBeug]

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,