Forum Discussion

Dev-001's avatar
Dev-001
Regular Visitor
1 year ago

Expression.Error:We cannot apply operator + to types Date and Number

Hi I keep the above error even after changing data type to Date.Trying to create a new custom column based on another column.



This was the excel formula
=IF(AND(AM2="M",OR(AL2="",AL2<TODAY()+1))," PCF","")
I created the power query(M Code) for this but it's throwing an error. 

Any ideas how I can get around this?

 

Many thanks for all help

Need Help

4 Replies

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    Rather than checking < today + 1 do a check for <= today.

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi Dev-001 

    Use Date.AddDays(Date.From(DateTime.LocalNow()), 1)

     

    Note: Power BI Servie uses UTC so take that into consideration when using functions that gets the current time. I use the formula below to convert a UTC date to my current time zone.

    Date.From(DateTime.From(DateTimeZone.RemoveZone(DateTimeZone.UtcNow()) + #duration(0,8,0,0)))
//replace +/8 with your UTC offset
  • Dev-001's avatar
    Dev-001
    Regular Visitor
    1 year ago
    Table.AddColumn(CBCEventcalc, "PCF Event calc", each if [PCF Event] = "M" and ([Estimated Pickup] = null or [Estimated Pickup] <= Date.From(DateTime.LocalNow()))then
               "PCF"
           else
               "")
    It's still showing the  error as shown below
  • Dev-001's avatar
    Dev-001
    Regular Visitor
    1 year ago
    Table.AddColumn(CBCEventcalc, "PCF Event calc", each if DateTime.From([PCF Event]) = DateTime.From("M") and DateTime.From([Estimated Pickup] = DateTime.From(null) or DateTime.From([Estimated Pickup]) <= Date.From(DateTime.LocalNow()))then
               "PCF"
           else
               "")
    Even i tried this but still throwing error. Please provide some advice