Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Custom Column in Power Query IF ELSE

Is there such a formula in Power Query Custom Column for this:

 

if ([BUSINESS_UNIT]='ZZZ' and STARTSWITH([ORDER_TYPE],'Standard')) then 'True'

elseif ([BUSINESS_UNIT]='ZZZ' and STARTSWITH([ORDER_TYPE],'Project')) then 'True'
elseif ([BUSINESS_UNIT]='XX' and STARTSWITH([ORDER_TYPE],'Project')) then 'True'
elseif ([BUSINESS_UNIT]='ZZZ' and STARTSWITH([ORDER_TYPE],'Standard')) then 'True'
elseif ([BUSINESS_UNIT]='AAA' and NOT STARTSWITH(STR([ORDER_NUMBER]),'9')) then 'True'
elseif ([BUSINESS_UNIT]='AAA' and NOT STARTSWITH(STR([ORDER_NUMBER]),'7')) then 'True'

else 'False' END

 

 

Need Help
  • ERD's avatar
    ERD
    4 years ago

    Anonymous , and using lower case:

    not (...)

8 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , Try like

     

    if [BUSINESS_UNIT]="ZZZ" and Text.StartsWith([ORDER_TYPE],"Standard") then "True"

    else if [BUSINESS_UNIT]="ZZZ" and Text.StartsWith([ORDER_TYPE],"Project") then "True"
    else if [BUSINESS_UNIT]="XX" and Text.StartsWith([ORDER_TYPE],"Project") then "True"
    else if [BUSINESS_UNIT]="ZZZ" and Text.StartsWith([ORDER_TYPE],"Standard") then "True"
    else if [BUSINESS_UNIT]="AAA" and NOT Text.StartsWith(STR([ORDER_NUMBER]),"9") then "True"
    else if [BUSINESS_UNIT]="AAA" and NOT Text.StartsWith(STR([ORDER_NUMBER]),"7") then "True"

    else "False"

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        4 years ago

        Anonymous , Sorry, Number.ToText in place of STR

         

        if [BUSINESS_UNIT]="ZZZ" and Text.StartsWith([ORDER_TYPE],"Standard") then "True"

        else if [BUSINESS_UNIT]="ZZZ" and Text.StartsWith([ORDER_TYPE],"Project") then "True"
        else if [BUSINESS_UNIT]="XX" and Text.StartsWith([ORDER_TYPE],"Project") then "True"
        else if [BUSINESS_UNIT]="ZZZ" and Text.StartsWith([ORDER_TYPE],"Standard") then "True"
        else if [BUSINESS_UNIT]="AAA" and NOT Text.StartsWith(Number.ToText([ORDER_NUMBER]),"9") then "True"
        else if [BUSINESS_UNIT]="AAA" and NOT Text.StartsWith(Number.ToText([ORDER_NUMBER]),"7") then "True"

        else "False"