Forum Discussion

Madhu155154's avatar
Madhu155154
Helper I
2 years ago

Not able use <> and = with dates in custom column Function

Hi Everyone, when I  am able to get the desired result when I filter in the Power query = Table.SelectRows(#"Removed Other Columns1", each ([#"Convereted - Rejected Date"] <> #date(1970, 1, 1)) an...
Need Help
mlsx4's avatar
mlsx4
Memorable Member
2 years ago

Hi Madhu155154 

 

I think you're missing each before the if, and there's an extra parenthesis at the end:

 

each if [#"Convereted - Rejected Date"] <> #date(1970, 1, 1) and [#"Converted - Offered Date "] = #date(1970, 1, 1) then "Rejected by us " else "Not Rejected"

 

 

In the end, you should have something like:

 

 

Table.AddColumn(#"Removed Other Columns1", "Custom", each if [#"Convereted - Rejected Date"] <> #date(1970, 1, 1) and [#"Converted - Offered Date "] = #date(1970, 1, 1) then "Rejected by us" else "Not Rejected")

 

 

  • 123abc's avatar
    123abc
    Community Champion
    2 years ago

    You're right, and I appreciate your clarification. If you're using the each keyword in the context of Table.AddColumn, the correct syntax for your custom column formula should indeed include each before the if statement. Also, there's an extra space in the string "Rejected by us ".

    Here's the corrected formula:

     

    = Table.AddColumn(#"Removed Other Columns1", "CustomColumnName", each if [#"Convereted - Rejected Date"] <> #date(1970, 1, 1) and [#"Converted - Offered Date "] = #date(1970, 1, 1) then "Rejected by us" else "Not Rejected")

     

    Make sure to replace "CustomColumnName" with the actual name you want for your custom column. This formula adds a new column with the specified logic for each row in the table.

     

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

     

    In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.