Forum Discussion

kl8818's avatar
kl8818
Frequent Visitor
3 years ago

Group Terms and Count

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, ...
eliasayyy's avatar
eliasayyy
Icon for Memorable Member rankMemorable Member
3 years ago

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's avatar
    kl8818
    Frequent Visitor
    3 years ago

    I am receiving this error...

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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