Find Duplicates in a Column (No Group By)

[donkey shrek]

I want to identify duplicates in column RandomColumn without using Group By - as this method will drop unused columns

= let columnNames = {"RandomColumn"}, addCount = Table.Group(#"Added Index", columnNames, {{"Count", Table.RowCount, type number}}) in Table.AddColumn(#"Added Index", "TESTICLES", if Table.SelectRows(addCount, each [Count] > 1) then "Duplicate" else "OK")

 

[lrobbo314]

Something like this?

Duplicates
	A	B	C	D
1	Values		Values	DupCheck
2	A		A
3	B		B
4	C		C
5	C		C	Duplicate
6	D		D
7	E		E
8	F		F
9	F		F	Duplicate
10	G		G
11	H		H
Sheet4

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Duplicates = List.Accumulate(Source[Values],{},(s,c)=> if List.Contains(s,c) then s & {"Duplicate"} else s & {c}),
    Index = Table.AddIndexColumn(Source, "DupCheck", 0, 1, Int64.Type),
    TX = Table.TransformColumns(Index,{{"DupCheck", each if Duplicates{_}="Duplicate" then "Duplicate" else null}})
in
    TX

 

[donkey shrek]

Something like this?

Duplicates
	A	B	C	D
1	Values		Values	DupCheck
2	A		A
3	B		B
4	C		C
5	C		C	Duplicate
6	D		D
7	E		E
8	F		F
9	F		F	Duplicate
10	G		G
11	H		H
Sheet4

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Duplicates = List.Accumulate(Source[Values],{},(s,c)=> if List.Contains(s,c) then s & {"Duplicate"} else s & {c}),
    Index = Table.AddIndexColumn(Source, "DupCheck", 0, 1, Int64.Type),
    TX = Table.TransformColumns(Index,{{"DupCheck", each if Duplicates{_}="Duplicate" then "Duplicate" else null}})
in
    TX

Thank you! That works well. How about if ALL instances of F and C state "Duplicate", rather than just the 2nd occurrence of them?

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    lst = List.Buffer(Source[Values]), 
    tbl = Table.AddColumn(Source, "DupCheck", each if List.Count(List.Select(lst, (x)=> x = [Values]))>1 then "Duplicate" else "")
in
    tbl

 

[donkey shrek]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    lst = List.Buffer(Source[Values]),
    tbl = Table.AddColumn(Source, "DupCheck", each if List.Count(List.Select(lst, (x)=> x = [Values]))>1 then "Duplicate" else "")
in
    tbl

Thanks! This works but only when the list matches exactly. How about if just any given element in is found in any other list, then they will all be marked duplicate?

 

[JGordon11]

Not sure what you're asking. Can you post sample data and what the results table should look like for that sample?

 

[donkey shrek]

I want all lists to be able to compare against each other, to check for any similar values

Something like:
For Each List in [ListofDateTimes]
if List.ContainsAny(CurrentList, [ListOfDateTimes])
then "Yes" else "No"

 

[JGordon11]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(
        "XVFJDsQgDPtLz1XUEsDJWxD//8agiiWeG5EdL6S1K2dXl+RvxXV/UxWU+YTkt24AUvKhueAx1TmZ4JPod5vzwDY6Nk0DNxcL2LAI5k9gLo+lyqhRbNaBpLIl6377JB1BXlKN8rHgsKbyKWA+P2apQpwKxrpcASEbyGGVO0lBjraPZJSFG5mUv+P1/gM=", 
        BinaryEncoding.Base64), Compression.Deflate))),
    ChangedType = Table.TransformColumnTypes(Source,{{"Column1", type number}, {"Column2", type number}, {"Column3", type number}, {"Column4", type number}, {"Column5", type number}, {"Column6", type number}}),
    tbl = Table.TransformColumnTypes(ChangedType,{{"Column1", type datetime}, {"Column2", type datetime}, {"Column3", type datetime}, {"Column4", type datetime}, {"Column5", type datetime}, {"Column6", type datetime}}),
    lst = Table.ToColumns(tbl),
    tbl2 = Table.FromList(lst, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    tbl3 = Table.AddIndexColumn(tbl2, "Index", 0, 1, Int64.Type),
    lst1 = List.Zip({tbl3[Column1], tbl3[Index]}),
    tbl4 = Table.AddColumn(tbl3, "Overlap", each List.Accumulate(lst1, "", (s,c)=> if List.ContainsAny([Column1], c{0}) then s & Text.From(c{1}) & "," else s)),
    tbl5 = Table.FromRecords(Table.TransformRows(tbl4, each Record.TransformFields(_, {"Overlap", (x)=> Text.Replace(x, Text.From(_[Index]) & ",", "")}))),
    Result = Table.TransformColumns(tbl5, {"Overlap", each Text.Start(_, Text.Length(_) -1 )})
in
    Result

 

[donkey shrek]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(
        "XVFJDsQgDPtLz1XUEsDJWxD//8agiiWeG5EdL6S1K2dXl+RvxXV/UxWU+YTkt24AUvKhueAx1TmZ4JPod5vzwDY6Nk0DNxcL2LAI5k9gLo+lyqhRbNaBpLIl6377JB1BXlKN8rHgsKbyKWA+P2apQpwKxrpcASEbyGGVO0lBjraPZJSFG5mUv+P1/gM=",
        BinaryEncoding.Base64), Compression.Deflate))),
    ChangedType = Table.TransformColumnTypes(Source,{{"Column1", type number}, {"Column2", type number}, {"Column3", type number}, {"Column4", type number}, {"Column5", type number}, {"Column6", type number}}),
    tbl = Table.TransformColumnTypes(ChangedType,{{"Column1", type datetime}, {"Column2", type datetime}, {"Column3", type datetime}, {"Column4", type datetime}, {"Column5", type datetime}, {"Column6", type datetime}}),
    lst = Table.ToColumns(tbl),
    tbl2 = Table.FromList(lst, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    tbl3 = Table.AddIndexColumn(tbl2, "Index", 0, 1, Int64.Type),
    lst1 = List.Zip({tbl3[Column1], tbl3[Index]}),
    tbl4 = Table.AddColumn(tbl3, "Overlap", each List.Accumulate(lst1, "", (s,c)=> if List.ContainsAny([Column1], c{0}) then s & Text.From(c{1}) & "," else s)),
    tbl5 = Table.FromRecords(Table.TransformRows(tbl4, each Record.TransformFields(_, {"Overlap", (x)=> Text.Replace(x, Text.From(_[Index]) & ",", "")}))),
    Result = Table.TransformColumns(tbl5, {"Overlap", each Text.Start(_, Text.Length(_) -1 )})
in
    Result

You're amazing! Thank you so much!