Forum Discussion
Extract date from a text field
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.
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
2 Replies
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