Forum Discussion
android1
Post Patron
10 years agoConvert 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)
- 10 years ago
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.
Grumelo
Advocate II
10 years agoI 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) - 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