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

[Anonymous]

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 Mat'l Received Due Date	PD1 Build	Start (MIN)Date	End (Max) date)
ABC	12	XYZ	1/1/2021	2/1/2021		6/1/2021	3/1/2021	4/1/2021	????	???

[Syndicate_Admin]

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.

ID	Date1	Date2	Date3	Date4	Date5
A	1/1/1999	null	null	3/4/2025	12/31/2025

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

[BA_Pete]

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

[CNENFRNL]

[BA_Pete]

Hi @CNENFRNL ,

I like this solution, much tidier than mine, but I can't seem to work out how this only picks out the dates for evaluation nad no other values/numbers.

In OP's example data, there is a field that just contains the number 12. How is that not lower than the integers behind the date fields?

Hope this makes sense.

Pete

[CNENFRNL]

Hello, my friend, for a formula

List.Min({"ABC", 10, #date(2021,11,18), null})

I'm sure that string and null value are to be skipped; but frankly speaking, I didn't know why the result is #date, rather than 10.

I turned one of my friends for help in the hope of some clue.

[BA_Pete · Accepted solution]

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

[Shivam_Kathpal]

Hi @BA_Pete 

Thanks for the solution, I also kudoed your post and I tired the mquery and its working good, but since we have data as direct query from sql, so we cannot use calculated column function in direct query, can you suggest some dax also to get the same solution by using measure or dax.
Thanks,

[BA_Pete]

Hi @Shivam_Kathpal ,

I'd recommend opening a new topic on the Desktop forum.

Direct Query can be a bit tricky for writing some more advanced measures, and DAX isn't really my strong point to be honest.

Pete