Extracting Specific Text from a String Type Column

[Anonymous]

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]

Yes so I want "NA" when it just says "NA" and isn't part of another word such as "name".

[parry2k · Accepted solution]

@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/38490

Anyhow, 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",
    ""
)

[Anonymous]

Thanks!

[v-eachen-msft]

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] )

[parry2k]

@Anonymous do you want to extract only EU value where available otherwise return empty. Is this correct?

[Anonymous]

Hi,

Thanks for getting back - "EU" and some other location values as well. Any idea as to how I might be able to do it?

[parry2k]

@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.

[Anonymous]

 I am looking for "EU", "NA" and "APAC" between the texts -  I need the results in a single column. Please note, that I also have other values in this column that might contain this character (for example, Default-First-Name, contains "NA").

Thanks for your help!

[parry2k]

@Anonymous can you paste sample data here in a table?

[Anonymous]

I'll paste some sample values from this column:

Default-First-Site-Name

Azure-EU- North Europe ( I need EU from this value)

none

FR-Telecity

FIT

DE_MUC

TX95C

AWS-NA-East (I need just NA from this value)

[parry2k]

@Anonymous is this the only value from which you don't want "NA" to extract? 

[Anonymous]

There are around 70,0000 rows of data in this dataset so there maybe other values that contain "na" in the value.

[parry2k]

@Anonymous that raised the question what would be the logic to ignore not to extract "NA" , you have to have some logic, isn't it?