Power Query - Create new column based on latest date in two other columns

[Anders_G]

Hi,

I want to create a new column in Power Query with the latest date found in the below mentioned two columns. The column on the right might have Null values. If so, the date in the left column should be used.

if [Start_date__c] >= [Opportunity History.First Signature Date] then [Start_date__c] else if [Opportunity History.First Signature Date] = "null" then [Start_date__c] else [Opportunity History.First Signature Date]

I've managed a simple IF/THEN/ELSE formula. But, it doesn't take in to account the null values.

Thank you!

[davehus]

Hi @Anders_G ,

Add a conditional column where Opportunity History = null then Start_date_c else Opportunity History.

This should do it for you.

Did I help you today? Please accept my solution and hit the Kudos button.

[Anders_G]

That will give the Start Date column only when Opportunity History is null right? The date is not always the latest in the Opportunity History column. 

I've created a if/then/else formula for a custome column but it fails to take in to consideration the null values.

if [Start_date__c] >= [Opportunity History.First Signature Date] then [Start_date__c] else if [Opportunity History.First Signature Date] = "null" then [Start_date__c] else [Opportunity History.First Signature Date]

[davehus]

Hi @Anders_G , The the inverted columns off the null.

[Anders_G]

But I want it to first compare which column has the most recent date. If column A has the most recent date it should return column A, if column B has the most recent date it should return column B.

Like this:

[onurbmiguel_]

Hello Anders_G

Do you want the  latest date by row or for all column ? 

BR 

Bruno

[Anders_G]

By row. 

I've managed an if formula that gives me the correct values, but it fails with the null values.

if [Start_date__c] >= [Opportunity History.First Signature Date] then [Start_date__c] else if [Opportunity History.First Signature Date] = "null" then [Start_date__c] else [Opportunity History.First Signature Date]

[onurbmiguel_ · Accepted solution]

Hi again , 

try to add a condition in the begin to check if the signature date is null 

someting like that: 

if
[Opportunity History.First Signature Date] = null
then [Start_date__c]
else
if
[Start_date__c] >= [Opportunity History.First Signature Date]
then [Start_date__c]
else
if
[Opportunity History.First Signature Date] = "null"
then [Start_date__c]
else
[Opportunity History.First Signature Date]

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! ;-
Best Regards
BC

[Anders_G]

That worked very well. Thank you very much for the help!

[onurbmiguel_]

you are welcome. 

BR 

Brunoi