How to do partial comparison b/w 2 columns in power bi, power query, DAX

[Vishal]

I have to columns where i need to do comparison. If data in 2 columns are exactly same I am getting correct result but where there are additinal characters, I am not getting desired result. For eg: Following values in 2 columns must match, how to do that? I used contain function in power query, SEARCH function in DAX but not getting result. Need help!!

abc234def	qazwsxabc234de
4edcv	234edcv
123qaz	3qaz

[mlsx4]

Hi @Vishal 

You can do:

= Table.AddColumn(#"PreviousStepName", "IsContained", each Text.Contains( [Column1] , [Column2] , Comparer.OrdinalIgnoreCase ) or Text.Contains( [Column2] , [Column1] , Comparer.OrdinalIgnoreCase ) , type logical)

[Vishal]

Hi @mlsx4 ,
Thanks for your reply. Like I mentioned, I have already tried mentioned steps and it is not working.

[mlsx4]

I have tried with your exact example and it is working perfectly. 

Have you checked?

[Vishal]

Yes, Re-checked. Here is output Table for your reference which is incorrect.

Col1

Col2

2PC920040Y	Not in Report
HJ0403QJ	Not in Report
QJ05Y5E5	Not in Report
Not in Report	Q0V114HJ0403QJ
Not in Report	A0V114QJ05Y5E5
Not in Report	P09W001C0F87WG
Not in Report	T0EW00K90D3T2E
Not in Report	PC920040Y
1C0F87WG	Not in Report
K90D3T2E	Not in Report

Ideally every single value must show status as Matched.

[v-xinruzhu-msft · Accepted solution]

Hi @Vishal 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKNjI2SUlNU9JRKkysKi+ugIkoxepEK5mkpiSXAaWAImAWSMzQyBioEigIpmJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.Intersect({Text.ToList([Column1]),Text.ToList([Column2])}))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom]<>null then "Matched" else "Not Matched")
in
    #"Added Custom1"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mlsx4]

Hi @Vishal 

I don't know which is your data then, with the only information you have provided, it works for me...

Try to put here more examples, especially what you're trying to compare and fails...