cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
froxas
Helper II
Helper II

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.

1 ACCEPTED SOLUTION
artemus
Microsoft
Microsoft

Add a custom column with:

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

Where InvoiceTable  is the query name of your invoice table.

View solution in original post

7 REPLIES 7
mdutot
Regular Visitor

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"

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!

PradeepDive
Helper II
Helper II

Did you try merge query 

artemus
Microsoft
Microsoft

Add a custom column with:

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

Where InvoiceTable  is the query name of your invoice table.

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.

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

HowardLJ - did you find a workaround for large datasets?

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Kudoed Authors