Forum Discussion

k1s2's avatar
k1s2
Helper 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
Super User
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
k1s2's avatar
k1s2
Helper I
2 years ago

Thanks,

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"))}})

 

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    No. Don't use Table.TransformColumnTypes. Use this 

        = Table.TransformColumns(
        Source,
        {{"This Date", each Date.From(DateTime.From(_, "en-GB")), type date},
        {"That Date", each Date.From(DateTime.From(_, "en-GB")), type date}}
    )
    • k1s2's avatar
      k1s2
      Helper I
      2 years ago

      Thanks,

       

      I'm having trouble applying it to all columns ending with the word 'Date".

       

      I tried:

      = Table.TransformColumns(
  #"prevstep",
   {List.Transform(List.Select(Table.ColumnNames#"prevstep"), eachText.EndsWith(_,"Date"))),
   each Date.From(DateTime.From(_, "en-GB")), type date}
 )

       

      ...but got an "expression error 1 arguments were passed to a function which expects 2",

       

      Where am I going wrong

      • AlienSx's avatar
        AlienSx
        Super User
        2 years ago

         

         

         

        Table.ColumnNames#"prevstep")

         

         

        no opening bracket. You are trying to wrap everything into one single step. Don't be afraid to write your calcs down step by step - more chances to catch a real reason of your error when you are new to M. This does not always slow down your code.