topic Re: Group Terms and Count in DAX Commands and Tips

Group Terms and Count

kl8818 — Tue, 11 Jul 2023 15:24:44 GMT

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.

Re: Group Terms and Count

eliasayyy — Wed, 12 Jul 2023 11:00:48 GMT

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

Re: Group Terms and Count

kl8818 — Fri, 14 Jul 2023 18:40:51 GMT

I am receiving this error...

Re: Group Terms and Count

Anonymous — Fri, 21 Jul 2023 07:21:20 GMT

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