- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
Converting JSON to Date/Time
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
05-03-2016 07:38 AM
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?
Re: Converting JSON to Date/Time
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
05-03-2016 05:37 PM
This post from Curt should help you with the conversion:
Re: Converting JSON to Date/Time
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
05-05-2016 05:05 AM
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
Re: Converting JSON to Date/Time
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
08-11-2016 06:00 AM
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
Re: Converting JSON to Date/Time
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
04-19-2017 02:58 PM
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))),