Forum Discussion

CMoppet's avatar
CMoppet
Helper IV
2 years ago
Solved

Conditional Column if value appears within a list

Hello,     I have a conditional column in a table called 'WO DATA' with various clauses based on the values in a column called 'Account NAME'.  It's straightforward because I can reply on the accou...
Need Help
  • AmiraBedh's avatar
    AmiraBedh
    2 years ago

    Try the following :

    Column =
IF(
    [SAP Account Id] IN {5300023122, 5300023123, 5300023124, 5300023125, 5300023126, 5300023127, 5300023129, 5300023130, 5300023132, 5300023131},
    "EMCOR",
    IF(
        CONTAINSSTRING([Account Name], "Waitrose"),
        "Waitrose",
        IF(
            CONTAINSSTRING([Account Name], "John Lewis"),
            "John Lewis",
            IF(
                CONTAINSSTRING([Account Name], "Thames Water"),
                "Thames Water",
                IF(
                    CONTAINSSTRING([Account Name], "WSP"),
                    "WSP",
                    BLANK() 
                )
            )
        )
    )
)
CMoppet's avatar
CMoppet
Helper IV
2 years ago

So, I want to add a clause that says "If AZTEC column contains "Aztec", the output for this Customer Group column should be 'Aztec'

AmiraBedh's avatar
AmiraBedh
Super User
2 years ago

Use DAX it is better where you can reference the calculated column.

Calculated columns do not show in Power Query Editor (PQE) because they are created in report view, data view, or model view, which are based on data you've already loaded into Power BI desktop. 

  • CMoppet's avatar
    CMoppet
    Helper IV
    2 years ago

    Ah, I see.  So I need to combine whatI already had in the conditional column, with what you've provided above?   I've tried this: 

     

    Column =
    IF([SAP Account Id] IN {5300023122, 5300023123, 5300023124, 5300023125, 5300023126, 5300023127, 5300023129, 5300023130, 5300023132, 5300023131},
        "EMCOR"), //
    else if Text.Contains([Account Name], "Waitrose") then "Waitrose" else if Text.Contains([Account Name], "John Lewis") then "John Lewis" else if Text.Contains([Account Name], "Thames Water") then "Thames Water" else if Text.Contains([Account Name], "WSP") then "WSP" else null)
     
    But it's throwing up a syntax error.  Please can you help me spot the issue?  I'm very grateful for your help.  I'm not very good at writing DAX yet, but am learning...