Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Vishal
Regular Visitor

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

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!!

 

abc234defqazwsxabc234de
4edcv234edcv
123qaz3qaz
1 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

vxinruzhumsft_0-1700807639646.png

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.

 

View solution in original post

6 REPLIES 6
mlsx4
Super User
Super User

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)

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

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

Have you checked?

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

 Col1   Col2  
2PC920040YNot in Report
HJ0403QJNot in Report
QJ05Y5E5Not in Report
Not in ReportQ0V114HJ0403QJ
Not in ReportA0V114QJ05Y5E5
Not in ReportP09W001C0F87WG
Not in ReportT0EW00K90D3T2E
Not in ReportPC920040Y
1C0F87WGNot in Report
K90D3T2ENot in Report

 

Ideally every single value must show status as Matched.

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

vxinruzhumsft_0-1700807639646.png

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.

 

Hi @Vishal 

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

 

mlsx4_0-1700744325933.png

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors