Here a simple M Language Custom Function used to convert UTC datetime field to local time.
In my case I get from SQL server databasetime field containing the UTC start date of an operation.
I need to show to my users this information converted to the local time in order to simplify the data reading.
Then I simply created a custom m language Function to do it.
Here a step by step guide:
I suppose to get from an external source (e.g. a SQL Database) a recordset built by 2 column:
- StartDate
- ElapsedMs
My goal is to convert the UTC Date and Time value stored inside the StartDate Field to the local time.
Then I decide to create a Custom Function to do this operation.
My function receives as input parameter a datetime named utcDateTime and returns a datetimezone containing the local time conversion.
To perform it, I need to create an Blank Query and, using the Advanced Editor from the Home Ribbon, paste the following m language code
(utcDateTime as datetime) as datetimezone
=>
let
#"myUTC" = DateTime.AddZone(utcDateTime,0),
#"myLocal" = DateTimeZone.ToLocal(#"myUTC")
in
#"myLocal"
Now, a generic Query1 function is created: you can rename it with a less ambiguous name, for example utc2Local.
This function can be invoked from your table, by clicking Invoke Custom Function button inside the Add Column Ribbon.
And then, the local date field is part of your main table.
