Forum Discussion

kenchan's avatar
kenchan
Frequent Visitor
4 years ago
Solved

Conditional Col - Unexpected result

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 funct...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    4 years ago

    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's avatar
    PhilipTreacy
    4 years ago

    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

PhilipTreacy's avatar
PhilipTreacy
Icon for Super User rankSuper User
4 years ago

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's avatar
kenchan
Frequent Visitor
4 years ago

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 BResult
Imran shoukat KenImran ShoukatFail
Kmran shoukatKmran ShoukatPass
PKKja  Wadhwa MathurPKKja Wadhwa MathurPass
  • PhilipTreacy's avatar
    PhilipTreacy
    Icon for Super User rankSuper User
    4 years ago

    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

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    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"