Mix of US/Uk Date/ Date Time data

[amirghaderi]

I have a data which is the mix of US/UK Date and Date/ Time format. Any idea how I can get all get stored as date format?

21/05/2020

21/10/2019

26/02/2020

21/10/2019

2/08/2022

27/07/2022

2/08/2021

24/07/2022

29/10/2021

30/09/2021

7/12/2021

23/05/2022

23/11/2021

24/07/2021

28/05/2022

17/10/2021

3/21/2022 7:54:00 AM

17/07/2021

21/11/2021

4/10/2023 8:19:00 AM

3/21/2022 8:00:00 AM

3/21/2022 8:00:00 AM

3/21/2022 8:00:00 AM

8/2/2022 4:30:00 PM

7/20/2022 4:30:00 PM

6/1/2022 11:30:00 PM

4/9/2022 4:30:00 PM

7/25/2022 4:30:00 PM

4/8/2022 4:30:00 PM

5/30/2022 9:30:00 PM

6/16/2022 4:24:00 PM

[m_dekorte]

Hi @amirghaderi,

Sure there are ways to transform this into proper dates but does each record also contain a field from which you can deduct it's origin (UK or US)? If not, how do you plan to deal with dates that can convert both ways for example: 1/2/2022 should that be Jan 2nd or Feb 1st? 

[amirghaderi]

Anything with AM/PM has USformat and anything without AM/PM has UK format. 

[m_dekorte · Accepted solution]

Hi @amirghaderi,

I trust this will resolve it for you, copy the full script into a new blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZBJDsMgDEWvgrKu5AEz7nKASN1Huf81SsEooJJVl+8/+xtxnhsToANGxu16VSQsSKmhB+RnCxi/khsFwDCgSmoks0ytSK1FwHRjAOJh1eoLuSPRolgxTsMU5jvAdZVNyE4yotmPPje10HRDtMSamCkNa3dfLOlfovxVyyXbmr8P/QvGpfCgRUSzEEhPTW4pBOIyd2D1dPo57fsGSxfXBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    SetDateType = Table.TransformColumns( Source,
        {
            {"Date", each 
                [ 
                    d = Splitter.SplitTextByWhitespace()(_), 
                    r = try if Text.Length( d{2}?? "" ) >1 then Date.FromText( d{0}, [Culture="en-US"]) else null 
                        otherwise Date.FromText( d{0}, [Culture="en-GB"])
                ][r], type date
            }
        })
in
    SetDateType

The code above is splitting the date value and sees if there is a third list item (meaning: AM, PM)

if so it converts to en-US else en-GB

Ps. If this helps solve your query please mark this post as Solution, thanks!