Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Madhu155154
Helper I
Helper I

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)) 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”



3 REPLIES 3
mlsx4
Memorable Member
Memorable Member

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.