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

Question

question

LumiOueb-1440 asked Feb 22, '21 Lz-3068 commented Feb 22, '21

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

I have a problem with a column that cannot be used as date :

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

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

power-query-not-supported

image.png (9.7 KiB)

image.png (11.3 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2021-02-22T12:10:54.567Z

Lz-3068 answered Feb 22, '21

Hi @LumiOueb-1440

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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2021-02-22T13:06:06.12Z

LumiOueb-1440 answered Feb 22, '21 Lz-3068 commented Feb 22, '21

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 !

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 · Feb 22, 2021 at 02:07 PM

Glad I could help @LumiOueb-1440 & Thanks for posting back

0 ·

question