cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jayendran
Solution Sage
Solution Sage

Table.TransformColumns with #datetime Issue

Hi,

 

This is my table value in PowerBI which is a timestamp

 

Capture.PNG

 

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)+#duration(0,0,0,<fieldname>)

Now I'm trying to make the DAX like 

 

#"Transformed the Column" = Table.TransformColumns(#"Renamed Columns", List.Transform({"timestamp"}, each {_, (inner) => #datetime(1970,1,1,0,0,0)+#duration(0,0,0,inner), type text}))

But then I got the error like 

 

Expression.Error: We cannot convert the value "1549262624176" to type Number. Details: Value=1549262624176 Type=Type

 

Please help me to solve this issue

 

~Jay

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

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


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

4 REPLIES 4
xeha88
Regular Visitor

timestemp with mili sec (example from Google Analytics 4). This works for me

 

= Table.AddColumn(#"Changed Type", "Custom", each #datetime(1970,1,1,0,0,0)+ #duration(0,+4,0,Number.Round(Number.From([event_timestamp])/1000000, 0)))

 

Don t forget to change timezone in duration part of code 

Stachu
Community Champion
Community Champion

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


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

 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
Community Champion
Community Champion

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"


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors