Converting JSON to Date/Time

android1

This post from Curt should help you with the conversion:

https://social.technet.microsoft.com/Forums/en-US/52811583-9dfe-450b-b0f8-aa6fc53cb4fa/convert-json-date-to-date?forum=powerquery

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) - 8) 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

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))),

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)))