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
k1s2's avatar
k1s2
Icon for Helper I rankHelper 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
Icon for Super User rankSuper 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.

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

    If I understand your suggestion, your point is taken that making it work it 2 steps is fine, but I suppose 1 step is more efficient.

     

    Two simple steps for one column are straightforward for me like this:

     

        #"Changed Type" = Table.TransformColumnTypes(prevstep,{{"Date & Time", type datetime}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Date & Time", type date}}),

     

    Also,

    Table.TransformColumnTypes(prevstep,List.Transform(List.Select(Table.ColumnNames#"prevstep"), eachText.EndsWith(_,"Date"))),

    Seems to work, but combing them I still can't get it working.  This:

     

     

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

     

    now gives me 

    Expression.Error: 3 arguments were passed to a function which expects 2.
    Details:
    Pattern=
    Arguments=[List]

     

    • 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")...