How to extract first string after first numeric values in DAX - Power BI

Question

I have a column with values like below in my power BI table:

14   Madrid
1409 Moscow - TC
1510 Millan - FGHM
1405 London - XXXXX- YYYYY

You will observe that the first string is a numeric value followed by a space. The second string is a city and that's what i want to extract as a new column. Am not interested in what ever comes after the city.

Any one have an idea of which DAX formula i can use to achieve this?

The expected values in my new calculated column would be as below:

Madrid
Moscow 
Millan 
London

Alexis Olson Alexis Olson · Accepted Answer · 2018-02-15T18:00:34

Assuming that if anything follows the city name there is a dash included, you could try a calculated column like this:

City = 
 VAR FirstSpace = FIND(" ", Table[Column1])
 VAR Dash = FIND("-", Table[Column1], 1, LEN(Table[Column1]) + 2)
 RETURN MID(Table[Column1], FirstSpace, Dash - FirstSpace - 1)

How to extract first string after first numeric values in DAX - Power BI

1 Answers