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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anders_G
Helper I
Helper I

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

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!

 

Anders_G_0-1660730392977.png

 

1 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

 


Best regards


Bruno Costa | Super User


 


Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 


Take a look at the blog: PBI Portugal 


 


View solution in original post

9 REPLIES 9
davehus
Memorable Member
Memorable Member

Hi @Anders_G ,

 

davehus_0-1660731360703.png

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.

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]

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

davehus_0-1660732500711.png

 

 

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:

Anders_G_0-1660732760908.png

 

onurbmiguel_
Super User
Super User

Hello Anders_G

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

BR 

Bruno

 


Best regards


Bruno Costa | Super User


 


Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 


Take a look at the blog: PBI Portugal 


 


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]

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

 


Best regards


Bruno Costa | Super User


 


Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 


Take a look at the blog: PBI Portugal 


 


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

you are welcome. 

 

BR 

Brunoi 

 


Best regards


Bruno Costa | Super User


 


Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 


Take a look at the blog: PBI Portugal 


 


Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Solution Authors