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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
onedayover
Helper III
Helper III

Need to convert US DateTime to AU DateTime

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

2 ACCEPTED SOLUTIONS
AnkitKukreja
Super User
Super User

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"

 

 

For Power BI trainings or support dm or reach out to me on LinkedIn.
If my response has successfully addressed your question or concern, I kindly request that you mark this post as resolved. Additionally, if you found my assistance helpful, a thumbs-up would be greatly appreciated.

Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904
https://topmate.io/ankit_kukreja

View solution in original post

collinsg
Super User
Super User

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.

collinsg_0-1684862232990.png

 

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.

View solution in original post

4 REPLIES 4
onedayover
Helper III
Helper III

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

collinsg
Super User
Super User

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.

collinsg_0-1684862232990.png

 

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
Super User
Super User

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"

 

 

For Power BI trainings or support dm or reach out to me on LinkedIn.
If my response has successfully addressed your question or concern, I kindly request that you mark this post as resolved. Additionally, if you found my assistance helpful, a thumbs-up would be greatly appreciated.

Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904
https://topmate.io/ankit_kukreja
mussaenda
Super User
Super User

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

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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