Skip to Content
Author's profile photo Daniel Berwanger

ODATA queries: JSON and date format

this blog describes how to convert dates e.g. posting date of ODATA queries in JSON Format to a readable date.

General information for ODATA queries:

General Information for ODATA and Power Query:

If you decide to request the data in JSON Format, you have several pros but at least one con:

Every date looks like: !'”§%&# 🙁

in real:


But this problem is solved very quickly.

Just* copy this code to your editor and smile again 🙂

    #”date_converting1″ = Table.ReplaceValue(<last process step>,Date(“,””,Replacer.ReplaceText,{“CPOSTING_DATE”}),

    #”date_converting2″ = Table.ReplaceValue(#”date_converting1″,”)/”,””,Replacer.ReplaceText,{“CPOSTING_DATE”}),

    #”date_converting3″ = Table.TransformColumnTypes(#”date_converting2″,{{“CPOSTING_DATE”, Int64.Type}}),

    #”date_converting4″ = Table.AddColumn(#”date_converting3″, “Posting Date”, each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [CPOSTING_DATE]/1000)),

    #”date_converting5″ = Table.RemoveColumns(#”date_converting4″,{“CPOSTING_DATE”})

*Please replace <last process step> with your last step e.g. #”results erweitern1″ which is above your code now. Additionally your column name could be different, so please check it and replace the parts of “CPOSTING_DATE” with your column name.

And finally you get the result in your new column:


Assigned Tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo Ivan Bondarenko
      Ivan Bondarenko

      Thanks, Daniel!

      Same I thought when saw response from SAP first time: "Every date looks like: !'"§%&#" 😆

      I did the same as you - made a function for such transformations.

      Then I went further - built functions to get reports easily.

      In my repository on Github you may find very useful function for work with ByD using Power Query.

      ByD related functions start from "ByD."

      Function for JSON date transformation called Date.EpochToDate.

      Any feedback, contribution to project will be highly appreciated.

      Feel free to ask if something is not clear.

      Other useful examples, like relative selection in PQ, you may find in my blog