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, 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. 

 

 

 

 

 

 

3 Replies

  • 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