Forum Discussion

kaym's avatar
kaym
Helper I
2 years ago
Solved

remove Max values in an index column

How can i remove the row with highest order count in each shipment? For below data: for example, for shipment # 37136 it should not show the row with order count 4
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Let's say your last step was PriorStep. I would group by Shipment # column, and choose the Max Aggregation for the ORDER COUNT column. Name that step "Group" or whatever you want. Then you can join that table to PriorStep using a left anti join. So after grouping, 

     

    = Table.Join(PriorStep, {"SHIPMENT #", "ORDER COUNT"}, Group, {"GROUPED SHIPMENT # COLUMN", "Max ORDER COUNT"}, JoinKind.LeftAnti)

     

    Side note: using the "#" character in your column name is going to get you into trouble some day, because somehow it'll get parsed as an escape character and mess up your quotes.

     

    --Nate

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Let's say your last step was PriorStep. I would group by Shipment # column, and choose the Max Aggregation for the ORDER COUNT column. Name that step "Group" or whatever you want. Then you can join that table to PriorStep using a left anti join. So after grouping, 

 

= Table.Join(PriorStep, {"SHIPMENT #", "ORDER COUNT"}, Group, {"GROUPED SHIPMENT # COLUMN", "Max ORDER COUNT"}, JoinKind.LeftAnti)

 

Side note: using the "#" character in your column name is going to get you into trouble some day, because somehow it'll get parsed as an escape character and mess up your quotes.

 

--Nate