Forum Discussion

Centaur1's avatar
Centaur1
Regular Visitor
1 year ago
Solved

Compare 2 date fields and use Greater than date

Hello,

 

I am a novice user of PQ. 

I need to compare 2 dates I have:

Date1 and Date2

If Date2>=Date1 then use Date2 else use Date1

I added a conditional column but I think the issue is that I need to handle NULLS on Date2 but not sure how to do this.  

here is the Custom column I added:

= Table.AddColumn(#"Reordered Columns", "Custom", each if [Date1] >= [Date2] then [Date2] else [Date1])

 

If Date2 is NULL then use Date1 is how I need to handle NULLS.

 

thank you

 

Need Help
Tips and Tricks
  • BeaBF's avatar
    BeaBF
    1 year ago

    Centaur1 Try:

    = Table.AddColumn(
    #"Reordered Columns",
    "Custom",
    each
    if [Date2] = null then [Date1]
    else if [Date2] >= [Date1] then [Date2]
    else [Date1]
    )

     

    BBF

3 Replies

  • Centaur1's avatar
    Centaur1
    Regular Visitor
    1 year ago

    BBF, that was perfect!  thank you very much.  

  • BeaBF's avatar
    BeaBF
    Super User
    1 year ago

    Centaur1 Try:

    = Table.AddColumn(
    #"Reordered Columns",
    "Custom",
    each
    if [Date2] = null then [Date1]
    else if [Date2] >= [Date1] then [Date2]
    else [Date1]
    )

     

    BBF

  • dufoq3's avatar
    dufoq3
    Community Champion
    1 year ago

    Hi Centaur1, another solution:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLQNzTUNzIwMlHSUTK0hHNidaKVDE2R5CAixlARU5BqczgnNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date1 = _t, Date2 = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date1", type date}, {"Date2", type date}}),
    Ad_MaxDate = Table.AddColumn(ChangedType, "MaxDate", each List.Max( { [Date2] ?? [Date1], [Date1] } ), type date)
in
    Ad_MaxDate