Forum Discussion
Solved
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 ...
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
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