Date Contains "th", "st" etc, how to change to date format

[Anonymous]

Hi, I can't find the code to change a date from "3rd December 2022" to 03/12/2022.  I therefore used multiple wasteful steps below, but I'm sure it's easier than that.  Any help hugely appreciated, thanks

#"Replaced Value" = Table.ReplaceValue(#"Added Custom","w/e ","",Replacer.ReplaceText,{"Output Table.Time"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Output Table.Time", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Output Table.Time.1", "Output Table.Time.2", "Output Table.Time.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Output Table.Time.1", type text}, {"Output Table.Time.2", type text}, {"Output Table.Time.3", Int64.Type}}),
#"Replaced Value1" = Table.ReplaceValue(#"Changed Type1","st","",Replacer.ReplaceText,{"Output Table.Time.1"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","nd","",Replacer.ReplaceText,{"Output Table.Time.1"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","rd","",Replacer.ReplaceText,{"Output Table.Time.1"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","th","",Replacer.ReplaceText,{"Output Table.Time.1"}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Replaced Value4", {{"Output Table.Time.3", type text}}, "en-GB"),{"Output Table.Time.1", "Output Table.Time.2", "Output Table.Time.3"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Bighams Date"),
#"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"Bighams Date", type date}})
in
#"Changed Type2"

[hashtag_pete]

Hello @Anonymous ,

my appoach would be to extract the digits from the day, then combine the rest with it and let Power Query do the rest of the magic. Such as 

=Table.AddColumn(#"Changed Type", "Date new",  each 
        Text.Combine({ 
            Text.Select(Text.Start([Date], 2), {"1".."9"}), //selects digits from the first two values
            " ", 
            Text.AfterDelimiter([Date], " ", 0)  //selects everything after the first delimiter " "
            })
            ),
    #"Parsed Date" = Table.AddColumn(#"Custom Column", "Parse Date", each Date.From(DateTimeZone.From([Date new])), type date)
in
    #"Parsed Date"

if that helps, please give kudos and accept as solution. 

Best

hashtag_pete

[collinsg]

Good day markbighams,

List.Accumulate may be used to reduce the amount of code at the cost of it being a potentially complex function.

The steps are:

1. Bring the data into Power Query.
2. Add a query step to create a list of suffixes.
3. Remove the suffixes from the date using List.Accumulate.
   1. The list of suffixes provides the list you iterate through to “accumulate” a result.
   2. The start of the accumulation is the table you brought in to Power Query.
   3. The accumulation runs through the list of suffixes, for each one it finds and replaces it in the “Date” column of the table.
4. Change the type to date.

I used this data

The result looks like this

My M code is this

let
   Source = Excel.CurrentWorkbook(){[Name="Events"]}[Content],
   #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Location", type text}}),

   #"List of Date Suffixes" = {"th","st","nd","rd"},

   #"Remove Suffixes" = List.Accumulate( 
      #"List of Date Suffixes", 
      #"Changed Type", 
      (table, suffix) => Table.ReplaceValue( table, suffix, "", Replacer.ReplaceText, {"Date"} )
   ),
   #"Change to Date Type" = Table.TransformColumnTypes(#"Remove Suffixes",{{"Date", type date}})
in
   #"Change to Date Type"

Hope that helps. I adapted the code from a solution to a similar problem by Data Zoe.

[Anonymous]

Thank you so much, I'll try this today