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

[Madhu155154]

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)) and ([#"Converted - Offered Date "] = #date(1970, 1, 1)))

But then  I tried it through the Custom Column in Power Query

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

Not able to get “Rejected by us”

[mlsx4]

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]

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.

[123abc]

It looks like there might be a small typo in your custom column formula. Specifically, there's a missing parenthesis at the end of the formula. Here's the corrected version:

= 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 add the closing parenthesis at the end of the formula. If the issue persists, here are a few additional troubleshooting steps:

1. Data Types: Ensure that the data types of your date columns are consistent and correctly set. If the data types are not matching, the comparison might not work as expected.

2. Spaces in Column Names: It seems there might be a space in the column name "Converted - Offered Date". Make sure there are no extra spaces in the column names, and they match exactly with the column names in your data.

3. Data Values: Check some sample values in the columns "Convereted - Rejected Date" and "Converted - Offered Date" to see if there are any unexpected values that might be causing the condition not to be met.

If the issue still persists, providing more details about the data or any error messages you are encountering would be helpful for further assistance.

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.