Forum Discussion
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 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 | ???? | ??? |
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
8 Replies
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
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,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
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
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.
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)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 nullPete
