Forum Discussion

k1s2's avatar
k1s2
Icon for Helper I rankHelper I
2 years ago
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 transf...
Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago
    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
AlienSx's avatar
AlienSx
Icon for Super User rankSuper User
2 years ago

ones again, don't use Table.TransformColumnTypes. I told many times about this. I give up, sorry 😞

k1s2's avatar
k1s2
Icon for Helper I rankHelper I
2 years ago

I don't use TransformColumnTypes, I use TransformColumns as instructed, but I can't work out the syntax when combining with the the  List.Transform(List.Select(Table.ColumnNames,(#"prevstep")...

  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    2 years ago

    k1s2 I am sorry, I was looking at message #11 with transform column types. Never mind. Try this

        date_columns = List.Buffer(
        List.Select(
            Table.ColumnNames(prevstep),
            each Text.Contains(_, "Date")
        )
    ),
    tfms = List.Transform(
        date_columns,
        each {_, each Date.From(DateTime.From(_, "en-GB")), type date}
    ),
    result = Table.TransformColumns(prevstep, tfms)