How to list duplicates in each of the rows in new column?

[Anonymous]

Hello,

I have encountered some challenges in attempting to achieve the following task. Identifying duplicates becomes straightforward when considering entire columns, but my objective is to identify duplicates from Column A within each individual row seperated by commas. Is this achievable using Power BI DAX? The desired results are outlined below. Please note that Column A is a calculated column, and we are restricted from making modifications in M.

A	Duplicates
a,b,b,c	b
a,a,b,b,c,c	a,b,c
a,b,c
a,a,b	a

Any help is greatly appreciated, thank you so much. 

[barritown]

Hi @Anonymous,

I guess the proper approach is to use Power Query and @ThxAlot's solution, but here's a DAX way to solve this:

The same in plain text:

Duplicates = 
VAR CurrentItem = [A]
VAR WorkItem = SUBSTITUTE ( CurrentItem, ",", "|" )
VAR Qty = LEN ( CurrentItem ) - LEN ( SUBSTITUTE ( CurrentItem, ",", "" ) ) + 1
VAR _tbl = GENERATESERIES ( 1, Qty )
VAR _tbl2 = ADDCOLUMNS ( _tbl, 
                         "Name", 
                         PATHITEM ( WorkItem, [Value] ) )
VAR _tbl3 = SUMMARIZE ( _tbl2, [Name],
                         "Qty", 
                         VAR CurrentName = [Name]
                         RETURN COUNTROWS ( FILTER ( _tbl2, [Name] = CurrentName ) ) )
RETURN CONCATENATEX ( SELECTCOLUMNS ( FILTER ( _tbl3, [Qty] > 1 ), [Name] ), [Name], "," ) 

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[Anonymous · Accepted solution]

Thank you! I tried but it is showing error for [Name] at the very last line.

I modified a little and it works! 

All Duplicates =

VAR CurrentItem = [A]

VAR WorkItem = SUBSTITUTE(CurrentItem, ",", "|")

VAR Qty = LEN(CurrentItem) - LEN(SUBSTITUTE(CurrentItem, ",", "")) + 1

VAR _tbl = GENERATESERIES(1, Qty)

VAR _tbl2 = ADDCOLUMNS(_tbl,

                       "Name",

                       PATHITEM(WorkItem, [Value]))

VAR _tbl3 = SUMMARIZE(_tbl2, [Name],

                      "Qty",

                      VAR CurrentName = [Name]

                      RETURN COUNTROWS(FILTER(_tbl2, [Name] = CurrentName)))

VAR DuplicatesList = CONCATENATEX(FILTER(_tbl3, [Qty] > 1), [Name], ",")

RETURN DuplicatesList

[ThxAlot]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WStRJAsJkpVgdEBvKg/NRZJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Str = _t]),
    #"Added Dupliate" = Table.AddColumn(Source, "Dup", each let l = Text.Split([Str], ",") in Text.Combine(List.Distinct(List.Select(l, each List.Count(List.PositionOf(l, _, Occurrence.All))>1)), ","))
in
    #"Added Dupliate"