Forum Discussion

mb0307's avatar
mb0307
Responsive Resident
5 years ago
Solved

Identify canceled items

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, Ite...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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.

     

Fowmy's avatar
Fowmy
Super User
5 years ago

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's avatar
    mb0307
    Responsive Resident
    5 years ago

    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