Exclude values from Table B based on matching values in Table A, + extra condition between 2 values

[Anonymous]

Hello,

I have Table A with a list of "item" values that I need to exclude from Table B. So far so good, I use:

#"Filter" = Table.SelectRows(#"Table B", each not List.Contains(#"Table A"[#"item"], [item]))

Now I need to add an additional condition to this filter, the "item" value needs to be excluded ONLY if its "date" is more recent than the corresponding "date" in Table B:

 #"Table A"[#"email"] = #"Table B"[#"email"] & #"Table A"[#"date"] > #"Table B"[#"date"]

Even more, based on this condition between Table A and B, I need to exclude these items from Table C.

I cannot use Relashionships for other reasons. I tried to exclude values from Table A, then in B, but I incurr in a loop error. I tried other ways, but no luck so far. Any ideas?

Example Tables:

Table A			Table B			Table C
date	item		date	item		date	item
02/01/2020	A		03/01/2020	B		01/01/2020	A
05/01/2020	B		01/01/2020	D		01/01/2020	F
04/01/2020	C		05/01/2020	C		06/01/2020	B

[artemus · Accepted solution]

#"Filter" = Table.SelectRows(#"Table B", each 
let
   rowA = #"Table A"{[item = [item]]}?,
   rowC = #"Table C"{[item = [item]]}?
in
   not (rowA <> null and rowA[email] = [email] and rowA[date] > [date]) and not (rowC <> null and rowC[email] = [email] and rowC[date] > [date])
)

Only works if [item] is unique in the tables. If performance is bad add Table.AddKey(PreviousStep, {"item"}, true) to the end (or middle) or your queries

[artemus · Accepted solution]

#"Filter" = Table.SelectRows(#"Table B", each 
let
   rowA = #"Table A"{[item = [item]]}?,
   rowC = #"Table C"{[item = [item]]}?
in
   not (rowA <> null and rowA[email] = [email] and rowA[date] > [date]) and not (rowC <> null and rowC[email] = [email] and rowC[date] > [date])
)

Only works if [item] is unique in the tables. If performance is bad add Table.AddKey(PreviousStep, {"item"}, true) to the end (or middle) or your queries

[Mariusz]

Hi @Anonymous 

Please see the attached file with a solution

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Thanks fro the swift reply @Mariusz !

I only have a problem opening the file: Object reference not set to an instance of an object.

[Mariusz · Accepted solution]

Hi @Anonymous 

This is the script for Table B

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDWNzDUNzIwMlDSUXJSitUBChkiCblAhEyRhJyVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t, item = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"item", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", ( a ) => Table.RowCount( Table.SelectRows( #"Table A", ( b ) => b[item] = a[item] and b[date] < a[date] ) ) = 0 )
in
    #"Filtered Rows"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn