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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
lukasoes
Regular Visitor

Removal of rows based on duplicates and column value

So I have the following table for vacations. The data is extracted from SAP and it would seem that when a vacation has been requested and approved it has "INS" in the operation column. However, should this request be deleted a new entry, identical to the first will come into the excel file with the data, but with "DEL" in the operation column.

Now my question: How do I delete the rows that are identical, except for the "INS/DEL" in operation?


Below I've added an example of my data. The rows with red at the end are the rows I need deleted. 
As you can see, the requested vacation has been added first, and then later deleted.

lukasoes_0-1692620870252.png

Thank you for taking the time to help 🙂

I'm using this in a matrix to showcase vacations in a calendar and it's working fine, though I have these dates that shouldn't be there, so it's confusing the users.

1 ACCEPTED SOLUTION
mlsx4
Super User
Super User

Hi @lukasoes 

 

The only thing that comes to my mind is that you do the following

 

1. Create a combination of begin and end date ( I have called it begEndDate)

mlsx4_1-1692623842344.png

 

2. Now, I create a custom column (with owner and begEndDate):

mlsx4_2-1692623950260.png

 

3. Once done it, I duplicate the table and in the new one, I group by the custom column

mlsx4_3-1692624023054.pngmlsx4_4-1692624063436.png

 

4. I will get a table like this:

mlsx4_5-1692624103727.png

5. I combine the table to get the count in the original table:

mlsx4_6-1692624166301.png

6. I filter those with only a 1 

mlsx4_7-1692624203528.png

 

Finally, you can remove the count column.

It is a bit messy, but it's the only way I figured out to solve the problem

 

View solution in original post

3 REPLIES 3
mlsx4
Super User
Super User

Hi @lukasoes 

 

The only thing that comes to my mind is that you do the following

 

1. Create a combination of begin and end date ( I have called it begEndDate)

mlsx4_1-1692623842344.png

 

2. Now, I create a custom column (with owner and begEndDate):

mlsx4_2-1692623950260.png

 

3. Once done it, I duplicate the table and in the new one, I group by the custom column

mlsx4_3-1692624023054.pngmlsx4_4-1692624063436.png

 

4. I will get a table like this:

mlsx4_5-1692624103727.png

5. I combine the table to get the count in the original table:

mlsx4_6-1692624166301.png

6. I filter those with only a 1 

mlsx4_7-1692624203528.png

 

Finally, you can remove the count column.

It is a bit messy, but it's the only way I figured out to solve the problem

 

This helped! Thank you! 

I later in the process have created a "dates_between" column and expanded, so at first I thought this would not work. 
However, I did your steps before creating the "dates_between" column and it filtered out the ones that were not needed. 🙂

Mkarwa-123
Resolver II
Resolver II

@lukasoes you can create a new column based on owner, start date and end date and count the number of operation . if no = 2 you can delete else you can keep it. 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.