Forum Discussion

froxas's avatar
froxas
Helper II
6 years ago
Solved

check if value contains in another table

I have "clien's" table with client_id, and i have another table "invoices" where each invoice has client_id.

 

with power query I need to add in "client's" table column yes/no  to check if every client are in "invoice" table.

 

wih the excel it would be simple lookup. how i can do it.

i tried with Table.Contains but did not suceeded.

Need Help
  • artemus's avatar
    artemus
    6 years ago

    Add a custom column with:

    = List.Contains(InvoiceTable[client_id], [client_id])

    Where InvoiceTable  is the query name of your invoice table.

7 Replies

  • artemus's avatar
    artemus
    Microsoft Employee
    6 years ago

    Add a custom column with:

    = List.Contains(InvoiceTable[client_id], [client_id])

    Where InvoiceTable  is the query name of your invoice table.

    • MarkPalmberg's avatar
      MarkPalmberg
      Kudo Commander
      4 years ago

      I implemented this between two tables with fewer than 30K rows each and got a pretty substantial performance hit when going to apply changes, FWIW.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Agreed, I tried this with ~150k rows, and it's unusable. Very good for small datasets though

  • mdutot's avatar
    mdutot
    Frequent Visitor
    4 years ago

    For quick fixes, I merge queries on a common column and then write a custom column that replaces "null" with "no" and everything else with "Yes" 

     

    Merge "Clients" query and "Invoices" queries on Client-ID.

    Expand any Client-ID column
    CustomColumn = if [#"Client-ID"] is null then "No" else "Yes"

    • Pablinho's avatar
      Pablinho
      Helper I
      4 years ago

      This is the actual solution! Thank you so much! This is my first time doing the expand but it worked as a charm! Thank you!