Free text date column correction

[JSPQ]

Hi All,
I have a report that has a free text arrival date column that has a lot of inconsistent date formats. I'm looking to change the all to dd/mm/yyyy, some of them don’t have the year included but can assume its current year or next year (if before the current date by more than a month as some arrivals are late). Is there a way that doesn’t use list to replace, as i have found that slow to convert varying format dates? I do have access to another date column that it the request delivery date that is not free form but can vary up to 5-6 months.

Estimated Arrival Date

SEPT.29

AUG.18

27/05

FEB .19

27/05

05.11.19

JUNE .27

MAY.2

29/04/2023

11-APR

02.26.2023

[ronrsnfld · Accepted solution]

Here's one method.

The transforms I did should be clear in the code.

I assumed that there would never be an entry with just Month and Year

I also assumed that if a date was ambiguous, it would be interpreted as "DMY".

The routine converts all of your dates to real dates. How they are displayed will depend on your Windows Regional Settings in Power Query, and on how you set the date formatting in Power BI (or Excel).

If you need them displayed as DMY in Power Query, you may need to convert them to text strings as otherwise the display in PQ will be dependent on the windows regional settings of the computer. But then they won't be "real dates".

let
    Source = Excel.CurrentWorkbook(){[Name="Dates"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Estimated Arrival Date", type text}}),
    #"Normalize Dates" = Table.AddColumn(#"Changed Type", "Normalized Dates", (c)=> 
        let 
            split = Text.SplitAny(c[Estimated Arrival Date],"./-"),
            addYear = if List.Count(split) = 2 then split & {DateTime.ToText(DateTime.FixedLocalNow(),"yyyy")} else split,
            trim = List.Transform(addYear, each Text.Trim(_)),
            #"3 Max" = List.Transform(trim, each 
                if List.ContainsAll({"A".."Z"},Text.ToList(_),Comparer.OrdinalIgnoreCase)
                then Text.Start(_,3) else _),
            dt = try Date.From(Text.Combine( #"3 Max","-"),"en-150") otherwise Date.From(Text.Combine( #"3 Max","-"),"en-US")

        in dt, type date),
    #"Removed Columns" = Table.RemoveColumns(#"Normalize Dates",{"Estimated Arrival Date"})
in
    #"Removed Columns"

It converts all the dates in your example:

[v-xinruzhu-msft]

Hi @JSPQ 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYtBCoAwDAS/UnrWNInW2mOFKghKUXqQ4v+/ISR4m2F3WrN3LsDRvl2zqW5AsyAHh15ozYsB0gN6IPplr2c2wEHkSA+wltHh6Bh5ECXqU7m0ZuAJdHk/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Estimated Arrival Date" = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"-",".",Replacer.ReplaceText,{"Estimated Arrival Date"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each (List.Count(List.Intersect({Text.ToList([Estimated Arrival Date]),{"A".."Z"}}))>0)),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Estimated Arrival Date", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Estimated Arrival Date.1", "Estimated Arrival Date.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Estimated Arrival Date.1", type text}, {"Estimated Arrival Date.2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Value.Is(Value.FromText([Estimated Arrival Date.1]),type number) then [Estimated Arrival Date.2] else [Estimated Arrival Date.1]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if Value.Is(Value.FromText([Estimated Arrival Date.2]),type number) then [Estimated Arrival Date.2] else [Estimated Arrival Date.1]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Estimated Arrival Date.1", "Estimated Arrival Date.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"Custom.1", "Custom"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Estimated Arrival Date"),
    Custom1 = Table.SelectRows(#"Replaced Value", each (List.Count(List.Intersect({Text.ToList([Estimated Arrival Date]),{"A".."Z"}}))=0)),
    #"Appended Query" = Table.Combine({Custom1, #"Merged Columns"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Appended Query", "Estimated Arrival Date", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Estimated Arrival Date.1", "Estimated Arrival Date.2", "Estimated Arrival Date.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Estimated Arrival Date.1", type text}, {"Estimated Arrival Date.2", Int64.Type}, {"Estimated Arrival Date.3", Int64.Type}}),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Estimated Arrival Date.2", type text}, {"Estimated Arrival Date.3", type text}}, "en-US"),{"Estimated Arrival Date.2", "Estimated Arrival Date.1", "Estimated Arrival Date.3"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Merged Columns1", {{"Merged", type date}}, "en-GB")
in
    #"Changed Type with Locale"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mussaenda]

Hi @v-xinruzhu-msft ,

The first row is SEPT.29

I think you tested SEP.29

[v-xinruzhu-msft]

Hi @mussaenda 

Yes, just replace the SEPT with SEP.

Best Regards!

Yolo Zhu

[ronrsnfld]

Not only are the dates in different formats, but they are also ambiguous. You need to provide a matching list of what you expect the dates to actually be.

For example, is Aug. 18  1-Aug-2018 or is it 18-Aug-current year?

In addition to providing a translation table for your current EAD column, also:

Since you have dates in both MDY and DMY format, should a date like 4/7/2023 be interpreted as 4-Jul-2023 or 7-Apr-2023?

[spinfuzer]

Add Custom Column =Date.From([column]) and see what you end up with