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:

http://scn.sap.com/community/business-bydesign/blog/2015/10/02/connect-excel-with-sap-business-bydesign-using-odata-for-analytics

General Information for ODATA and Power Query:

http://scn.sap.com/community/business-bydesign/blog/2015/10/07/using-sap-bydesign-odata-in-microsoft-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: