Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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
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
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.
You could use Table.AddColumn(PriorStepName, "New DateTime", each DateTime.From(Number.From([DateTime])))
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 ;). |
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. | Proud to be a Super User! |
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.
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"
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! |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.