Forum Discussion

Jayendran's avatar
Jayendran
Icon for Solution Sage rankSolution Sage
7 years ago
Solved

Table.TransformColumns with #datetime Issue

Hi,   This is my table value in PowerBI which is a timestamp     I'm trying to conver this into DateTime, after refering to this Solution I'm plan to use #datetime(1970,1,1,0,0,0)+#durati...
Need Help
  • Stachu's avatar
    Stachu
    7 years ago

    right now this column has type any, can you change the type to the whole number, and then try to perform the transformation?

    EDIT for me the transformation fails on the #duration
    #duration(0,0,0,[timestamp])
    Expression.Error: The Duration operation failed because the resulting duration falls outside the range of allowed values.

     

    is 1549262624176 the number of seconds? it seems to be 17931280 days (original number divided by 86400) which is 49126 years, which seems a lot
    if you treat it as miliseconds (i.e. divide by 1000) it gives 4th Feb 2019

    = Table.AddColumn(#"Changed Type", "Custom", each #duration(0,0,0,[timestamp]/1000)+#datetime(1970,1,1,0,0,0))
Jayendran's avatar
Jayendran
Icon for Solution Sage rankSolution Sage
7 years ago

 Hi Stachu  Thanks for your solution. It works perfectly. Before accepting the solution i have some doubt I tried your answer with transformcolumns instead of addcolumn facing different issue

 

= Table.TransformColumns(#"Changed Type", List.Transform({"timestamp"}, each {_, (inner) => #datetime(1970,1,1,0,0,0)+#duration(0,0,0,[inner]/1000), type text}))

Expression.Error: We cannot apply field access to the type Text.
Details:
Value=timestamp
Key=inner

Stachu's avatar
Stachu
Icon for Community Champion rankCommunity Champion
7 years ago

this works fine for me

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ1sTQyA0ITQ3MzpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [timestamp = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"timestamp", Int64.Type}}),
    #"Added to Column" = Table.TransformColumns(#"Changed Type", {{"timestamp", each #datetime(1970,1,1,0,0,0)+#duration(0,0,0,_/1000), type number}})
in
    #"Added to Column"