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
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.
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.
Solved! Go to Solution.
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)
2. Now, I create a custom column (with owner and begEndDate):
3. Once done it, I duplicate the table and in the new one, I group by the custom column
4. I will get a table like this:
5. I combine the table to get the count in the original table:
6. I filter those with only a 1
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
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)
2. Now, I create a custom column (with owner and begEndDate):
3. Once done it, I duplicate the table and in the new one, I group by the custom column
4. I will get a table like this:
5. I combine the table to get the count in the original table:
6. I filter those with only a 1
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. 🙂
@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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
90 | |
82 | |
76 | |
64 |
User | Count |
---|---|
144 | |
109 | |
108 | |
100 | |
96 |