Extract Date from Text in Column

[ABC11]

Hello,

In power Query- I would like to extract date from Source.Name column  in Lasest date column.

Please help me here

Source.Name	Lastest Date
HZN All Last 7 - AM - 10-27-2024 05_31 AM	10/27/2024
HZN All Last 7 - AM - 10-28-2024 05_31 AM	10/28/2024
HZN All Last 7 - AM - 10-29-2024 05_31 AM	10/29/2024
HZN All Last 7 - AM - 10-30-2024 05_31 AM	10/30/2024
HZN All Last 7 - AM - 10-31-2024 05_31 AM	10/31/2024
HZN All Last 7 - AM-2024-11-12	11/12/2024
HZN All Last 7 - AM-2024-11-13	11/13/2024
HZN All Last 7 - AM-2024-11-14	11/14/2024
HZN All Last 7 - AM-2024-11-15	11/15/2024
HZN All Last 7 - AM-2024-11-16	11/16/2024
HZN All Last 7 - AM-2024-11-17	11/17/2024
HZN All Last 7 - AM-2024-11-18	11/18/2024
HZN All Last 7 - AM-2024-11-19	11/19/2024

Thanks

ABC11

[ThxAlot]

Easy enough,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lcoxDoAgDEDRqzTMNmkLCIxuDuoBJMS4s+n9I/EAVpY//LyczbxvMNUKy3ndEABhWluYUAIKiQPyh+V2TRm+dOzSqUdb6tL8T78KmZFFJ1YnTideJ6NOgk6iTpIp5QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t]),
    #"Extracted Date" = Table.AddColumn(Source, "Date", each Date.From(Text.BetweenDelimiters([Source.Name],"-"," ",1,1)))
in
    #"Extracted Date"

[Omid_Motamedise]

Hi @ABC11 

Check this solution

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lcoxDoAgDEDRqzTMNmkLCIxuDuoBJMS4s+n9I/EAVpY//LyczbxvMNUKy3ndEABhWluYUAIKiQPyh+V2TRm+dOzSqUdb6tL8T78KmZFFJ1YnTideJ6NOgk6iTpIp5QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ource.Name = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.RemoveNulls(List.Transform(Text.Split([ource.Name]," "), (x)=> try Date.From(Text.Replace(x,"AM-","")) otherwise null)){0})
in
    #"Added Custom"

[ABC11 · Accepted solution]

Hello Omid_Motamedise,

It working but when I click Table. All other column disappear. what would be the solution?

Please

Thanks

[ABC11]

Hello Greg,

Its didn't worked becuase it remove my whole data set.

I have really big data set with 48 column and thousands of row- all those data disappear.

Thanks

Please

[Greg_Deckler]

@ABC11 Here is one way:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdI7CsMwDIDhqwjPMdbDju0xW4a0B2gIpXu29v40KSpZjNGiQXz/JK2rmx93mPYdltf7Axk8TLdjEHrOnpEjYHoKHVs3OMLAOZxbtw29tDTTYklrM62GVLCVClpSaqbUTX+JJ/LEp6dAbPSiXow+qo9Gn9Qnox/Vj0af1fd/4fJFff8BLl/V/6++fQE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t, #"Lastest Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source.Name", type text}, {"Lastest Date", type date}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Source.Name", Splitter.SplitTextByDelimiter("AM - ", QuoteStyle.Csv), {"Source.Name.1", "Source.Name.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Source.Name.1", type text}, {"Source.Name.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Source.Name.1", Splitter.SplitTextByDelimiter("AM-", QuoteStyle.Csv), {"Source.Name.1.1", "Source.Name.1.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Source.Name.1.1", type text}, {"Source.Name.1.2", type date}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "Source.Name.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Source.Name.2.1", "Source.Name.2.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Source.Name.2.1", type date}, {"Source.Name.2.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type3",{"Lastest Date"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Latest Date", each if [Source.Name.2.1] = null then [Source.Name.1.2] else [Source.Name.2.1]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Source.Name.1.2", "Source.Name.2.1", "Source.Name.2.2"})
in
    #"Removed Columns1"