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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.