Forum Discussion
Free text date column correction
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 b...
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:
Hi Anonymous
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.
