Extract date from a text field

[Anonymous]

Hi All,

Is it possible to extract date from a text column which looks like this:

Nurcan Türkan- 21.08.1986
18.01.1988
09.08.1965
M1 28.02.1987
M2 11.04.1987
31.12.1986
11.09.1994
21.04.1995
M1 18.07.1992
M2 12.05.1993
Fr.09.03.82/ Hr.1.11.79
18.05.1993
01.05.1971 (Herr
02.02.1976 (Frau)
25.04.1993
26.07.1980
29.04.1973
Rube- 29.11.1970/ Hofmann- 02.05.1967
Ruzana B- 04.6.75/ Vjaceslav B. 24.9.71
13.03.1971 (Frau Schafft)
26.01.1971 (Herr Anderle)
Lück 22.11.1988/ Lakatos 25.01.1990
Fr. Pütz 25.07.1991/ Hr.Meise 02.04.1991
15.10.1993
09.06.1971
06.05.1974 in Slawgorod
02.07.1967
Fr. L.  21.06.1988/ Hr. K. 11.08.1987
12.07.1990
15.01.1980
23.02.1963
03.09.1965
Fr. 27.03.1962/ Hr. 24.06.1974
10.05.1991
19.12.1950
Fr. 20.06.1942/ Hr. 10.04.1949

I want another column with the date values from the given set. Places where we have 2 date values, it's okay to extract first value only. 

[v-yuta-msft · Accepted solution]

Hi AS31,

I'm afraid power bi can't implement too complex string matching, so you should write a regular expression using R Script to achieve the date you want. Install R package "stringr" and implement R Script as below:

# 'dataset' holds the input data for this script
library(stringr)
pattern <- "\\d+\\.\\d+\\.\\d+"
dataset$CleanedDate <- str_extract(dataset$Date, pattern)
output <- dataset

The result is like this:

 

Regards,

Jimmy Tao

[Anonymous]

Hi @v-yuta-msft Jimmy, Thank you for your response. I will surely give it a try.

regards,

Ashu