CODEWITHSUNDEEP
excelpowerbipowerquery
Drzemlik
Drzemlik

Reputation: 59

Expression.Error: We cannot convert the value #date(2021, 9, 2) to type Logical

I am trying to combine date and time by adding custom column with Date/Time data (e.g. 2021-09-01 18:00:00). Trying to achieve this with the following code:

[date] and " " and [time]

but I end up with the error from the title. I don't see anything wrong in the formula bar:

= Table.AddColumn(#"CreateTime naming", "Custom", each [date] and " " and [time])

Before I did very similar operation with date and time, using the same code phrase structure as above and it worked. Then I tried to adjust it for the mentioned operation and it failed.

The error message I get is:

Expression.Error: We cannot convert the value #date(2021, 9, 2) to type Logical.
Details:
Value=2021-09-02
Type=[Type]

Could you help me to solve this?

Upvotes: 0

Views: 7007

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

Hopefully, this discussion will clarify what I have written in the comments.

The formula to use:

        #"Added Custom" = Table.AddColumn(#"Changed Type", "datetime", 
each [date] & [time])

I suspect you are trying to use something like:

       each [date] & " " & [time])

which is why you see the last error you mention in your comments

Expression.Error: We cannot apply operator & to types Date and Text.
Details:
    Operator=&
    Left=9/1/2021
    Right=

Had the issue been with [Time], the error would have read:

Expression.Error: We cannot apply operator & to types Text and Time.
Details:
    Operator=&
    Left= 
    Right=1:15:00 PM

But the & operator will join a date type and a time type into a datetime type. No further conversion needed.

For example:

let

//data typed into a table
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkksSVXSUSrJzE1VitWJVrLUN9Q3MjAyBIoZGlsZmkIFjWCCZlYm5uhillamRkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

//data types set to date and time for the respective columns
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"date", type date}, {"time", type time}}),

//create a datetime column
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [date] & [time], type datetime)

in
    #"Added Custom"

enter image description here

Upvotes: 1

Related Questions