Forum Discussion
Anonymous
6 years agoNot applicable
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...
- 6 years ago
#"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
- 6 years ago
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
artemus
6 years agoMicrosoft Employee
#"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