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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Memorable Member
Memorable Member

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.

mlsx4
Memorable Member
Memorable Member

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.

 

mlsx4
Memorable Member
Memorable Member

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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