Forum Discussion
Concatenate IF with OR operator
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)
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 nullBest Regards!
Dale
3 Replies
- v-jiascu-msft
Microsoft Employee
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 nullBest Regards!
Dale
- v-jiascu-msft
Microsoft Employee
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- socialengagedNew Member
Thanks! Fixed, sorry for delay.