Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Nested If Statement PowerQuery

Hi need help with a  nested if statetment. 

I have a table with a Location_code and Unit. Need the follwoing if statement in power query

IF the location code is "130" or "600" then replace all the unit values with the word "Mixed" else show the original unit value.

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    Anonymous 

    Modified:

     

    if ([Location_Code] = 130 or [Location_Code] = 600) and [Units] = "IPO" then [Units] else if ([Location_Code] = 130 or [Location_Code] = 600) then "Mixed" else [Units]

     

7 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    Anonymous 

    Do you need a new column? Hope you have two column Location_Code and the Units. If you have numbers in units, why do you want to have text in that as "mixed" ?


    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Yes, it will be a new column and the Unit is text not numbers oringinaly. Example Unit: GRE, ADO, or ADU.

      • Fowmy's avatar
        Fowmy
        Super User
        5 years ago

        Anonymous 

        Click on Custom Column and add the following code:

        if [Location_Code] = 130 or [Location_Code] = 600 then "Mixed" else [Units]

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Sample Code that may help your case from Microsofts documentation.  It also depends if your using DAX or Power Query as the coding is completely different.

    DAX:

    Price Group = IF( 'Product'[List Price] < 500, "Low", IF( 'Product'[List Price] < 1500, "Medium", "High" ) )

    IF function (DAX) - DAX | Microsoft Docs

  • ChrisMendoza's avatar
    ChrisMendoza
    Resident Rockstar
    5 years ago

    Here it is if you do not want to add an additional column:

    = Table.ReplaceValue(#"Changed Type",each [Unit], each if [Location_code] = 130 or [Location_code] = 600 then "Mixed" else [Unit] ,Replacer.ReplaceText, {"Unit"})