Forum Discussion
Delete Row after condition is met
I am trying to delete status "P" row once there is a date in due date actual for status "I". I can not seem to wrap my head around this one. Any support or help is much needed.
| Status | ID | Complete_1 | Actual_1 | Due_Date_CWV | Due_Date_Original |
| E | LAC497709 | 7/13/2022 0:00 | 7/26/2022 0:00 | 7/26/2022 0:00 | |
| E | LAC497710 | 7/13/2022 0:00 | 7/26/2022 0:00 | 7/26/2022 0:00 | |
| I | ARC187775 | 5/12/2022 0:00 | 5/11/2022 0:00 | 5/17/2022 0:00 | 5/17/2022 0:00 |
| P | KYC199135 | 4/14/2022 0:00 | 5/9/2022 0:00 | 4/21/2022 0:00 | 4/21/2022 0:00 |
| P | KYC199136 | 4/14/2022 0:00 | 4/21/2022 0:00 | 4/21/2022 0:00 | |
| E | KCC217619 | 4/27/2022 0:00 | 4/29/2022 0:00 | 4/29/2022 0:00 | |
| P | KCC217622 | 4/27/2022 0:00 | 4/29/2022 0:00 | 4/29/2022 0:00 | |
| I | OKC254540 | 3/14/2022 0:00 | 4/18/2022 0:00 | 3/18/2022 0:00 | 3/18/2022 0:00 |
| P | OKC254539 | 3/14/2022 0:00 | 3/18/2022 0:00 | 3/18/2022 0:00 | |
| IK | KCC217534 | 4/26/2022 0:00 | 4/29/2022 0:00 | 4/29/2022 0:00 | |
| IK | KCC217535 | 4/26/2022 0:00 | 4/29/2022 0:00 | 4/29/2022 0:00 | |
| P | KCC217540 | 4/26/2022 0:00 | 4/29/2022 0:00 | 4/29/2022 0:00 | |
| P | KCC217541 | 4/26/2022 0:00 | 4/29/2022 0:00 | 4/29/2022 0:00 | |
| P | KCC217542 | 4/19/2022 0:00 | 4/21/2022 0:00 | 4/21/2022 0:00 | |
| P | KCC217543 | 4/19/2022 0:00 | 4/21/2022 0:00 | 4/21/2022 0:00 | |
| E | SLC323114 | 4/20/2022 0:00 | 4/22/2022 0:00 | 4/22/2022 0:00 | |
| E | SLC323115 | 4/20/2022 0:00 | 4/22/2022 0:00 | 4/22/2022 0:00 | |
| P | LAC494416 | 3/16/2022 0:00 | 3/25/2022 0:00 | 3/21/2022 0:00 | 3/21/2022 0:00 |
| P | LAC494417 | 3/16/2022 0:00 | 3/21/2022 0:00 | 3/21/2022 0:00 | |
| E | SAC381351 | 7/27/2022 0:00 | 8/2/2022 0:00 | 8/2/2022 0:00 | |
| E | SAC381353 | 7/27/2022 0:00 | 8/2/2022 0:00 | 8/2/2022 0:00 | |
| E | ARC187020 | 3/11/2022 0:00 | 3/16/2022 0:00 | 3/16/2022 0:00 | |
| E | ARC187021 | 3/11/2022 0:00 | 3/16/2022 0:00 | 3/16/2022 0:00 | |
| IK | ARC186519 | 2/1/2022 0:00 | 2/4/2022 0:00 | 2/4/2022 0:00 | |
| IK | ARC186520 | 2/1/2022 0:00 | 2/4/2022 0:00 | 2/4/2022 0:00 | |
| IK | ARC186916 | 3/10/2022 0:00 | 3/16/2022 0:00 | 3/16/2022 0:00 | |
| IK | ARC186917 | 3/10/2022 0:00 | 3/16/2022 0:00 | 3/16/2022 0:00 |
7 Replies
I have had another look at this. Next time please post some sample data as text. Presumably this is on a per ID basis
--
First, if you add a column (call it DTAndI ) to show if the Status is I and the date is not null (make it a 1 for this condition and 0 otherwise) - that helps in the next step.
--
Then add a custom column:
let AnID = [id] in if [Status] = "P" then Table.MatchesAnyRows(previousStep, each [id] = AnID and [DTAndI] = 1) else nullthat should put TRUE, FALSE or null in the column and you can filter out rows you don't want from the column header.
When I try the information you provided, it has added more rows. I am not sure why. I think it will work if I figure out why more rows are being added. Also, I have added some sample data. Thank you for your support.
In Power Query?
It will be a Table.SelectRows statement and the way to get the correct syntax is to get powerbi to generate it for you by filtering from the column header e.g. the Remove Empty option and then editing it to retain rows that meet 2 conditions: status not equal to I and date not equal to null (you'll have to write the right syntax for that bit)
I have tired as per your comments. What column should I put the filter on?
post your advanced editor code here and i'll have a look
let
Source = Blank,
#"Removed Duplicates" = Table.Distinct(Source, {"041_circuit_layout_order_base_nbr"}),
#"Added Custom" = Table.AddColumn(#"Removed Duplicates", "DTAndI", each if [CLO_Status] = "I" and [Due_Date_Actual] <> null then 1 else 0),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each let AnID = [Combined_CLLI] in if [CLO_Status] = "P" then Table.MatchesAnyRows(#"Added Custom",each [Combined_CLLI] = AnID and [DTAndI] = 1) else null)
in
#"Added Custom1"
The first column (step #"Added Custom") has the wrong name, it needs to be called DTandI. It's called "Custom" at the moment.
The next column has a field [Combined_CLLI]. I don't see it in the data provided but if that's the id field everything should work.
Let me know.
If you get it working, you can filter rows out from the "Custom.1" header
