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:

/wp-content/uploads/2015/10/p1_805023.png

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:

/wp-content/uploads/2015/10/p2_805031.png

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

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

    https://github.com/IvanBond/pquery

    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

    https://bondarenkoivan.wordpress.com/

    (0) 

Leave a Reply