Forum Discussion

AlejandroPCar's avatar
AlejandroPCar
Helper IV
8 years ago
Solved

Multiple AND conditions in a IF statement in Query Editor

Hi!

 

I have a question, I tried to do the following as a custom column: 

 

=  if [P7130] = 1 and 
[P7140S1] = 1 and 
[P7150] = 1 and 
[P7160] = 1
then 5
else 7

But the column result does not cointains any 5! just 7 What I doing wrong? 

 

Thanks for your help. 

 

Alejandro

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    I don't know, that worked for me. Are you columns numbers or text? Mine are numbers.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSQcKxOqgiRkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [P7130 = _t, P7140S1 = _t, P7150 = _t, P7160 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"P7130", Int64.Type}, {"P7140S1", Int64.Type}, {"P7150", Int64.Type}, {"P7160", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [P7130] = 1 and 
[P7140S1] = 1 and 
[P7150] = 1 and 
[P7160] = 1
then 5
else 7)
in
    #"Added Custom"

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    I don't know, that worked for me. Are you columns numbers or text? Mine are numbers.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSQcKxOqgiRkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [P7130 = _t, P7140S1 = _t, P7150 = _t, P7160 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"P7130", Int64.Type}, {"P7140S1", Int64.Type}, {"P7150", Int64.Type}, {"P7160", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [P7130] = 1 and 
[P7140S1] = 1 and 
[P7150] = 1 and 
[P7160] = 1
then 5
else 7)
in
    #"Added Custom"
    • AlejandroPCar's avatar
      AlejandroPCar
      Helper IV
      8 years ago

      Hi, 

       

      Thanks for your response. Yes you are right, I taked some time before reach a solution and as you said is about the columns data format. Thank you.