Forum Discussion

jhouse's avatar
jhouse
Regular Visitor
9 years ago
Solved

IF statement Custom Column, compare two columns for the same text and return a result

Hello Community,

 

I am attempting to create a custom column that combines several columns of data into one. Currently i am stuck at comparing two potentially text filled columns to each other for a result. I say potentially because the column has a range of scores to multiple text options.

=if [Reviewer2Action] = "NULL" 
or [Score Delta] <= 3
or [Reviewer2Result]=[Reviewer1Result]
then "Aligned"
else "Not Aligned"

 

Here is the error i am receiving

Expression.Error: We cannot apply operator - to types Text and Text.
Details:
Operator=-
Left= Functionality Issues
Right= Functionality Issues 

 

The error stating that it cannot use the operator " - " to types text & text makes me think i should be able to convert the text to numerical values and then compare. Unfortunately i am too green with Power BI and don't know how to do that....if that is even the right next step.

 

I have been able to figure it out with the use of multiple columns, but i would really like to keep this contained to just one. In the future i will be needing to add additional parameters and do not want to bloat the source up with additional columns.

 

Thanks in advance.

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    Hi jhouse

     

    It worked fine for me using this code. I load my own sample data.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ivNyVHSUTICYkMwjtWJVkopTgOyTeDisbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Reviewer2Action = _t, #"Score Delta" = _t, Reviewer2Result = _t, Reviewer1Result = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reviewer2Action", type text}, {"Score Delta", Int64.Type}, {"Reviewer2Result", Int64.Type}, {"Reviewer1Result", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Reviewer2Action] = "NULL" 
or [Score Delta] <= 3
or [Reviewer2Result]=[Reviewer1Result]
then "Aligned"
else "Not Aligned")
in
    #"Added Custom"

1 Reply

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    Hi jhouse

     

    It worked fine for me using this code. I load my own sample data.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ivNyVHSUTICYkMwjtWJVkopTgOyTeDisbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Reviewer2Action = _t, #"Score Delta" = _t, Reviewer2Result = _t, Reviewer1Result = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reviewer2Action", type text}, {"Score Delta", Int64.Type}, {"Reviewer2Result", Int64.Type}, {"Reviewer1Result", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Reviewer2Action] = "NULL" 
or [Score Delta] <= 3
or [Reviewer2Result]=[Reviewer1Result]
then "Aligned"
else "Not Aligned")
in
    #"Added Custom"