Remove Similar Rows

[Sdhn420]

Hi, 

I have data similar to below.

Customer	Material	Sales Value
123	ABC	2000
123	ABC	-2000

How can i remove such rows in power query? Where sales value is positive in one row for same material and customer and negative in another row for same customer and material.

[Anonymous · Accepted solution]

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.

[Jihwan_Kim]

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.

[Sdhn420]

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

[Jihwan_Kim]

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.

[Sdhn420]

@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

[Jihwan_Kim]

Hi, @Sdhn420 

Thank you for your feedback.

My previous process was for removing all that have more than one line. Because I understood that more than one line meant not-unique-information and I thought that I should remove all the not-unique-information.

Is the situation changed?

If I only see the first situation and second situation, can I think like group by customer/material and sum the sales value + do not show the zero value?

What will be the desirable outcome for the below?

Please help me to understand more accurately.