Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
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...
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
29 | |
14 | |
11 | |
10 | |
9 |