Forum Discussion
Syntax error in custom function to covert UTC to Central Time
I have the following M code as a custom function in power query, and I'm getting a "Token ')' expected" error, but I don't see where a parenthesis is missing. Can you please help?
Function is supposed to take a column with UTC datetime and return the datetime in Central Time, taking into account daylight savigs.
// Function to convert UTC to Central Time (CST/CDT)
(ConvertUTCtoCT as datetime) =>
let
// Extract the year of the given date
Year = Date.Year(ConvertUTCtoCT),
// DST starts on the second Sunday of March at 2:00 AM
MarchFirst = #date(Year, 3, 1),
FirstSundayMarch = Date.AddDays(MarchFirst, ((7 - Date.DayOfWeek(MarchFirst, Day.Sunday)) mod 7)),
DSTStart = DateTime.From(FirstSundayMarch + #duration(7, 2, 0, 0)), // Second Sunday at 2:00 AM
// DST ends on the first Sunday of November at 2:00 AM
NovemberFirst = #date(Year, 11, 1),
FirstSundayNovember = Date.AddDays(NovemberFirst, ((7 - Date.DayOfWeek(NovemberFirst, Day.Sunday)) mod 7)),
DSTEnd = DateTime.From(FirstSundayNovember + #duration(0, 2, 0, 0)), // First Sunday at 2:00 AM
// Check if the date falls within DST range
IsDST = ConvertUTCtoCT >= DSTStart and ConvertUTCtoCT < DSTEnd,
// Apply time zone offset (-6 for CST, -5 for CDT)
Offset = if IsDST then -5 else -6,
CentralTime = DateTime.AddZone(ConvertUTCtoCT, Offset)
in
CentralTime
The error messages often point to something other than what they specifically indicate.
Examining the code window can give you some hints.
For example, there is no `mod` operator in PQ and you will see that it has a wavy red underline in the editor:
You need to use the `Number.Mod` function.
Also, if your computer itself is set to Central Time, there is a much simpler method to convert to local time (or to the local time of whatever a computer is set to (and it will take DST into account):
// Function to convert UTC to Local Time) (ConvertUTCtoLT as datetime) => let addTZ = DateTime.AddZone(ConvertUTCtoLT,0), toLocal = DateTimeZone.ToLocal(addTZ), localDateTime=DateTime.From(toLocal) in localDateTime
6 Replies
The error messages often point to something other than what they specifically indicate.
Examining the code window can give you some hints.
For example, there is no `mod` operator in PQ and you will see that it has a wavy red underline in the editor:
You need to use the `Number.Mod` function.
Also, if your computer itself is set to Central Time, there is a much simpler method to convert to local time (or to the local time of whatever a computer is set to (and it will take DST into account):
// Function to convert UTC to Local Time) (ConvertUTCtoLT as datetime) => let addTZ = DateTime.AddZone(ConvertUTCtoLT,0), toLocal = DateTimeZone.ToLocal(addTZ), localDateTime=DateTime.From(toLocal) in localDateTimeYes, the Number.Mod() function cleared the error for me, thank you!
My computer is not in Central Time, and I'm actually having to convert the date time column to different time zones, because we have data for different locations all coming in UTC time zone. So I think I'm stuck with having to create a custom column for each timezone I need.
Thank you!
Do all your timezones have the same datetime for the DST shift?
I am not sure I'm understanding your question correctly... but I think the answer is yes. I have a column in UTC time zone. And the table has records for locations that are in different time zones (Pacific, Central, Mountain, etc.) And all those locations shift to/from DST on the same datetime. Is that what you are asking?
