Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply

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

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.

 

poojashribanger_0-1716197424733.png

 

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/YYYY01/01/2024 - 01/07/2024
MM/DD/YYYY01/08/2024 - 01/14/2024
MM/DD/YYYY01/15/2024 - 01/21/2024
MM/DD/YYYY01/22/2024 - 01/28/2024
MM/DD/YYYY01/29/2024 - 02/04/2024
MM/DD/YYYY02/05/2024 - 02/09/2024
MM/DD/YYYY02/12/2024 - 02/18/2024
MM/DD/YYYY02/19/2024 - 02/25/2024
MM/DD/YYYY02/26/2024 - 03/03/2024
DD/MM/YYYY04/03/2024 - 10/03/2024
DD/MM/YYYY11/03/2024 - 17/03/2024
DD/MM/YYYY18/03/2024 - 24/03/2024
DD/MM/YYYY25/03/2024 - 31/03/2024
DD/MM/YYYY01/04/2024 - 07/04/2024
DD/MM/YYYY08/04/2024 - 14/04/2024
DD/MM/YYYY15/04/2024 - 21/04/2024
DD/MM/YYYY22/04/2024 - 28/04/2024
DD/MM/YYYY29/04/2024 - 05/05/2024

 

Can someone please help me on this.

 

Thanks & Regards,

Poojashri

1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

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.

vxinruzhumsft_0-1716261043342.png

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}

vxinruzhumsft_1-1716261104485.png

Output

vxinruzhumsft_2-1716261127823.png

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.

 

View solution in original post

3 REPLIES 3
v-xinruzhu-msft
Community Support
Community Support

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.

vxinruzhumsft_0-1716261043342.png

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}

vxinruzhumsft_1-1716261104485.png

Output

vxinruzhumsft_2-1716261127823.png

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.

 

Thanks ,

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

lbendlin
Super User
Super User

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors