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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors