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 function Text.Length). Conditional Col show they are different.

 

Any thing I can do to identify the issues?

 

 

B.R.

Ken

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

4 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super 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
        Super 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