Forum Discussion

1 Reply

  • ibarrau's avatar
    ibarrau
    Icon for Super User rankSuper User
    2 years ago

    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,