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 ...
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"
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"