Transforming values in column without creating new columns in M language

[arielcedola]

Hi,

I'm using m language to perform the following convertions of datetime data:

1) unix timestamp to utc date time zone

2) utc date time zone to local time zone

3) local time zone to datetime

The query I'm using is the following:

let

....

#"unix to utc" = Table.AddColumn(#"previous step", "timestamp_utc", each #datetimezone(1970,1,1,0,0,0,0,0) + #duration(0,0,0,[data])),
#"utc to local" = Table.AddColumn(#"unix to utc", "timestamp_local", each DateTimeZone.ToLocal([timestamp_utc])),
#"to datetime" = Table.AddColumn(#"utc to local", "timestamp", each DateTime.From([timestamp_local]))
in
#"to datetime"

Unix datetime data is in the column [data] of my table.

Every step in the query introduces a new column: timestamp_utc, timestamp_local and timestamp. My question is, how can I perform the same functions and get the same result as in the last column timestamp, but without introducing the intermediate columns timestamp_utc and timestamp_local?

Thanks .)

[camargos88 · Accepted solution]

Hi @arielcedola ,

Try nesting the functions:

Table.AddColumn(#"previous step", "timestamp_utc", each DateTime.From(DateTimeZone.ToLocal(#datetimezone(1970,1,1,0,0,0,0,0) + #duration(0,0,0,[data]))))

[arielcedola]

Exactly! I tried nesting the functions before but for some reason it didn't work. Now it did it perfectly.

Thanks .)