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, 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.

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.

     

4 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    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