how to convert date/time/timezone to standardized timezone

[Anonymous]

I have a field of type Date/Time/Timezone.  Two sample values are:

11/24/2020 9:28:09 AM -06:00

11/25/2020 5:45:08 AM -07:00

In Power Query, I need to convert all of these values to Central Standard Time (CST).  How can I achieve this?

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Download example PBIX file here with this query

Assuming that the last part of the Date/Time/TZ values is the offset from GMT e.g. -06:00 then this query converts all such values to CST which is 6 hours behind GMT

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTUNzLRNzIwMlCwtDKysDKwVHD0VdA1MLMyMFCK1YEoMIUoMLUyMbUysIAoMCekwAi/Am0DE7CCWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column1.2", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Column1.2.1", "Column1.2.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.2.2", Int64.Type}, {"Column1.2.1", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Minutes", each [Column1.2.2]/60),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1.2.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.2.1", "Hours"}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Renamed Columns", {{"Column1.1", type datetime}}, "en-US"),
    #"Added Custom2" = Table.AddColumn(#"Changed Type with Locale", "CST", each [Column1.1] + #duration(0,(6+[Hours])*-1,0,0))
in
    #"Added Custom2"

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.