cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
h4tt3n
Resolver I
Resolver I

Need help converting timestamp 2020-12-10T14:01:21.155Z to DateTime 12/10/2020 14:01:21

Hello folks,

 

I am consuming measurement data from an API and get a value and a timestamp with the format "2020-12-10T14:01:21.155Z". This is giving me some problems with Power BI visuals and time slicers, since they appear to be incompatible.

 

I have tried to convert the datatype of the timestamp to datetime in the power query editor, but this doesn't solve the problem.

 

Can you please give me some hints on how to explicitly take this date format apart and reassemble it as true PBI-compatible datetime format, ie. "12/10/2020 14:01:21" (with or without seconds)?

 

I have googled and looked in the Gil Raviv Power Query "bible".

 

Thanks in advance!

1 ACCEPTED SOLUTION
h4tt3n
Resolver I
Resolver I

Okay, I found the error. It seems that the timestamps were incompatible with my datetime table because of the seconds. My lookup calendar table has seconds, but they are always zero. The timeststamp seconds can have any value. After deconstructing the timestamp, and reconstructing it with #datetime(), setting seconds to zero, everything worked fine.

 

Cheers, Mike

View solution in original post

9 REPLIES 9
h4tt3n
Resolver I
Resolver I

Okay, I found the error. It seems that the timestamps were incompatible with my datetime table because of the seconds. My lookup calendar table has seconds, but they are always zero. The timeststamp seconds can have any value. After deconstructing the timestamp, and reconstructing it with #datetime(), setting seconds to zero, everything worked fine.

 

Cheers, Mike

View solution in original post

Jimmy801
Super User III
Super User III

Hello @h4tt3n 

 

It's always best to use build-in functions of Power Query. As I don't know the format of this MM/DD or DD/MM I supposed it is american style and we are talking here about the 12th of october. If it's not like this, change the culture "en-US".

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA1NNI1NAgxNLEyMLQyMtQzNDWNUoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TextDateTim = _t]),
   TransFormToDateTime = Table.TransformColumns
   (
       Source,
       {
           {
               "TextDateTim",
               each DateTime.From(DateTimeZone.RemoveZone(DateTimeZone.FromText( _)), "en-US"),
               type datetime
           }
       }
   )
in
    TransFormToDateTime

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Hi Jimmy, the timestamp is simply my local "now" at the time of posting, december 10th 2020. I'm in Europe, so perhaps that's part of the problem 🙂

Hello @h4tt3n 

 

I just saw that my code gives the 10th of december, so it should work. Or what else is the problem now?

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

It's early morning here, so I am testing the suggestions that popped in during the night. I should have it covered, and I'll of course "mark as solution" when I get there.

watkinnc
Solution Sage
Solution Sage

You could use Table.AddColumn(PriorStepName, "New DateTime", each DateTime.From(Number.From([DateTime])))

KNP
Responsive Resident
Responsive Resident

Hi @h4tt3n 


 

I have tried to convert the datatype of the timestamp to datetime in the power query editor, but this doesn't solve the problem.

 


Why does converting to datetime not solve the problem?

What is the specific problem you're experiencing with the visuals/slicers?

 

 

Visuals and slicers work great if I use the timestamps from the dataset as x axis or "time table". But as soon as I use my tried-and-true datetime table implemented in all our other reports, it simply shows a blank screen. A relation has been established between the data and datetime table, so that's not it. Changing timestamp datatype to datetime in power query editor doesn't help either.

CNENFRNL
Super User III
Super User III

Hi, @h4tt3n , you might want to remove the millisecond part and then convert it to PBI-compatible datetime format,

let
    Source = "2020-12-10T14:01:21.155Z",
    #"datetime value" = DateTime.From(Text.Start(Source,Text.PositionOf(Source,".")))
in
    #"datetime value"

Screenshot 2020-12-10 172013.png

 

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 on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors