Converting "DD/MM/YYYY" & "MM/DD/YYYY" date Type to DD/MM/YYYY

[poojashribanger]

Hi All,

I am trying to convert 2 date type to an single date type but i am getting an issue .

I have a week column like below from which i split the column and extract the weekstart date .

I am getting correct output for everything other than  for 4th Month.

Expression used for custom column:

try Date.FromText( [WeekStartDate], [Format = "MM/dd/yyyy" ] ) otherwise
Date.FromText( [WeekStartDate], [Format = "dd/MM/yyyy" ])

My week column has 2 different format.

MM/DD/YYYY	01/01/2024 - 01/07/2024
MM/DD/YYYY	01/08/2024 - 01/14/2024
MM/DD/YYYY	01/15/2024 - 01/21/2024
MM/DD/YYYY	01/22/2024 - 01/28/2024
MM/DD/YYYY	01/29/2024 - 02/04/2024
MM/DD/YYYY	02/05/2024 - 02/09/2024
MM/DD/YYYY	02/12/2024 - 02/18/2024
MM/DD/YYYY	02/19/2024 - 02/25/2024
MM/DD/YYYY	02/26/2024 - 03/03/2024
DD/MM/YYYY	04/03/2024 - 10/03/2024
DD/MM/YYYY	11/03/2024 - 17/03/2024
DD/MM/YYYY	18/03/2024 - 24/03/2024
DD/MM/YYYY	25/03/2024 - 31/03/2024
DD/MM/YYYY	01/04/2024 - 07/04/2024
DD/MM/YYYY	08/04/2024 - 14/04/2024
DD/MM/YYYY	15/04/2024 - 21/04/2024
DD/MM/YYYY	22/04/2024 - 28/04/2024
DD/MM/YYYY	29/04/2024 - 05/05/2024

Can someone please help me on this.

Thanks & Regards,

Poojashri

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solution @lbendlin  provided and i want to offer some more information for user to refer to.

hello @poojashribanger  based on your description, you can add a format column like the sample data you have offered "MM/DD/YYYY" or "DD/MM/YYYY", you can named it 'DateFormat' column, then based on this column you can add a index column first.

2.Add a custom column

=let a=Table.SelectRows(#"Added Index",(x)=>x[Index]=[Index]),
b=if [DateFormat]="DD/MM/YYYY" then Table.TransformColumnTypes(a, {{"WeekStartDate", type date}}, "en-GB") else Table.TransformColumnTypes(a, {{"WeekStartDate", type date}}, "en-US")
in b[WeekStartDate]{0}

Output

You can refer to the following m code in advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdJNDoMgEAXgqxjWNsN7QsW9Ww/QGO9/jU7bgGPqQNiQ+ZL5YfY9bJusq7z0hDFESCzCyDQ8Bn0gfR/hGP8hsoGED0kLSwcuDVKim1pj2cLFh6CBcFNrzKZm9iGfDU6it0FVaitMNaYQ0YeAhXMHFgOZfKjFn3BCp0bUGX+amS8Dv8JioG6EC3UjTkj4kLSwdOBia8z142+3J8Ksmfbzg8cb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateFormat = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateFormat", type text}, {"Column2", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column2", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"Column2.1", "Column2.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column2.1", type text}, {"Column2.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column2.1", "WeekStartDate"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns"," ","",Replacer.ReplaceText,{"WeekStartDate"}),
    #"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let a=Table.SelectRows(#"Added Index",(x)=>x[Index]=[Index]),
b=if [DateFormat]="DD/MM/YYYY" then Table.TransformColumnTypes(a, {{"WeekStartDate", type date}}, "en-GB") else Table.TransformColumnTypes(a, {{"WeekStartDate", type date}}, "en-US")
in b[WeekStartDate]{0})
in
    #"Added Custom"

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.

[poojashribanger]

Thanks ,

I tweaked a bit according to my requirement and it worked.

[lbendlin]

That is impossible as you cannot disambiguate between 05/06 and 06/05  etc.  You need to have some other flag that can indicate which is which.

If you really have date ranges like in your example then you could monitor if the difference between the first two digits is 7 or not. If it is the your format is DD/MM, otherwise it is MM/DD.  This will need to be tweaked for weeks stretching over two months.  Instead of 7 you can do a cutoff at 2.