Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Syndicate_Admin
Administrator
Administrator

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

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.

Maricel_0-1642408078803.png

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?

1 ACCEPTED SOLUTION
v-janeyg-msft
Community Support
Community Support

Hi, @Syndicate_Admin 

 

According to your description, I made a sample.

vjaneygmsft_0-1642668118641.png

First, add an index column from 1.

Second, create a custom column.

Like this:

vjaneygmsft_1-1642668220773.png

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

Like this:

vjaneygmsft_2-1642668245586.png

Finally, select unique rows.

vjaneygmsft_3-1642669285967.png

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

 

View solution in original post

2 REPLIES 2
v-janeyg-msft
Community Support
Community Support

Hi, @Syndicate_Admin 

 

According to your description, I made a sample.

vjaneygmsft_0-1642668118641.png

First, add an index column from 1.

Second, create a custom column.

Like this:

vjaneygmsft_1-1642668220773.png

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

Like this:

vjaneygmsft_2-1642668245586.png

Finally, select unique rows.

vjaneygmsft_3-1642669285967.png

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
Super User
Super User

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors