Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
Hi @Anonymous
Off the top of my head, whether using M or DAX, I would:
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
Hi @Anonymous
Off the top of my head, whether using M or DAX, I would:
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