Forum Discussion
Solved
Error converting data type to date but not if converting data type to datetime first
I have data in a column detected as text like this 18/05/2021 15:15:33
If I try to transform it to date, PowerBI throws an error 'couldn't parse the input as a Date value'
If I try to transform it to datetime, to works and then I have to add a second step to transform it to date.
How can I do this in one step?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrTQNzDVNzIwMlQwNLUCImNjpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [data = _t]), to_date = Table.TransformColumns(Source, {"data", each Date.From(DateTime.From(_, "de-DE"))}) in to_date
13 Replies
= Date.From(DateTime.From("05/18/2021 15:15:33"))Thanks for your reply, but I'm not sure where that goes,
Currently I have something like:
= Table.TransformcolumnTypes(Source,{{"This Date", type datetime},{"That Date", type datetime}})Does what you are suggesting required a new column?
I don't know what you mean. Would you mind showing me a complete example starting with what I 've got in the formula bar
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrTQNzDVNzIwMlQwNLUCImNjpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [data = _t]), to_date = Table.TransformColumns(Source, {"data", each Date.From(DateTime.From(_, "de-DE"))}) in to_dateThanks,
So the line for converting multiple columns to UK date format in one step would be something like:
= Table.TransformcolumnTypes(Source,{{"This Date", each Date.From(DateTime.From(_, "en-GB"))},{"That Date", each Date.From(DateTime.From(_, "en-GB"))}})
