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
android1
Post Patron
Post Patron

Convert json DateTime format

Hi,

 

How do I convert a column containing a date and time in the json format to DateTime. My dates are like this /Date(1457355600000)

1 ACCEPTED SOLUTION

I was referring to change your query name so that your table name in the data model doesn't have the GUID and such in it and ? and such.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

13 REPLIES 13
Anonymous
Not applicable

How I can convert this format "2019-10-14T19:46:53.2751336Z" to "2019-10-14 19:46:53"?

Thaks!

Anonymous
Not applicable

@Anonymous try cleaning the column with the Clean function on the ribbon. It worked for me.

Grumelo
Advocate II
Advocate II

I would not recommend to do the transformation in a DAX calculated column.

You can do it with Power Query by Transforming an existing column.

 

The step to execute to do the transformation

= Table.TransformColumns(#"<previous step name>",{{"<column name to update>", DateFromJson}})

The code of the Power Query custom function

let DateFromJson = (date as any) as any =>
     let
        input = if date is null then "/Date(00000000000000)/"else date,
         Stripped = if Text.StartsWith(input, "/Date(") and Text.EndsWith(input, ")/") then Text.Range(input, 6, Text.Length(input) - 😎 else error "Not a date",
         Position = Text.PositionOfAny(Stripped, {"+", "-"}, 1),
         Parts = if Position < 0 then { Stripped, "0" } else { Text.Range(Stripped, 0, Position), Text.Range(Stripped, Position) },
         NumberParts = { Number.FromText(Parts{0}), Number.FromText(Parts{1}) },
         Result = Date.FromText("1/1/1970") + #duration(0, 0, 0, (NumberParts{0} + 36000 * NumberParts{1}) / 1000),
        output = if Date.Year(Result) = 1970 then null else Result

     in
  output 
 in DateFromJson

 

Greg_Deckler
Super User
Super User

My understanding of JSON Dates is that it is the number of milliseconds from a reference date of 1/1/1970. Therefore, I would strip out all the text characters, if you need assistance with that, let me know but you could do it with a MID that grabs the first character after the ( and grabs the number of characters up until the ). Make sure it is a number format and then you can use the following DAX:

 

Date = DATE(1970,1,1) + [JSONDate]/1000/86400

Basically, divide by 1,000 to get seconds and then by 86,400, the number of seconds in a day (you could just divide by 864,000. This gives you the number of days since 1/1/1970 and then you just add that to a date of 1/1/1970. I got back "3/7/2016 1:00:00 PM"


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler This worked like a champ for me!  Thanks so much!

Hi,

 

Thanks for your assistance. I'm having a bit of trouble with this. I have stripped out the text using extract. I am left with values such as 453808709000. When I change this column to a decimal number I get 4.53809E+11.

 

I perform the following DAX formula -> Temp1 = DATE(1970,1,1) + [Column1.DutyTimeFrom]/1000/86400. 

[Column1.DutyTimeFrom] contains 4.53809E+11.

 

i get the following error message -> The value for 'Column1.DutyTimeFrom' cannot be determined. Either 'Column1.DutyTimeFrom' doesn't exist, or there is no current row for a column named 'Column1.DutyTimeFrom'.

 

@android1 - Can you post a screen shot of your data model so that I can see your columns? The error indicates that it can't find [Column1.DutyTimeFrom]. This could be because you need to put the table name in front of it or that there is a typo or something.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

JSon Date Conversion.png

Hmm, I creatd a column in a test model called Column1.DutyTimeFrom and was able to interact with it no problem. Only thing I can think of is that perhaps it doesn't really like your table naming convention. Can you right click it and rename it to something simple like "ClickData" and see if that resolves the issue?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi,

 

Still no luck. Same error message appearing. I've attached a pic of my Query, showing the colun & data type.JSon Date Conversion 2.jpg

I was referring to change your query name so that your table name in the data model doesn't have the GUID and such in it and ? and such.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi,

 

Still no luck. Query name changed & same error message. One of those weird ones it seems.

Hi,

 

What you have told me to do is correct, something else is throwing it off. I'll mark your answer as the solution.

Thanks for your help.

 

 

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.