Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. 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?
User | Count |
---|---|
8 | |
6 | |
6 | |
5 | |
5 |
User | Count |
---|---|
9 | |
8 | |
6 | |
6 | |
6 |