Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
Employee

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
Frequent 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
Employee
Employee

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.

Anonymous
Not applicable

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

Anonymous
Not applicable

HowardLJ - did you find a workaround for large datasets?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors