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,
My column name say Name contains long text string out of which i want to extract only specfic word so how can I achieve this requirement?
I tried using Search/Find/Contains functions but didn't get the expected result.
Input Column value: Output Column
Experiment_RSA_home_loans_Brand_BMM Home
Experiment_RSA_Home_Loan_Brand_Exact Home
Exp - Core - Phrase - USA - New Ads USA
Any lead would be really appreciated!
Solved! Go to Solution.
hi @Tahreem24
if your looking for a way to check a string against a list of values and return one of those valuse if it exsists in your string then the method below should work
Create a table of values in this case mine is called search_Values
the table should have two colunms the first is the search and the second a numeric id for each value (must be unique)
example below
SearchID
HOME | 1 |
USA | 2 |
then use this colunm to get do your search aginst the values in the table
Column = -- Declare a veriable to hold the id number of search term VAR incl = -- MAXX will check each row of the search values table to see if the value exsists in the input colunm and return the max id MAXX(Search_Values, if( --use find to check if the value in the search values, search colunm is in the string FIND( UPPER(Search_Values[Search]), UPPER('Table'[Input Column]),1,0) -- if the value is in the string then return the id else return blank > 0 ,Search_Values[ID],blank())) -- if several values exist then only the max id will be returned as you can only have one valuein the colunm --use the id value returned above to look up the word and return it var ret = LOOKUPVALUE(Search_Values[Search],Search_Values[ID],incl) -- return search term RETURN ret
Warning - this can only be used to return a single value. if your string had both "HOME" and "USA" then it would only return USA as this is the highest id value
Proud to be a Super User!
hi @Tahreem24
if your looking for a way to check a string against a list of values and return one of those valuse if it exsists in your string then the method below should work
Create a table of values in this case mine is called search_Values
the table should have two colunms the first is the search and the second a numeric id for each value (must be unique)
example below
SearchID
HOME | 1 |
USA | 2 |
then use this colunm to get do your search aginst the values in the table
Column = -- Declare a veriable to hold the id number of search term VAR incl = -- MAXX will check each row of the search values table to see if the value exsists in the input colunm and return the max id MAXX(Search_Values, if( --use find to check if the value in the search values, search colunm is in the string FIND( UPPER(Search_Values[Search]), UPPER('Table'[Input Column]),1,0) -- if the value is in the string then return the id else return blank > 0 ,Search_Values[ID],blank())) -- if several values exist then only the max id will be returned as you can only have one valuein the colunm --use the id value returned above to look up the word and return it var ret = LOOKUPVALUE(Search_Values[Search],Search_Values[ID],incl) -- return search term RETURN ret
Warning - this can only be used to return a single value. if your string had both "HOME" and "USA" then it would only return USA as this is the highest id value
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |