Deleting a row based on conditions in other columns and rows

[TVR0927]

I have what seems like a complicated situation.  I have a ton of data, but here is a sample.

My criteria:

If Inv Rec is blank, but Combo has an Inv Rec # in any other row, then delete the row with the blank Inv Rec(2nd row would need to be deleted)

If Inv Rec is blank, but none of the Combo’s have an Inv Rec # in any other row then keep the row (the last 2 rows would need to stay)

The factors that keep throwing off deleting the rows are the Bundle and Total Quantity, which are looked at in POWERBI as non duplicates so I can’t just say delete the duplicates and I can’t figure out how to separate them out.  Any help would be appreciated. I really hope this makes sense as I’ve been struggling with this for a couple weeks now.  Thanks!

SKU	MD	PLANT	COMBO	BUNDLE	TOTAL QUANTITY	INV REC
106020116	ABCD	1301	1301106020116ABCD	11688758	5148	300006828
106020116	ABCD	1301	1301106020116ABCD	11688751	4796
106020116	ABCD	1301	1301106020116ABCD	11688760	5148	300006828
106020118	CDEF	1301	1301106020118CDEF	11688546	4925	300006850
106020118	CDEF	1301	1301106020118CDEF	11688547	4925	300006850
106028884	EFGH	1301	1301106028884EFGH	11703440	5148
106028884	EFGH	1301	1301106028884EFGH	11703447	5148

[v-yingjl · Accepted solution]

Hi @TVR0927 ,

I can provide two solutions to achieve this:

1. Create a calsulated column like @ akutt mentioned and filter it:

Tag = 
IF(
    'Table'[INV REC] <> BLANK(),
    "REC",
    IF(
        COUNTROWS(
            FILTER(
                'Table',
                'Table'[SKU] = EARLIER('Table'[SKU])&&
                'Table'[MD] = EARLIER('Table'[MD])&&
                'Table'[PLANT] = EARLIER('Table'[PLANT])&&
                'Table'[COMBO] = EARLIER('Table'[COMBO])&&
                'Table'[BUNDLE] = EARLIER('Table'[BUNDLE])&&
                'Table'[TOTAL QUANTITY] = EARLIER('Table'[TOTAL QUANTITY])&&
                'Table'[INV REC] = EARLIER('Table'[INV REC])
            )
        )>1,
        "Stayed",
        "Removed"
    )
)​

2. Use power query with M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rc9LDsMgDEXRvTDOwA9s8zJs8+seoux/GwWpVauKZpCEiQVXOsLrGiAuUQAPXbjdh7EMJMFrtCqczEaDslyTlOOMDFt3XIPmvr6eQVx2v1TLME5zW/uuVTN17aN9NJNTWv6vkdRSpnl5NLSfiixJ9b3pRcj2BA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SKU = _t, MD = _t, PLANT = _t, COMBO = _t, BUNDLE = _t, #"TOTAL QUANTITY" = _t, #"INV REC" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SKU", Int64.Type}, {"MD", type text}, {"PLANT", Int64.Type}, {"COMBO", Int64.Type}, {"BUNDLE", type text}, {"TOTAL QUANTITY", Int64.Type}, {"INV REC", Int64.Type}}),
    Custom2 = Table.AddColumn(
        #"Changed Type","count",each 
            let 
                sku=[SKU],md=[MD],plant=[PLANT],combo=[COMBO],bundle=[BUNDLE],totalquantity=[TOTAL QUANTITY],invrec=[INV REC] 
            in 
                Table.RowCount(
                    Table.SelectRows(
                        #"Changed Type",each [SKU]=sku and [MD]=md and [PLANT]=plant and [COMBO]=combo and [BUNDLE]=bundle and [TOTAL QUANTITY]=totalquantity and [INV REC]=invrec
                    )
                )
    ),
    Custom3 = Table.SelectRows(Custom2,each [INV REC]<>null or [count]<>1),
    #"Removed Columns" = Table.RemoveColumns(Custom3,{"count"})
in
    #"Removed Columns"​

Sample file is attached that depending on your specific situation(table is used DAX and Query1 is used power query), please check and try it: Deleting a row based on conditions in other columns and rows.pbix 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

You might be able to add another column with If/elseif statements in power query with your conditions where Y is the row should stay and N is the row should not stay. 

Then just filter that column for the Y and load the output back into powerBI. 

[TVR0927]

I'm not skilled enough in IF statemetns for this.  I would love any suggestions.  How do you write an if statement for a condition in another column and another row? 

If Combo is a duplicate and no other rows have a blank Inv Rec then Y

and

if Combo is duplicate and Inv Rec in another row is not blank then X?