Forum Discussion
Extracting Specific Text from a String Type Column
I have a text string column in my dataset that contains random strings. However, some of the records contain location information within the strings. An example of this is: EU-Romania- Server and they are often in an arbitrary order, for instance the next record with location information maybe SS-B-EU.
If I wanted to extract "EU" from the records that contain "EU" in this field, what would I do?
Anonymous I don't why wouldn't post these rules in your original post, how someone will get to know what to do. Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Anyhow, add new column with following expression.
MyRegion = SWITCH ( TRUE(), SEARCH ( "EU", Table[Data], , -1 ) >= 0, "EU", SEARCH ( "Name", Table[Data], , -1) = -1 && SEARCH ( "NA", Table[Data], , -1 ) >= 0, "NA", SEARCH ( "APAC", Table[Data], , -1 ) >= 0, "APAC", "" )
13 Replies
Anonymous do you want to extract only EU value where available otherwise return empty. Is this correct?
Anonymous do you have list of locations you want to search, if not then what would be business rule to identify that it is a location. You have to provide more information.
Hi Anonymous ,
Do you want the result like this image?
Column = VAR a = SEARCH ( "EU", 'Table'[Column1], 1, 0 ) RETURN IF ( a <> 0, "EU", 'Table'[Column1] )Anonymous I don't why wouldn't post these rules in your original post, how someone will get to know what to do. Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Anyhow, add new column with following expression.
MyRegion = SWITCH ( TRUE(), SEARCH ( "EU", Table[Data], , -1 ) >= 0, "EU", SEARCH ( "Name", Table[Data], , -1) = -1 && SEARCH ( "NA", Table[Data], , -1 ) >= 0, "NA", SEARCH ( "APAC", Table[Data], , -1 ) >= 0, "APAC", "" )
