Delete rows based on conditions in another table

[maclura]

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

[Greg_Deckler]

@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"

[maclura · Accepted solution]

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