Error converting data type to date but not if converting data type to datetime first

[k1s2]

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?

[k1s2]

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 

[AlienSx · Accepted solution]

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]

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]

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]

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]

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]

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]

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

[k1s2]

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]

@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)

[k1s2]

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?

[AlienSx]

@k1s2  use Table.TransformColumns instead.

[AlienSx]

= Date.From(DateTime.From("05/18/2021 15:15:33"))