Forum Discussion

JoeyF's avatar
JoeyF
Frequent Visitor
2 years ago
Solved

Nested IF OR Statement when creating a New Custom Column

I am trying to create a new Custom Column which is creating a new Category Name based on the text contained in the CATNAME column.  Basically I want the Category Name to be "Roads" if the CATNAME co...
Need Help
  • kpost's avatar
    kpost
    2 years ago

    Well, I'm much more familiar with DAX than Power Query. 

     

    So here's how I'd do it as a calculated column.  But don't let this deter you from trying it your way, just throwing in one viable solution.

     

    Custom Column = 
    VAR catname = [CATNAME]
    RETURN
    SWITCH(
            TRUE(),
             CONTAINSSTRING(catname, "SIGN") || CONTAINSSTRING(catname, "KERB") || CONTAINSSTRING(catname, "PATH") || CONTAINSSTRING(catname, "ROAD"), "Roads",

             CONTAINSSTRING(catname, "DRAIN"), "Drainage",
             catname

    )

     

    Hopefully you can get some help with your column in Power Query, but if not this will work for you.

Transform121's avatar
Transform121
Helper I
2 years ago

The issue is syntax.

You have more nestings created by open parenthesis than you do by closed. Count your open and closed parenthesis separately (using a stepping method) and you'll see where things are left open.

 

I've put a simple counting image below for parenthesis, but you're currently missing 5 closed parenthesis because of additional inserts. If you resolve this the token literal error should fix itself.

 

 

Try something like this. It will nest all of the first options for roads into 1 group.

 

if (Text.Contains([CATNAME], "SIGN") or Text.Contains([CATNAME], "KERB") or Text.Contains([CATNAME], "PATH") or Text.Contains([CATNAME], "ROAD") then "ROADS") else if Text.Contains([CATNAME], "DRAIN"), "Drainage", else [CATNAME]