none
Power query statement with if, or, and RRS feed

  • Question

  • Hey guys

     

    Please, i know i must be doing something wrong...

    I'm trying to create a new column

     

    =if [Cost Center#] = "7851" and [Type Doc] = "FA" , or if [Cost Center#] = "7851" and [Type Doc] = "SS" , then "ResA", 

    else if [Cost Center#] = "7852" and [Type Doc] = "FA" , or if [Cost Center#] = "7852" and [Type Doc] = "SS" , then "ResB",

    else if [Cost Center#] = "7853" and [Type Doc] = "FA" , or if [Cost Center#] = "7853" and [Type Doc] = "SS" , then "ResC",

    "ResB" 

     

    Can anyone help me? The idea is to work with if, or, and

    Thank you very much

    Pedro

    Sunday, November 4, 2018 5:08 PM

Answers

  • try this

    = if ( [#"Cost Center#"]="7851" and [Type Doc]="FA" ) or ([#"Cost Center#"]="7851" and [Type Doc]="SS") then "ResA" else if ([#"Cost Center#"]="7852" and [Type Doc]="FA") or ([#"Cost Center#"]="7852" and [Type Doc]="SS") then "ResB" else if ([#"Cost Center#"]="7853" and [Type Doc]="FA") or ([#"Cost Center#"]="7853" and [Type Doc]="SS") then "ResC"

    else null


    • Edited by anthony34 Monday, November 5, 2018 12:24 AM
    • Marked as answer by Pedroccamara Monday, November 5, 2018 9:48 AM
    Sunday, November 4, 2018 7:24 PM
  • = Table.AddColumn(#"previous step", "new column", each if ( [#"Cost Center#"]="7851" and not (Text.StartsWith([Type Doc],"S") ) or ([#"Cost Center#"]="7851" and [Type Doc]="SS") then "ResA" else if ([#"Cost Center#"]="7852" and [Type Doc]="FA") or ([#"Cost Center#"]="7852" and [Type Doc]="SS") then "ResB" else if ([#"Cost Center#"]="7853" and [Type Doc]="FA") or ([#"Cost Center#"]="7853" and [Type Doc]="SS") then "ResC"

    else null, type text)

    Be careful that the last "else" is mandatory:

    if then is not working

    Only if then else is working in M

    I added else null to complete your formula but you are free to add whatever you need after else

    Alternatives:

    [#"Cost Center#"]="7851" and not (Text.Start([Type Doc],1) = "S")
    
    // if not case sensitive always use Text.Lower or Text.Upper to avoid bad surprise
    
     [#"Cost Center#"]="7851" and not (Text.StartsWith(Text.Upper([Type Doc]),"S")


    https://msdn.microsoft.com/query-bi/m/text-startswith

    https://msdn.microsoft.com/query-bi/m/text-start

    https://msdn.microsoft.com/query-bi/m/text-contains

    Be careful of the case and of the "s" in Text.StartsWith


    • Edited by anthony34 Monday, November 5, 2018 12:27 AM
    • Marked as answer by Pedroccamara Monday, November 5, 2018 9:48 AM
    Sunday, November 4, 2018 11:48 PM

All replies

  • try this

    = if ( [#"Cost Center#"]="7851" and [Type Doc]="FA" ) or ([#"Cost Center#"]="7851" and [Type Doc]="SS") then "ResA" else if ([#"Cost Center#"]="7852" and [Type Doc]="FA") or ([#"Cost Center#"]="7852" and [Type Doc]="SS") then "ResB" else if ([#"Cost Center#"]="7853" and [Type Doc]="FA") or ([#"Cost Center#"]="7853" and [Type Doc]="SS") then "ResC"

    else null


    • Edited by anthony34 Monday, November 5, 2018 12:24 AM
    • Marked as answer by Pedroccamara Monday, November 5, 2018 9:48 AM
    Sunday, November 4, 2018 7:24 PM
  • Very good. Thank you so much

    Now what if i wanted [Cost center] = "7851" and [Type Doc] <> "S*"  

    Sunday, November 4, 2018 8:57 PM
  • = Table.AddColumn(#"previous step", "new column", each if ( [#"Cost Center#"]="7851" and not (Text.StartsWith([Type Doc],"S") ) or ([#"Cost Center#"]="7851" and [Type Doc]="SS") then "ResA" else if ([#"Cost Center#"]="7852" and [Type Doc]="FA") or ([#"Cost Center#"]="7852" and [Type Doc]="SS") then "ResB" else if ([#"Cost Center#"]="7853" and [Type Doc]="FA") or ([#"Cost Center#"]="7853" and [Type Doc]="SS") then "ResC"

    else null, type text)

    Be careful that the last "else" is mandatory:

    if then is not working

    Only if then else is working in M

    I added else null to complete your formula but you are free to add whatever you need after else

    Alternatives:

    [#"Cost Center#"]="7851" and not (Text.Start([Type Doc],1) = "S")
    
    // if not case sensitive always use Text.Lower or Text.Upper to avoid bad surprise
    
     [#"Cost Center#"]="7851" and not (Text.StartsWith(Text.Upper([Type Doc]),"S")


    https://msdn.microsoft.com/query-bi/m/text-startswith

    https://msdn.microsoft.com/query-bi/m/text-start

    https://msdn.microsoft.com/query-bi/m/text-contains

    Be careful of the case and of the "s" in Text.StartsWith


    • Edited by anthony34 Monday, November 5, 2018 12:27 AM
    • Marked as answer by Pedroccamara Monday, November 5, 2018 9:48 AM
    Sunday, November 4, 2018 11:48 PM
  • awesome!!!!

    Thank you so much Anthony!!!

    Monday, November 5, 2018 9:47 AM