Using 1904 date system like in Excel

[android1]

Hi,

When I pull a date and time column through Web Service into Power BI (07/03/2016 1:00 PM) it reads as /Date(1457355600000)/ . How do I get it to display correctly? Something like the 1904 date system in Excel maybe?

[Grumelo]

Let me provide you a full answer and not just links and hints

You can achieve the conversion this way in Power Query

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

[Greg_Deckler]

See my response here:

http://community.powerbi.com/t5/Desktop/Convert-json-DateTime-format/m-p/28712