Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
hello everyone!
im almost new with powerbi, and i find it a great tool! I have an issue thou, I hope you can help me find out a solution:
when i create a Custom Query -> Custom Column, I add the below formula to create a Custom.Topic column:
= if Text.Contains([Custom], "cloud", Comparer.OrdinalIgnoreCase) then "Cloud"
else if Text.Contains([Custom], "nuvola", Comparer.OrdinalIgnoreCase) then "Cloud"
else null
This formula basically analizes the [Custom] column text and if it finds the word "cloud" it adds Cloud in the new column.
Now I want to add different words to the new column, so that
IF it finds the word "cloud" or "computing" it adds "Cloud" to the new column, CONCATENATE "|", OR IF it finds the word "analytics" or "data" it adds the word "Analytics" to the new column (separating both words with a pipe)
I tried with this formula:
= (if Text.Contains([Custom], "cloud", Comparer.OrdinalIgnoreCase) then "Cloud"
else if Text.Contains([Custom], "nuvola", Comparer.OrdinalIgnoreCase) then "Cloud"
else null)&(if Text.Contains([Custom], "analytics", Comparer.OrdinalIgnoreCase) then "Analytics"
else if Text.Contains([Custom], "dati", Comparer.OrdinalIgnoreCase) then "Analytics"
else null)
But it basically concatenate the 2 words only when it finds both. So the formula does not work. Do you know how I can obtain the below wanted output (it's an example)?
Custom.Topic (Column)
Cloud
Analytics
Cloud|Analytics
Cloud
Cloud|Analytics
Thanks a lot for help!
Eugenio (SEO)
Solved! Go to Solution.
Hi @socialengaged,
Try this formula please.
=if (Text.Contains([Words], "cloud") or Text.Contains([Words], "computing")) and (Text.Contains([Words], "analytics") or Text.Contains([Words], "data") ) then "Cloud|Analytics" else if (Text.Contains([Words], "cloud") or Text.Contains([Words], "computing")) and not (Text.Contains([Words], "analytics") or Text.Contains([Words], "data") ) then "Cloud" else if not (Text.Contains([Words], "cloud") or Text.Contains([Words], "computing")) and (Text.Contains([Words], "analytics") or Text.Contains([Words], "data") ) then "Analytics" else null
Best Regards!
Dale
Hi @socialengaged,
Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.
Best Regards!
Dale
Thanks! Fixed, sorry for delay.
Hi @socialengaged,
Try this formula please.
=if (Text.Contains([Words], "cloud") or Text.Contains([Words], "computing")) and (Text.Contains([Words], "analytics") or Text.Contains([Words], "data") ) then "Cloud|Analytics" else if (Text.Contains([Words], "cloud") or Text.Contains([Words], "computing")) and not (Text.Contains([Words], "analytics") or Text.Contains([Words], "data") ) then "Cloud" else if not (Text.Contains([Words], "cloud") or Text.Contains([Words], "computing")) and (Text.Contains([Words], "analytics") or Text.Contains([Words], "data") ) then "Analytics" else null
Best Regards!
Dale
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |