Expression.Error: We cannot convert the value to type Date.

Lumi Oueb 21 Reputation points
2021-02-22T11:39:23.123+00:00

I have a problem with a column that cannot be used as date : 70598-image.png

My value seems to be in date format ( I tried to change it from date to datetime, the time is added)
70605-image.png

but when I try to change it, it's not recognised as date and I get the error

let  
    Source = Excel.CurrentWorkbook(){[Name="GETTNINFO"]}[Content],  
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source.Name1", type text}, {"Seq No.", Int64.Type}, {"Doc No.", type text}, {"Doc Rev", type text}, {"Issue Purpose", type text}, {"Title", type text}, {"Sent Date", type date}}),  
    
    #"Inserted Parsed Date" = Table.AddColumn(#"Changed Type", "Rev. Date", each Date.ToText("Sent Date","MM/dd/yyyy")),  

I don't understand exactly the issue I'm afraid. I tried to set the column as date, not datetime . It seems to change the date ( the time disappear) but the error is still the same

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
36,969 questions
0 comments No comments
{count} votes

Accepted answer
  1. Lz._ 8,991 Reputation points
    2021-02-22T12:10:54.567+00:00

    Hi @Lumi Oueb

    If you get the error on the #"Inserted Parsed Date" step, change it with:

    #"Inserted Parsed Date" = Table.AddColumn(#"Changed Type", "Rev. Date", each  
        Date.ToText([Sent Date],"MM/dd/yyyy"), type text  
    )  
    

    As you reference a column name/field you must pass it to the function (Date.ToText in the case) between [], not between "" (double quotes) as you did

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Lumi Oueb 21 Reputation points
    2021-02-22T13:06:06.12+00:00

    Thanks a lot, I'm still a little fuzzy when I should use "" and when it's [] and I really didn't understand the error as I was more checking the type or if I didn't made an error wording "DateTime / datetime" or other.

    Thanks a lot !