Forum Discussion

arielcedola's avatar
arielcedola
Icon for Helper III rankHelper III
6 years ago
Solved

Transforming values in column without creating new columns in M language

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 .)

  • 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]))))

     

     

2 Replies

  • camargos88's avatar
    camargos88
    Icon for Community Champion rankCommunity Champion

    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's avatar
      arielcedola
      Icon for Helper III rankHelper III

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

      Thanks .)