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: !'"§%&# :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:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
5 | |
2 | |
2 | |
2 | |
2 | |
2 | |
2 | |
1 | |
1 |