Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.
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.
Solved! Go to Solution.
Add a custom column with:
= List.Contains(InvoiceTable[client_id], [client_id])
Where InvoiceTable is the query name of your invoice table.
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!
Did you try merge query
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?
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.