Group Terms and Count

[kl8818]

Hi - does anyone have a way to automate the grouping of terms and recalculate the count? Below are a few examples of searches, and I would like to have them count for one common term, such as Excel, tableau, communication, agile, etc. I have about 4.5k terms in my spreadsheet, so I was trying to avoid using a search/replace feature as that would require more maintenance. 

[eliasayyy]

in power query (transform data)

add a new custom column

let
    cleanedTerm = Text.Replace(Text.Replace(Text.Replace(Text.Lower([Search Term]), ".", ""), " ", ""), "-", "")
in
if Text.Contains(cleanedTerm,"tableu") then "Tableu"
else if Text.Contains(cleanedTerm,"excel") then "Excel"
else if Text.Contains(cleanedTerm,"powerbi") then "Power BI"
else if if Text.Contains(cleanedTerm,"agile") then "Agile"
else if if Text.Contains(cleanedTerm,"communication") then "Communication"
else 
"Others"

or if you are a premium user in powerbi desktop you can do Text Analysis and it will automatically analyse the rows kinda

[kl8818]

I am receiving this error...

[v-shex-msft]

HI @kl8818 ,

It seems like the formula include some syntax issue with if statement, I fixed those issues and you can try to use following codes if help:

let
    cleanedTerm = Text.Replace(Text.Replace(Text.Replace(Text.Lower([Search Term]), ".", ""), " ", ""), "-", "")
in
if Text.Contains(cleanedTerm,"tableu") then "Tableu"
else if Text.Contains(cleanedTerm,"excel") then "Excel"
else if Text.Contains(cleanedTerm,"powerbi") then "Power BI"
else if Text.Contains(cleanedTerm,"agile") then "Agile"
else if Text.Contains(cleanedTerm,"communication") then "Communication"
else 
"Others"

Regards,

Xiaoxin Sheng