Conditional Col - Unexpected result

[kenchan]

Hi Team,

I am using PowerBI to identify 2 fields whether they are the same or not. However, there are 2 rows of data which should be same ( trimmed and Length of fields are the same - using function Text.Length). Conditional Col show they are different.

Any thing I can do to identify the issues?

B.R.

Ken

[PhilipTreacy · Accepted solution]

Hi @kenchan 

Your request is a little vague.  What exactly sre you comparing, rows or values in columns?  How do you want them to be compared?  What constitutes one thing being the same as the other?

Please provide some sampe data.

regards

Phil

[PhilipTreacy · Accepted solution]

Hi @kenchan 

Your request is a little vague.  What exactly sre you comparing, rows or values in columns?  How do you want them to be compared?  What constitutes one thing being the same as the other?

Please provide some sampe data.

regards

Phil

[kenchan]

Hi Philop, thank you for your response.

I am comparing 2 text cols. If the text are equal, "Pass, "fail". Some rows which are the same; but the data showing "pass". I have trimmed the Col; but it doesnt help. 

For this problem, any clues I can further identify the gap? Thank you.

Col A	B	Result
Imran shoukat Ken	Imran Shoukat	Fail
Kmran shoukat	Kmran Shoukat	Pass
PKKja  Wadhwa Mathur	PKKja Wadhwa Mathur	Pass

[Vijay_A_Verma]

For case insensitive equality, you can use following formula

= Comparer.Equals(Comparer.OrdinalIgnoreCase,[ColA],[ColB])

But your columns have got unequal spaces between words. Entry no. 3 is such a case...You would need to handle this through Excel's Trim function. I have described the formula through which Excel's Trim function is implemented here - Text.ExcelTrim function in Power Query M Language

So, you will have to merge this and above to have a final formula which will give you right result.

Hence, your final formula becomes

= if Comparer.Equals(Comparer.OrdinalIgnoreCase,Text.Combine(List.RemoveItems(Text.Split([ColA]," "),{""})," "),Text.Combine(List.RemoveItems(Text.Split([ColB]," "),{""})," ")) then "Pass" else "Fail"

See it in action here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8swtSsxTKM7IL81OLFHwTs1T0oGKBUPElGJ1opW8kVUBVXhjqAjw9s5KVFAIT0zJKE9U8E0sySgtAiqECKOKxsYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColA = _t, ColB = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Comparer.Equals(Comparer.OrdinalIgnoreCase,Text.Combine(List.RemoveItems(Text.Split([ColA]," "),{""})," "),Text.Combine(List.RemoveItems(Text.Split([ColB]," "),{""})," ")) then "Pass" else "Fail")
in
    #"Added Custom"

[PhilipTreacy · Accepted solution]

Hi @kenchan 

Download example PBIX file with code

So you want case sensitive comparison?  In you're 2nd row in that table should be Fail because the case of s is different in each string?

Try this, you want to add a column and use the code in the #"Added Custom" step

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8swtSsxTKM7IL81OLFHwTs1T0oGKBUPElGJ1opW8kVUBVXhjqAjw9s5KVFAIT0zJKE9U8E0sySgtAiqECKOKxsYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColA = _t, ColB = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Replace([ColA], " " , "") = Text.Replace([ColB], " " , "") then "Pass" else "Fail")
in
    #"Added Custom"

Regards

Phil