Forum Discussion
Delete rows based on conditions in another table
Hi,
I have to clean up a table in my data model before to import it in Power BI.
Basically the table I have to filter looks like
| Date | Item.Id |
| 2022-01-01 | A |
| 2022-01-02 | A |
| 2022-01-03 | A |
| 2022-01-04 | A |
| 2022-01-05 | A |
| 2022-01-06 | A |
| 2022-01-07 | A |
| 2022-01-01 | B |
| 2022-01-02 | B |
| 2022-01-03 | B |
| 2022-01-04 | B |
| 2022-01-05 | B |
| 2022-01-06 | B |
| 2022-01-07 | B |
And the table with my filtering criteria looks like
| Item.Id | CreatedDate | IsActive | LastModifiedDate |
| A | 2022-01-03 | FALSE | 2022-01-04 |
| B | 2022-01-05 | TRUE | 2022-01-05 |
The filtered table should contain
| Date | Item.Id |
| 2022-01-03 | A |
| 2022-01-04 | A |
| 2022-01-05 | B |
| 2022-01-06 | B |
| 2022-01-07 | B |
In other words, I'd like to remove all the rows which "Date" for each "Item.Id" is "before" the Item.Id "CreatedDate",
and only if "IsActive" = FALSE,
all the rows which "Date" for each "Item.Id" is "after" the Item.Id "LastModifiedDate" as well.
Thank you for any hint.
maclura
Thanks Greg_Deckler for your suggestion.
It doesn't work anyway. Perhaps because I was not clear in my problem description.
Either way, I solved this problem with a little edit to your code
let Source = Table.NestedJoin(Table, {"Item.Id"}, Table2, {"Item.Id"}, "Table2", JoinKind.RightOuter), #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"CreatedDate","LastModifiedDate", "IsActive"}, {"Table2.CreatedDate", "Table2.LastModifiedDate", "Table2.IsActive"}), #"Added Custom" = Table.AddColumn(#"Expanded Table2", "IsEarlier", each if [Date] < [Table2.CreatedDate] then 1 else 0), #"Added Custom1" = Table.AddColumn(#"Expanded Table2", "IsLater", each if [Tech Active] = "FALSE" then if [Date] > [Table2.LastModifiedDate] then 1 else 0 else 0), #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([IsEarlier] = 0 and [IsLater] = 0)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Table2.CreatedDate", "Table2.LastModifiedDate", "Table2.IsActive"}) in #"Removed Columns"I was not able to include all the logic in a single column, so I preferred to add 2 custom columns for clarity. And this is the solution.
maclura
2 Replies
maclura Try:
let Source = Table.NestedJoin(Table, {"Item.Id"}, Table2, {"Item.Id"}, "Table2", JoinKind.RightOuter), #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {" CreatedDate", " IsActive"}, {"Table2. CreatedDate", "Table2. IsActive"}), #"Added Custom" = Table.AddColumn(#"Expanded Table2", "Custom", each if [Date] < [Table2. CreatedDate] and [Table2. IsActive] = false then 1 else 0), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 0)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Table2. CreatedDate", "Table2. IsActive"}) in #"Removed Columns"Thanks Greg_Deckler for your suggestion.
It doesn't work anyway. Perhaps because I was not clear in my problem description.
Either way, I solved this problem with a little edit to your code
let Source = Table.NestedJoin(Table, {"Item.Id"}, Table2, {"Item.Id"}, "Table2", JoinKind.RightOuter), #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"CreatedDate","LastModifiedDate", "IsActive"}, {"Table2.CreatedDate", "Table2.LastModifiedDate", "Table2.IsActive"}), #"Added Custom" = Table.AddColumn(#"Expanded Table2", "IsEarlier", each if [Date] < [Table2.CreatedDate] then 1 else 0), #"Added Custom1" = Table.AddColumn(#"Expanded Table2", "IsLater", each if [Tech Active] = "FALSE" then if [Date] > [Table2.LastModifiedDate] then 1 else 0 else 0), #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([IsEarlier] = 0 and [IsLater] = 0)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Table2.CreatedDate", "Table2.LastModifiedDate", "Table2.IsActive"}) in #"Removed Columns"I was not able to include all the logic in a single column, so I preferred to add 2 custom columns for clarity. And this is the solution.
maclura
