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.
Hi
I've got a column listed as below and I'm trying to create a new column (with a Yes/No or True/False response) if some matches of certain keywords are found in the original column. Would "CONTAINS" be useful here? or should I create a new column with my Keywords [CEO, Chief Executive, Officer, CFO, Chief Financial Officer, Ambassador] and use Search?
Original column | New conditional column |
Mr John is the CEO of x | Yes |
Ms Lynn is the Vice president of Y | No |
Tom is the ambassador of USA to the UK | Yes |
Jerry is the Company Secretary of X Inc | No |
Keywords |
Ambassador |
CEO |
Chief Executive Officer |
Chief Financial Officer |
CFO |
Solved! Go to Solution.
@Wahid777 You can use CONTAINSSTRING:
Column =
CONTAINSSTRING ( Wahid[Original column], "Ambassador" )
|| CONTAINSSTRING ( Wahid[Original column], "CEO" )
|| CONTAINSSTRING ( Wahid[Original column], "Chief Executive Officer" )
|| CONTAINSSTRING ( Wahid[Original column], "Chief Financial Officer" )
|| CONTAINSSTRING ( Wahid[Original column], "CFO" )
Hi @Wahid777 ,
You could refer to Finding if a value in table 1 has a matching value in table 2.
You need a Keywords table by entering and there is no relationship between two tables.
Column 3 =
IF (
SUMX (
'Keywords',
FIND ( UPPER ( 'Keywords'[Keywords] ), UPPER ( [Original column] ),, 0 )
) > 0,
"True",
"False"
)
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Wahid777 You can use CONTAINSSTRING:
Column =
CONTAINSSTRING ( Wahid[Original column], "Ambassador" )
|| CONTAINSSTRING ( Wahid[Original column], "CEO" )
|| CONTAINSSTRING ( Wahid[Original column], "Chief Executive Officer" )
|| CONTAINSSTRING ( Wahid[Original column], "Chief Financial Officer" )
|| CONTAINSSTRING ( Wahid[Original column], "CFO" )
@Wahid777 I think you can go with the solution provided by @AntrikshSharma if you can't make changes to the alrady captured data. However, as a data analyst it is always recomended to have a clean data in the database itself. I suggest if possible please create a seperate column in your data itself for the position. For Example CEO or Chief Executive Officer are the same position. So ideally you would like to have one keyword for similar positions etc.
Proud to be a Super User!
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |