Convert JSON date /Date(1686031729000+0000)/ format in Power Query

[reematalal]

Dear All, 

Need your kind support in this issue after connecting power bi to API Json format date and time shown as below

/Date(1686031729000+0000)/

how to convert it to normal date? 

Thanks in advance. 

[ibarrau]

Hi. As far as I have read this is the format:

1. “XXXXXX” part is the number of milliseconds since 1 January 1970
2. “+XXXX” part indicates the time zone, so when you see “+0000”, it means the server time zone is UTC.

If you can just extract that Date to a column, you can transform it with PowerQuery. 

let
    Source = "Date(1686031729000+0000)",
    #"Converted to Table" = #table({"JsonDate"}, {{Source}}),
    start_date = #date(1970,1,1),
    #"Added Seconds" = Table.AddColumn(#"Converted to Table", "Secs", each Number.FromText(Text.BeforeDelimiter(Text.AfterDelimiter([JsonDate], "("), "+"))/1000),
    #"Added NewDate" = Table.AddColumn(#"Added Seconds", "NewDate", each start_date + #duration(0,0,0,[Secs]))
in
    #"Added NewDate"

The code has a hardcoded Date example, but you can use as source the Json response formatted as table. Then a variable that has the starting date point.

The following two steps adding columns are simple. One is converting miliseconds to seconds (divide in 1000) and then add to start_date the seconds to get the date. Result:

I hope that helps,