Data cleansing according to the sequence of one of the columns in the data

[Syndicate_Admin]

Hello

I need to know if there is any way to delete data that the system recorded 2 times, but it has different times so I can not clean them with "delete repeated".

In the image you can see how the user 1, records inputs (type 1) and outputs (type 2). The ideal order is to have an input and then an output, to have one input and one output again, so it happens.

Unfortunately, sometimes, two (or more) outputs are recorded in a row or two different inputs preventing you from properly cleaning the data. Can you somehow ask the power query to look in the Type column that the number in the previous row is NOT equal to the number in the analyzed row and if it is the same, delete the analyzed row?

Example:
Row/ TM/ Type

1 1

2 2

3 1

4 1 -----> row that should be deleted because it has a 1 above (row 3 contains the real data that interests us)

5 1 -----> row that should be deleted because it has a 1 above (row 3 contains the real data that interests us)

6 2

Is there a way to clean up the data and get a list of 1 and 2 without repetitions?

[v-janeyg-msft · Accepted solution]

Hi, @Syndicate_Admin 

According to your description, I made a sample.

First, add an index column from 1.

Second, create a custom column.

Like this:

Third: create a compare column and let duplicate lines show 0.

Like this:

Finally, select unique rows.

Below is my sample.

Did I answer your question? Please mark my reply as solution. Thank you very much.

If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey

[MFelix]

Hi @Maricel ,

To do this you need to select all the columns that have the values you want to keep and that are the same and then seelct the option remove duplicates.

Since you don't present any further columns than the dates and the type is difficult to give you a better explanation.