Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
h4tt3n
Resolver II
Resolver II

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 II
Resolver II

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 II
Resolver II

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

Jimmy801
Community Champion
Community Champion

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 🙂

Jimmy801
Community Champion
Community Champion

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
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
KNP
Super User
Super User

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?

 

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

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

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

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors