Need to convert US DateTime to AU DateTime

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"

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.