Adding a column label based on text value

[jwyoung07]

Hello, I hope my question is an easy one. I've been poking around the forums and reviewing documentation but can't wrap my head around the best way to do this: I have a list of names for items, and want to label these items based on their name. These items use a naming convention where the beginning of the name indicates what group they belong to. I want to add a column with these groups. Take the data below:

Line 1: the item name begins with 'Red-' so this indicates it belongs in the 'Winners' category. My 'Item Name' is being pulled automatically from a resource. I want to add 'Category' so I can easily splice these items. 

I have a feeling it will be a variation of @Greg_Deckler 's solution for Attendance, posted here: Solving Attendance with the Disconnected Table Tri... - Microsoft Fabric Community but can't seem to connect the dots. I'd be happy to provide more info if needed. Thank you!

[jwyoung07 · Accepted solution]

Update for all: I was able to find a working solution to the problem: a (very long) conditional column: 

The result is a very ugly string of if statements in a single Power Query, but it accomplished exactly what I was trying to do. Thank you!

[mlsx4]

Hi @jwyoung07 

I'm not sure if I have understood it correctly. But, I think you can create a new custom column:

Name Category = 

var searchCat = 
LEFT(
    'MyTable'[Item],
    SEARCH(
        "-", 
        MyTable[Item],
        ,
        LEN(MyTable[Item]) + 1
    ) - 1
)

return SWITCH(searchCat,
"Red","Winners",
"Blue","Losers",
"Yellow","Participators",
"Purple","Try-Hards",
"No category")

I hope it works!

[jwyoung07]

Thanks so much for your quick response @mlsx4 ! Pardon my ignorance, but I'm getting a Token Eof error when making this column: 

I'm sure I'm missing something very simple. Please advise, thank you so much!

[mlsx4]

Hi @jwyoung07 

I thought you need it in DAX, so that's why my formula isn't working on M. In M, the solution is the one you has done. I'm glad you finally solved your problem