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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors