Forum Discussion
Extract Date from String
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!
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/
1 Reply
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/