Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
Tips and Tricks
Tutorial Requests
  • artemus's avatar
    artemus
    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

  • Mariusz's avatar
    Mariusz
    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



Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    Mariusz
    Community Champion
    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