return uncommon String after Comparisons

[Anonymous]

I want to compare two colums. Both of them contain strings.

I need to get the un common string in thrid column.

Col1

abc|def|ijk|bcd|plk

Col2

abc|plk|ijk

Col3 [Resulting Column ]
def|bcd

[OwenAuger · Accepted solution]

Hi @Anonymous 

Off the top of my head, whether using M or DAX, I would:

1. Split each of Col1 & Col2 into lists List1 & List2
2. Take the union of List1 & List2, and remove the intersection of List1 & List2 (and remove duplicates & sort if desired)
3. Concatenate the result

Attached PBIX has examples in M & DAX.

The step to add such a column in M in my example is:

= Table.AddColumn(#"Changed Type", "Col3 M",
        each let List1 = Text.Split([Col1],"|"),
                 List2 = Text.Split([Col2],"|"),
                 Union = List.Union({List1,List2}),
                 Intersection = List.Intersect({List1,List2}),
                 Uncommon = List.Sort(List.Distinct(List.Difference(Union,Intersection)))
             in
                 Text.Combine(Uncommon,"|"), type text)

The DAX calculated column version is:

Col3 DAX = 
VAR String1 = Test[Col1]
VAR String2 = Test[Col2]
VAR Length1 = PATHLENGTH ( String1 )
VAR Length2 = PATHLENGTH ( String2 )
VAR List1 =
    SELECTCOLUMNS (
        GENERATESERIES ( 1, Length1 ),
        "Value", PATHITEM ( String1, [Value] )
    )
VAR List2 =
    SELECTCOLUMNS (
        GENERATESERIES ( 1, Length2 ),
        "Value", PATHITEM ( String2, [Value] )
    )
VAR Uncommon = 
    DISTINCT (
        EXCEPT ( 
            UNION ( List1, List2 ),
            INTERSECT ( List1, List2 )
        )
    )
RETURN
    CONCATENATEX (
        Uncommon,
        [Value],
        "|",
        [Value]
    )

There could be some optimization, but that's what comes to mind 🙂

Hope that helps.

Regards,

Owen