Forum Discussion
Exclude values from Table B based on matching values in Table A, + extra condition between 2 values
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 | ||
#"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
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
4 Replies
#"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
Thanks fro the swift reply Mariusz !
I only have a problem opening the file: Object reference not set to an instance of an object.
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