Extract Date from Text String/Power Query

[Yvonne Love]

In our practice, often times we'll include dates and text in a description (09/13/17 SPED, 1-15-09 AUDIO). I am trying to figure out if there is a way in Power Query to extract the date portion of the text string and then format it as a date. The date is not always at the beginning of the text string and is not always "mm/dd/yy" format. I've searched the webs and the forum, though admittedly I may not be using the right search verbiage, but I haven't been able to find anything. Is there a way to do this in Power Query?

Thanks in advance!!
YL

 

[Chihiro]

I'd recommend uploading sample patterns that can occur. Along with expected output.

However, there is no robust mechanism that will extract all date patterns. You can build custom function to handle most common cases though.

 

[Yvonne Love]

Thanks Chihiro. I've uploaded a sample of real-life examples. What I'd like is to extract the date as appearing in column B.

I haven't touched on or used custom functions very much so I will look into that some more.

YL

 

[Yvonne Love]

sorry - I didn't mean to attach it twice. It's the same file.

 

[Chihiro]

Ok, if it's always going to be like what your sample has. Then it's very easy.

Select [DESC] -> Split Column -> By Delimiter.

Then use Space as delimiter and use Left Most.


That's it, if you have system date format set to "mm/dd/yyyy" then it should automatically apply data type change.


Edit: Added screen shots.

 

[Yvonne Love]

Thanks Chihiro. I guess I didn't provide a big enough sample, BUT, this will catch most of them for sure. I have other descriptions with the date at the end, so I will use a combo of steps to extract from the right most when I need to. Thanks for your help!

 

[Yvonne Love]

For reference, here is a better sampling of the differences in description. I would say that most of the dates will be at the beginning or end, which will make it easier to extract, but some are in the middle, and some are spelled out.

 

[Chihiro]

That's going to be significantly more complex. Without use of R-script and PowerBi. I can't think of quick solution. Let me think on it a bit.

Is there a possibility of validating input? Rather than cleaning up output. I tend to enforce validation upfront to avoid headaches down the line.

 

[Yvonne Love]

Thanks Chihiro. We have been consistent the last few years, but I will make notes regarding how to input. I don't think the database we has any kind of built in data validation, so we'll have to have copious notes on it.

Thanks for your help!

 

[Chihiro]

I'm still working on ways to deal with text Month name pattern. (ex. April 20, 2004). But I've got function to extract dates from other patterns (m/dd/yy, mm-dd-yy, m/d/yy, m/d/yyyy etc).

See below function.

(myStr) =>
let
    myStr = Text.Replace(myStr, "- ", " "),
    Split = Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv)(myStr),
    Convert = List.Generate(
                            ()=> [Date=null, x=-1],
                            each [x]<List.Count(Split),
                            each [Date= try Date.From(Split{x}) otherwise null, x=[x]+1],
                            each [Date]),
    myDate = List.RemoveNulls(Convert){0}
in
    myDate

Sample usage and result:


I'll update when I've figured out logic for extracting date with month name and space between date parts.

 

[Chihiro]

Ok here's second function.

(myStr) =>
let
    Split = Splitter.SplitTextByDelimiter(", ")(myStr),
    Year = List.RemoveNulls(
                List.Generate(
                              ()=>[Year=null,x=0],
                              each [x]<List.Count(Split),
                              each [Year= try Number.From(Text.Start(Split{x},4)) otherwise null, x = [x]+1],
                              each [Year])
                            ){0},
    Split2 = Splitter.SplitTextByDelimiter(Number.ToText(Year))(myStr),
    Temp = List.LastN(Splitter.SplitTextByDelimiter(" ")(Text.Trim(Split2{0})), 2),
    myDate = Date.From(Text.Combine(Temp, " ") & " " & Number.ToText(Year))
in
    myDate

You could combine the two, but I kept it separate for ease of maintenance/use.

See attached sample for how it's used.

 

[Yvonne Love]

Wow. Thanks so much Chihiro! Above and beyond!!

 

[GraH - Guido]

@Chihiro rocks!

 

[Purna Chandra]

Hi Chihiro,

That was excellent

In my case I have variable dates and formats, I have to pull start date & end date in to 2 columns
could you help me out

 

[shaikhrulez]

Hello @Chihiro

I have come across somewhat similar problem with longer text length and different date formats which is manually entered, need null where there is no date. I tried to copy your solution in my scenario but it didn't work perhaps due to my limited knowledge of Power Query.

Could you please look into the file attached and assist me in extracting dates from last comment column. The actual data contains more than 100K rows.

Thanks.

 

[vletm]

shaikhrulez
You should open own thread for Your challenge.
This thread belongs to Yvonne Love