Need to convert US DateTime to AU DateTime

[onedayover]

Hi All

I've been given a large dataset with a mixture of text datetime fields to clean up in power query.

Rows that have the AM/PM suffix are the US dates/times (GMT -7 hours) and are in 12 hour format, e.g. 4/29/2022, 3:55:01 PM

Rows without the AM/PM suffix are the AU AEST dates/times (GMT +10 hours) and are in 24 hour format, e.g. 21/02/2022, 09:59:13

I need to convert all the US date/time rows to the same time zone as the AU dates. I then need to format all dates as dd/mm/yyyy hh:mm:ss. 

I've placed an example of the what the data looks like below and would appreciate any help/advice in how I might be able to do this.

Thanks 🙂

Datetime
21/02/2022, 09:59:13
03/08/2022, 15:26:17
18/10/2022, 10:56:45
19/05/2022, 11:03:44
10/13/2022, 11:05:10 AM
4/29/2022, 3:55:01 PM
7/27/2022, 11:02:56 AM
7/27/2022, 9:05:12 AM
7/27/2022, 10:59:00 PM
31/03/2022, 14:01:12
18/03/2022, 10:56:53
19/05/2022, 10:59:35
28/04/2022, 15:00:42

[AnkitKukreja · Accepted solution]

Hi @onedayover 

Please use the below m-code to see how this can be achieved. I've done this on your sample data.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdBLDoMwDATQqyDWSB7/gHjXA1TqPuL+16ibkILUrp9mnEmtszBBSCCyTCjhJVjnY6kzlLCfwB6yBm8NeCfGAISvYd6hEHwABzTMOoBYb+DBmB7PZkZSTtJwD/D06rKRbLeQ5KURulFpdfJHPm9LxejTXPp9heWhjI1BF7RBrr+DWpv2pZIJu/4GCMuq4w0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Datetime = _t]),
#"Duplicated Column" = Table.DuplicateColumn(Source, "Datetime", "Datetime - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Datetime", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"Datetime.1", "Datetime.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Datetime.2", "Time"}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Renamed Columns", "Datetime.1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Datetime.1.1", "Datetime.1.2", "Datetime.1.3"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Datetime.1.1", Int64.Type}, {"Datetime.1.2", Int64.Type}, {"Datetime.1.3", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Month", each if ( [Datetime.1.1] <=12 ) then [Datetime.1.1]
else [Datetime.1.2]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Date", each if [Datetime.1.1] > 12 then [Datetime.1.1] else [Datetime.1.2]),
#"Renamed Columns1" = Table.RenameColumns(#"Added Custom1",{{"Datetime.1.3", "Year"}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Renamed Columns1", {{"Year", type text}, {"Month", type text}, {"Date", type text}}, "en-AU"),{"Date", "Month", "Year"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"FinalDate"),
#"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"FinalDate", type date}}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type1", "Time", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Time.1", "Time.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Time.1", type time}, {"Time.2", type text}, {"Datetime - Copy", type text}}),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type2", {{"Time.1", type text}, {"FinalDate", type text}}, "en-IN"),{"FinalDate", "Time.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"DateTime"),
#"Changed Type3" = Table.TransformColumnTypes(#"Merged Columns1",{{"DateTime", type datetime}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type3",{"Datetime.1.1", "Datetime.1.2", "Time.2", "Datetime - Copy", "DateTime"}),
#"Added Custom2" = Table.AddColumn(#"Reordered Columns", "Now", each DateTime.LocalNow),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "AU_DateTime", each if [Time.2] = null then [DateTime] - #duration(0,7,0,0)
else [DateTime] + #duration(0,10,0,0)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Datetime.1.1", "Datetime.1.2", "Time.2", "DateTime", "Now"})
in
#"Removed Columns"

[onedayover]

Thanks for these detailed responses, both work perfectly for what I need 🙂

[collinsg · Accepted solution]

Good-day onedayover.

Here are some steps I hope will help you. I copied your data into Excel, then took these steps in Power Query.

1. Determine if the datetime is US by looking for "M" as the last character of the text date.
2. If US, add the US timezone and then convert to AU timezone.
3. If not US, add the AU timezone.
4. Set the type as datetimezone.

If, rather than a datetimezone you want a datetime only, you can take a further step.

1. Use RemoveZone to remove the zone.
2. Set the type as datetime.

My output for the first four steps is the "Datetimezone AU" column below and my additional step is the "Datetime AU with no zone" column.

My M code is

let
   Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
   #"Change Type" = Table.TransformColumnTypes(Source,{{"Datetime", type text}}),

   #"Convert to AU" = Table.AddColumn(#"Change Type", 
      "Datetimezone AU", 
      each 
         if ( Text.End([Datetime],1) = "M" ) 
         then
            DateTimeZone.SwitchZone( 
               DateTime.AddZone( DateTime.FromText( [Datetime], [Format="M/d/yyyy, h:mm:ss tt"] ), -7, 0 ), 
               10, 
               0 
            )
         else 
         DateTime.AddZone( DateTime.FromText( [Datetime], [Format="dd/MM/yyyy, HH:mm:ss"] ), +10, 0 ),
      type datetimezone
),

   #"Remove zone" = Table.AddColumn(#"Convert to AU", 
      "Datetime AU with no zone", 
      each DateTimeZone.RemoveZone( [#"Datetimezone AU"] ), 
      type datetime)
in
   #"Remove zone"

Power Query shows date and time in accordance with your Locale setting so hopefully the datetimes should be presented correctly in your locale.

The formatting codes I used in the .FromText functions are described here.

[AnkitKukreja · Accepted solution]

Hi @onedayover 

Please use the below m-code to see how this can be achieved. I've done this on your sample data.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdBLDoMwDATQqyDWSB7/gHjXA1TqPuL+16ibkILUrp9mnEmtszBBSCCyTCjhJVjnY6kzlLCfwB6yBm8NeCfGAISvYd6hEHwABzTMOoBYb+DBmB7PZkZSTtJwD/D06rKRbLeQ5KURulFpdfJHPm9LxejTXPp9heWhjI1BF7RBrr+DWpv2pZIJu/4GCMuq4w0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Datetime = _t]),
#"Duplicated Column" = Table.DuplicateColumn(Source, "Datetime", "Datetime - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Datetime", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"Datetime.1", "Datetime.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Datetime.2", "Time"}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Renamed Columns", "Datetime.1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Datetime.1.1", "Datetime.1.2", "Datetime.1.3"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Datetime.1.1", Int64.Type}, {"Datetime.1.2", Int64.Type}, {"Datetime.1.3", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Month", each if ( [Datetime.1.1] <=12 ) then [Datetime.1.1]
else [Datetime.1.2]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Date", each if [Datetime.1.1] > 12 then [Datetime.1.1] else [Datetime.1.2]),
#"Renamed Columns1" = Table.RenameColumns(#"Added Custom1",{{"Datetime.1.3", "Year"}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Renamed Columns1", {{"Year", type text}, {"Month", type text}, {"Date", type text}}, "en-AU"),{"Date", "Month", "Year"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"FinalDate"),
#"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"FinalDate", type date}}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type1", "Time", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Time.1", "Time.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Time.1", type time}, {"Time.2", type text}, {"Datetime - Copy", type text}}),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type2", {{"Time.1", type text}, {"FinalDate", type text}}, "en-IN"),{"FinalDate", "Time.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"DateTime"),
#"Changed Type3" = Table.TransformColumnTypes(#"Merged Columns1",{{"DateTime", type datetime}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type3",{"Datetime.1.1", "Datetime.1.2", "Time.2", "Datetime - Copy", "DateTime"}),
#"Added Custom2" = Table.AddColumn(#"Reordered Columns", "Now", each DateTime.LocalNow),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "AU_DateTime", each if [Time.2] = null then [DateTime] - #duration(0,7,0,0)
else [DateTime] + #duration(0,10,0,0)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Datetime.1.1", "Datetime.1.2", "Time.2", "DateTime", "Now"})
in
#"Removed Columns"

[mussaenda]

Hi @onedayover ,

I believe this is doable. 

Separate first the US date to another column and you can use the DateTimeZone.SwitchZone

Furthermore, here is another link from the community

After that, combine them and set the data type

Hope this helps