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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
android1
Post Patron
Post Patron

Converting JSON to Date/Time

Hi,

 

Trying to convert a 13 digit JSON date column to date/time. In Edit Query I change my JSON column,["Column1.Date - Copy"] to whole number.

I get 1.45731E+12 (1 example). I create a custom column and use this formula -> = ([#"Column1.Date - Copy"]/1000+7200)/84600+25569. I get 42794.95745.

When I change the column format to Date/Time I get 28/02/2017 10:58am. This should be 07/03/2016.

 

Any ideas what I'm doing wrong?

 

 

5 REPLIES 5
NiAck
New Member

As long as you're fine with precision of days you can use the Date.AddDays function, for instance as shown below.

= Table.AddColumn(#"Previous step", "New column name", each Date.AddDays(#date(1970,1,1),Int32.From(Number.FromText(Text.BetweenDelimiters([JsonDateColumn],"/Date(",")/"))/24/3600/1000)))

 

JSOTO_PBI
Frequent Visitor

In case anyone else needs a simpler solution, this is what worked for me.

 

#"Add Column" = Table.AddColumn(#",Previous Step Name", "<new column name>", each (#datetime(1970, 1, 1, 0, 0, 0 ) + #duration(0, 0, 0, [<field to change>]+36000/1000))),

Grumelo
Advocate II
Advocate II

Let's try this way

 

 

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 (create a new Query and post this code as it is)

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

 

pqian
Employee
Employee

Hi,

 

Thanks for the reply. I have zero knowledge of PQFL. In Curt's code what do I need to change to 

make it applicable to me? Is the let Json.Date the column that contains the JSON date in my table?

 

Thanks

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.