Forum Discussion

AliceW's avatar
AliceW
Power Participant
6 years ago
Solved

IF / AND / NOT in Power Query M

Hi all,

Could anybody please convert this DAX formula in M for me? I'm stuck..

Amount =
if(
And(
Opp[Model]="Ren",
not(Opp[Type]="Ma")
),
Opp[Up],
Opp[AC]
)
Need Help
  • nandukrishnavs's avatar
    nandukrishnavs
    6 years ago

    AliceW 

     

    Try this 

    if ([Model] = "Ren") and ([Type] <>"Ma") then [Up] else [Ac]

    PowerQuery is case sensitive, so use small letters only with if, else, then.



    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos     🙂

  • AllisonKennedy's avatar
    AllisonKennedy
    6 years ago

    You can start by adding a Conditional Column in Power Query with just one of the conditions. 

     

    To add a second condition, edit the formula in the formula bar by using the word 'and' as in the formula below:

     

    =if [Model] = "Ren" and [Type]<>"Ma" then [Up] else [AC]

     

    Or, just add a new custom column and paste that formula above into the formula box for the column. Spelling really counts with the column names, so make sure you get that right. 🙂 

     

    Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

     

    If you found this post helpful, please give Kudos.

    I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

    https://sites.google.com/site/allisonkennedycv

  • nandukrishnavs's avatar
    nandukrishnavs
    6 years ago

    AliceW 

     

    You have to mention the column name along with # symbol.

    example [Sales$] ==> [#"Sales$"]



    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos     🙂

     

6 Replies

  • nandukrishnavs's avatar
    nandukrishnavs
    Community Champion
    6 years ago

    AliceW 

     

    Try this 

    if ([Model] = "Ren") and ([Type] <>"Ma") then [Up] else [Ac]

    PowerQuery is case sensitive, so use small letters only with if, else, then.



    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos     🙂

    • AliceW's avatar
      AliceW
      Power Participant
      6 years ago

      Thank you so much, Nandu! It works!

      Follow up question though 🙂 It doesn't allow me to enter columns with a symbol in the name (like $) in the 'then' and 'else' statements. Any idea why?

      Thanks again!

      • nandukrishnavs's avatar
        nandukrishnavs
        Community Champion
        6 years ago

        AliceW 

         

        You have to mention the column name along with # symbol.

        example [Sales$] ==> [#"Sales$"]



        Did I answer your question? Mark my post as a solution!
        Appreciate with a kudos         🙂

         

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    6 years ago

    You can start by adding a Conditional Column in Power Query with just one of the conditions. 

     

    To add a second condition, edit the formula in the formula bar by using the word 'and' as in the formula below:

     

    =if [Model] = "Ren" and [Type]<>"Ma" then [Up] else [AC]

     

    Or, just add a new custom column and paste that formula above into the formula box for the column. Spelling really counts with the column names, so make sure you get that right. 🙂 

     

    Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

     

    If you found this post helpful, please give Kudos.

    I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

    https://sites.google.com/site/allisonkennedycv