Identify canceled items

[mb0307]

Hi,

Table below shows order detials and in column Canceled Order value 2 represents the canceled order:

If Canceled Type is 2 then I want to find 1 value with the same Order Number, Item, Batch and Quantity as "Remove" and others as "Keep" in IDENTIFICATION column.

It should only cancel one one row with same values even if another exists.  For example row 2,3 and 5 are same but Canceled Order 2 exists in 5 and it should only cancel either row 2 or 3.   

Please note that there can be multiple rows with value 2 per item so improtant to identify based on above criteria.

I want to create a new calculated column IDENTIFICATION  in my table to do this.

Thanks in advance. 

So the result and sum should look like this:

Thanks in advance.

[v-alq-msft · Accepted solution]

Hi, @mb0307 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create an index column in Power Query and then create a calculated column as below.

Result = 
var tab = 
    CALCULATETABLE( 
            'Table',
            FILTER(
                'Table',
                [Canceled Order] = 2&&
                [Order]=EARLIER('Table'[Order])&&
                [Date]=EARLIER('Table'[Date])&&
                [Item]=EARLIER('Table'[Item])&&
                [Quantity]=EARLIER('Table'[Quantity])&&
                [Batch]=EARLIER('Table'[Batch])
            )
    )
var minindex = 
CALCULATE(
    MIN('Table'[Index]),
    FILTER(
                'Table',
                [Order]=EARLIER('Table'[Order])&&
                [Date]=EARLIER('Table'[Date])&&
                [Item]=EARLIER('Table'[Item])&&
                [Quantity]=EARLIER('Table'[Quantity])&&
                [Batch]=EARLIER('Table'[Batch])&&
                [Canceled Order]=EARLIER('Table'[Canceled Order])
    )
)
return
IF(
    COUNTROWS(tab)>0&&[Index]=minindex,
    "REMOVE","KEEP"
)

Result:

Best Regards

Allan

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

[mb0307]

@v-alq-msft  exactly what i wanted. Thanks a lot for your help.

[Fowmy]

@mb0307 

Add the following code as a column to your table:

Keep-Remove = 

var __order = Table3[ORDER]
var __item = Table3[ITEM]
var __qty = Table3[QUANTITY]
var __batch = Table3[BATCH]
var __table = 
    CALCULATETABLE( 
        Table3,
        FILTER(
            Table3,
            Table3[CANCELED ORDER] = 2)
    )
var _tablefilter = 
    FILTER(
        __table,
        Table3[ORDER] = __order && Table3[ITEM] = __item && Table3[QUANTITY] = __qty && Table3[BATCH] = __batch)
return

IF( ISEMPTY(_tablefilter) , "KEEP", "REMOVE")

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[mb0307]

@Fowmy   Thanks for the code - much appreciated.

1)  i think you wanted to swap KEEP and REMOVE below?

IF( ISEMPTY(_tablefilter) , "KEEP", "REMOVE")

2)  Your code only populated Remove for Canceled Order 2 but it doesn't find an equivalent Quantity based on Order, Item, Batch and Quantity. 

It should only cancel one one row with same values even if another exists.  For example row 2,3 and 5 are same but Canceled Order 2 exists in 5 and it should only cancel either row 2 or 3.   

I have updated the table image to make it more clear.

Please could you review your code?

Thanks