Want to Delete and Retain Rows based on a Specific Value in the Column

[yogesh9ngp]

Hello, I am working on a project and I am stuck on a problem. I want to delete Rows based on multiple conditions on more than one column. Following is the Example of the Data.

Customer_Number	Date	Appointment_Status	Verdict
6298430	1/14/2019	A
6298490	1/16/2019	A
6298490	2/20/2019	A
6298490	3/13/2019	A
6298490	3/14/2019	A
6298490	8/29/2019	A
6298490	8/29/2019	A
6298490	9/17/2019	A
6298490	1/27/2020	A
6298490	2/12/2020	A
6298490	3/4/2020	A
9485034	1/30/2019	A
9485034	1/31/2019	A
9485034	4/29/2019	A
9485034	5/17/2019	A
9485034	11/19/2019	A
9485034	6/12/2020	S

I would like the Code to read through Each and Every line and use the following Logic.

1. For the First Customer_Number (6298490) :

       * I would want the code to check for each date if there is a Corresponding Value of "S" in the (Appointment_Status) colum . As            soon as it detects tje value "S" it should stop the detection for that particular Customer_Number and it should say as                        "Appointment Scheduled" in the column (Verdict) next to it. 

       * IF there is no Corresponding Value of "S" then it should "DELETE" all the earlier rows but keep the row with the most recent             date and say as " NO - Appointment Scheduled" in the column (Verdict) next to it.

       * Once it has gone through all the rows of the First Customer_Number it should repreat it for the next Customer_Number.

The Desirable Output I am looking for is :

Customer_Number	Date	Appointment_Status	Verdict
6298430	1/14/2019	A	This row should be Deleted
6298490	1/16/2019	A	This row should be Deleted
6298490	2/20/2019	A	This row should be Deleted
6298490	3/13/2019	A	This row should be Deleted
6298490	3/14/2019	A	This row should be Deleted
6298490	8/29/2019	A	This row should be Deleted
6298490	8/29/2019	A	This row should be Deleted
6298490	9/17/2019	A	This row should be Deleted
6298490	1/27/2020	A	This row should be Deleted
6298490	2/12/2020	A	This row should be Deleted
6298490	3/4/2020	A	NO- Appointment Scheduled
9485034	1/30/2019	A	This row should be Deleted
9485034	1/31/2019	A	This row should be Deleted
9485034	4/29/2019	A	This row should be Deleted
9485034	5/17/2019	A	This row should be Deleted
9485034	11/19/2019	A	This row should be Deleted
9485034	6/12/2020	S	Appointment Scheduled

So, Finally it would look like:

Customer_Number	Date	Appointment_Status	Verdict
6298490	3/4/2020	A	NO- Appointment Scheduled
9485034	6/12/2020	S	Appointment Scheduled

Please Help.

Regards,

Yogesh

[v-frfei-msft · Accepted solution]

Hi @yogesh9ngp ,

We can create a calculated table as below.

Table 2 = 
VAR k =
    ADDCOLUMNS (
        'Table',
        "A",
        VAR a =
            ADDCOLUMNS (
                'Table',
                "days", ABS ( DATEDIFF ( TODAY (), 'Table'[Date], DAY ) )
            )
        VAR days_ =
            ABS ( DATEDIFF ( TODAY (), 'Table'[Date], DAY ) )
        VAR b =
            MINX (
                FILTER (
                    a,
                    'Table'[Appointment_Status] = EARLIER ( 'Table'[Appointment_Status] )
                ),
                [days]
            )
        VAR s =
            CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER (
                    'Table',
                    'Table'[Appointment_Status] = "S"
                        && 'Table'[Appointment_Status] = EARLIER ( 'Table'[Appointment_Status] )
                )
            )
        RETURN
            IF (
                days_ = b
                    && 'Table'[Appointment_Status] <> "S",
                "NO- Appointment Scheduled",
                IF ( 'Table'[Appointment_Status] = "S", "Appointment Scheduled" )
            )
    )
RETURN
    FILTER ( k, [A] <> BLANK () )

[Greg_Deckler]

Create a custom column in Power Query that implements your logic and returns 1 if it does and 0 if it doesn't. Now, filter the table for just 0 values. Remove column.