Forum Discussion
Conditional Col - Unexpected result
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
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
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 |
Super UserHi 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