Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
kl8818
Frequent Visitor

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. 

 

kl8818_0-1689088735510.png

 

kl8818_1-1689088798577.png

 

kl8818_2-1689088857868.png

 

kl8818_3-1689088923216.png

 

 

3 REPLIES 3
eliasayyy
Memorable Member
Memorable Member

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

I am receiving this error...

 

kl8818_0-1689360033620.png

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.