Forum Discussion
Remove Similar Rows
Hi Sdhn420 ,
Please try to use the following formula to add a new custom column:
=Number.Abs([Sales Value])Then delete the Sales Value column and remove the duplicates rows:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, Sdhn420
Please correct me if I misunderstood your question.
I usually do the below.
- Groupby customer and material by Max/Sum/Min/Avg/etc. of Sales Value
Or,
- I create one more column that only picks positive value, for instance, and "N/A" for negative.
if [Sales Value] >0 then [Sales Value] else "N/A"
Then, I filter out the "N/A" value from the newly created column.
it only remains what you want in the original Sales Value Column.
I hope it helps.
Thank you.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster.
Jihwan_Kim I actually want to remove both the rows.
If customer and material is same and Sales value is same but one is positive and another is negative then remove both the rows.
Hi, Sdhn420
In that case, I do by following the below steps. It is quite many steps, but I prefer using UI, instead of writing M-Code by myself.
- Duplicate the table
- in the second table, group by Customer + Material by count rows (operation)
- in the result column, only remain 1, and filter out the rest.
- The second table only contains customer lists/Matrials that have only one sales value.
- Merge the data from the first table.
It is quite a many steps process but I think this will hardly create any mistakes.
Thank you.
Jihwan_Kim How will it work with below?
Customer Material Sales Value 123 ABC 2000 123 ABC -2000 123 ABC 2000 123 XYZ 1000 Output should be:
123 ABC 2000 123 XYZ 1000
