Forum Discussion

yogesh9ngp's avatar
yogesh9ngp
New Member
6 years ago
Solved

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

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_NumberDateAppointment_StatusVerdict
62984301/14/2019A 
62984901/16/2019A 
62984902/20/2019A 
62984903/13/2019A 
62984903/14/2019A 
62984908/29/2019A 
62984908/29/2019A 
62984909/17/2019A 
62984901/27/2020A 
62984902/12/2020A 
62984903/4/2020A 
94850341/30/2019A 
94850341/31/2019A 
94850344/29/2019A 
94850345/17/2019A 
948503411/19/2019A 
94850346/12/2020S 

 

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_NumberDateAppointment_StatusVerdict
62984301/14/2019AThis row should be Deleted
62984901/16/2019AThis row should be Deleted
62984902/20/2019AThis row should be Deleted
62984903/13/2019AThis row should be Deleted
62984903/14/2019AThis row should be Deleted
62984908/29/2019AThis row should be Deleted
62984908/29/2019AThis row should be Deleted
62984909/17/2019AThis row should be Deleted
62984901/27/2020AThis row should be Deleted
62984902/12/2020AThis row should be Deleted
62984903/4/2020ANO- Appointment Scheduled
94850341/30/2019AThis row should be Deleted
94850341/31/2019AThis row should be Deleted
94850344/29/2019AThis row should be Deleted
94850345/17/2019AThis row should be Deleted
948503411/19/2019AThis row should be Deleted
94850346/12/2020SAppointment Scheduled

 

So, Finally it would look like:

 

 

Customer_NumberDateAppointment_StatusVerdict
62984903/4/2020ANO- Appointment Scheduled
94850346/12/2020SAppointment Scheduled

 

Please Help.

 

Regards,

Yogesh

 

      

 

 

Data Protection
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    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 () )

     

     

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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.

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    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 () )