Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Showing results for 
Search instead for 
Did you mean: 
dberwanger
Explorer

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-bydes...

General Information for ODATA and Power Query:

http://scn.sap.com/community/business-bydesign/blog/2015/10/07/using-sap-bydesign-odata-in-microsoft...

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

Every date looks like: !'"§%&# :sad:

in real:

But this problem is solved very quickly.

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

    #"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:


1 Comment
Labels in this area