Forum Discussion
Multiple column date comparison and replace with earliest date
Hi Anonymous ,
We can use the following steps to meet your requirement.
1. Click ID column and unpivot other columns.
2. Copy the table and group one of them.
3. Merge the group table and expand the min date.
4. Then select id column and pivot value column.
5. Use conditional column to create four date columns to separate the null.
6. At last we can add another conditional column, then delete the Date.A, B, C, D.
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Hi,
Share data in a format the can be pasted in an MS Excel workbook and show the exact result you are expecting.
This is a sample of my data and Action required: My code is in powerquery, not excel.
Id DateA DateB DateC DateD Action to take 1 8/25/2020 8/1/2017 8/25/2020 8/25/2020 replace col A, col C and col d with 8/1/2017 2 7/1/2020 7/2/2020 7/3/2020 flag as manual intervention 3 6/1/2020 6/1/2020 6/5/2020 replace col c with 6/1/2020 4 5/1/2020 5/2/2020 flag as manual intervention 5 4/1/2020 4/21/2020 4/21/2020 replace col c and d with 4/1/2020 6 3/1/2020 3/1/2020 4/1/2020 replace col d with 3/1/2020 Id DateA DateB DateC DateD Action to Take 1 8/25/2020 8/1/2017 8/25/2020 8/25/2020 replace col A, col C and col D with 8/1/2017 2 7/1/2020 7/2/2020 7/3/2020 flag as manual intervention 3 6/1/2020 6/1/2020 6/5/2020 replace col c with 6/1/2020 4 5/1/2020 5/2/2020 flag as manual intervention 5 4/1/2020 4/21/2020 4/21/2020 replace col c and d with 4/1/2020 6 3/1/2020 3/1/2020 4/1/2020 replace col d with 3/1/2020 1. If any 3 of the 4 columns have the same date value, then compare that value with the 4th column to find the earliest date and replace all 4 dates with this lowest date. only non blank columns should be replaced.
2. If, out of the 4, some are blank, ignore that for the comparison. i.e if one column is blank, then if 2 columns are same, it can be compared against the 3rd one and earliest date found.
3. if 3 of them do not match, then flag entire row as "manual intervention"
Hi,
Someone else will help you with this please.