Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Multiple column date comparison and replace with earliest date

Hello,

I am a relatively new user to PowerBI. I am finding it challenging to implement some of the requirements using powerBI.  

 

I have 4 date columns, DateA, DateB, DateC and DateD. (Some of these may have blank value)  

I want to loop through these and do the following:

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"  

 

Without having "for" loops, this is a bit challenging for me. 

 

I managed to identify the earliest date for #1 and "manual intervention" for #3 with the help of a bunch of if statements, but:

a. If any column is blank, I dont know how the logic will work

b. I dont know how to flag and replace all the cells with the lowest value found here. 

c. General question. Can "if - then" have multiple statements after then. like if a=b then { c=d; e=f} ? 

 

Here is my code to identify #1 and #3 above: 

= Table.AddColumn(#"Added col for FutureHire", "FutureHires-Updated all dates with original hiredate", each
if ([DateA] = [DateB]) then
     if ([DateB] =[DateC] ) then
           if [DateC] < [DateD] then [DateC] else [DateD]
     else if ( [DateB] = [DateD]) then
            if ([DateC] < [DateD]) then [DateC] else [DateD]
     else "this condition should not arise"
else if ([DateB] = [DateC]) then
        if ([DateC] = [DateD]) then
               if ([DateA] < [DateD]) then [DateA] else [DateD]
        else "this condition should not arise"
else if([DateA] = [DateC] and [DateA] = [DateD]) then
           if ([DateA] < [DateB]) then [DateA] else [DateB]
else "Manual Intervention" 

 

Is my approach completely wrong here? can you help guide me on how to achieve this? To acheive #2 above (if one column is blank), I need to have whole another set of if conditions? 

 

Thanks in advance,

Jas

  • 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.

5 Replies

  • Hi,

    Share data in a format the can be pasted in an MS Excel workbook and show the exact result you are expecting.

    • Anonymous's avatar
      Anonymous
      Not applicable

      This is a sample of my data and Action required:  My code is in powerquery, not excel.  

      IdDateADateBDateCDateDAction to take
      18/25/20208/1/20178/25/20208/25/2020replace col A, col C and col d with 8/1/2017
      27/1/2020 7/2/20207/3/2020flag as manual intervention
      36/1/20206/1/20206/5/2020 replace col c with 6/1/2020
      4 5/1/2020 5/2/2020flag as manual intervention
      54/1/2020 4/21/20204/21/2020replace col c and d with 4/1/2020
      63/1/20203/1/2020 4/1/2020replace col d with 3/1/2020
      • Anonymous's avatar
        Anonymous
        Not applicable
        Id          DateA             DateB            DateC            DateD                 Action to Take
        18/25/20208/1/20178/25/20208/25/2020replace col A, col C and col D with 8/1/2017
        27/1/2020 7/2/20207/3/2020flag as manual intervention
        36/1/20206/1/20206/5/2020 replace col c with 6/1/2020
        4 5/1/2020 5/2/2020flag as manual intervention
        54/1/2020 4/21/20204/21/2020replace col c and d with 4/1/2020
        63/1/20203/1/2020 4/1/2020replace 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"  

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Community Support

    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.