Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculating Min date and Max date from multiple date columns for each row in Power query.

Finding Min(Start) date and Max(End) for below sample data- For each Row. Some of the dates will be blank as well   NPI Program P/n count est Mfg Facility Location Launch GW1  GW2 GW3 PD1...
Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    Hi Anonymous ,

     

    Try these calculations in new custom columns:

     

    // Start Date
List.Min({[Launch], [GW1], [GW2], [GW3]... })

// End Date
List.Max({[Launch], [GW1], [GW2], [GW3]... })

     

    Pete

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago

I have tried your formula but it's not working for me. I am using this formula in Custom column power Query. I am Supposed to get the date 12/31/2025 but I am getting null. All my date columns are Date type.

IDDate1Date2Date3Date4Date5
A1/1/1999nullnull3/4/202512/31/2025

if [Date1] = "1/1/1999"
and [Date2] = null
and [Date3] = null
then List.Max({[Date4], [Date5]})
else null)

 

 

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    Hi,

     

    I think you need to specify the initial date type correctly, like this:

    if [Date1] = #date(1999, 01, 01)
    and [Date2] = null
    and [Date3] = null
then List.Max({[Date4], [Date5]})
else null

     

    Pete