Extract Date from String

[jcawley]

Good afternoon BI wizards,

I’m having a problem in the power query editor!

I have a column of text, that I want to pull a date string out of only. The data isn’t always the same either. The column in question looks something like this:

Message
DOS 04/20/22 received
DOS 03/21/21]] billed blah blah
DOS N/A] billed
DOS 03/21/21 billed] billed

Now what I want is the column to exclude anything other than a possible date, if anything. What I would want returned would be:|

Message
04/20/22
03/21/21
null
03/21/21

Any help would be greatly appreciated!

[tackytechtom · Accepted solution]

Hi @jcawley ,

Here a possible solution:

Here the M code that you can use in the advanced editor:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvEPVjAw0Tcy0DcyUihKTU7NLEtNUYrVgcoY6xsZAlFsrEJSZk5OaopCUk5iBpiAq/HTd4TJYuiDiiPkYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Datum = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Datum", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Datum.1", "Datum.2", "Datum.3"}),
#"Keep Value1" = Table.ReplaceValue(#"Split Column by Delimiter", each [Datum.1], each Text.Select ( [Datum.1], {"0".."9"} ),Replacer.ReplaceValue,{"Datum.1"}),
#"Keep Value2" = Table.ReplaceValue(#"Keep Value1", each [Datum.2], each Text.Select ( [Datum.2], {"0".."9"} ),Replacer.ReplaceValue,{"Datum.2"}),
#"Keep Value3" = Table.ReplaceValue(#"Keep Value2", each [Datum.3], each Text.Select ( [Datum.3], {"0".."9"} ),Replacer.ReplaceValue,{"Datum.3"}),
#"Changed Type" = Table.TransformColumnTypes(#"Keep Value3",{{"Datum.1", Int64.Type}, {"Datum.2", Int64.Type}, {"Datum.3", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each if [Datum.1] = null then null else #date([Datum.3] + 2000, [Datum.1], [Datum.2] )),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Datum.1", "Datum.2", "Datum.3"})
in
#"Removed Columns"

It is hard to say, whether this solution takes care of all the ifs and buts in the messy data. I have the feeling that the four example do not cover all of the eventualities, but it might get you there anyway 🙂

Another cool and powerful feature is the "column from example" feature:

Here you can tell PBI what you expect and it tries to create a logic according to your examples. It works surprisingly well actually.

Hope this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/