Power Query output value based on matching values from different tables

[Anonymous]

Hey Power BI users,

I am currently trying to narrow down my table rows by filtering. However I need to filter based on a specific value which is not present in one of the tables.

Case:

2 tables, Customer and Product.  Both have millions of rows, out of which only about 500.000 are needed.

Table customer has a unique Customer ID (However its saved multiple times, due to it being saved on multiple dates) To this table I have added a unique row named: 'IsNeeded' with the value 1 and 0 based on a certain criteria (The same criteria can't be used on the other table). All 0's have been filtered.

I want to output the 1 and 0 in the 'Product' table, based on the value off the Customer ID (Dates don't impact the outcome).

I want to lookup the Customer ID on table 'Customer' and match it with the same Customer ID on table 'Product' and then output (if customer Id matches) the value that is in Table 'Customer[IsNeeded]'.

What can I do to achieve this?

Thank you in advance,

Daniël

[PattemManohar]

@Anonymous Any sample data and expected output will be helpful. But from your post, I can understand you are looking for "LOOKUPVALUE" function.

[Anonymous]

(EDIT: I want this to be done in the Power-Query editor)

Sure thing. 

Table: Customer                                                                        Table: Product             (expected output)

CustomerID       Employee         IsNeeded                                Customer ID               IsNeeded

12345                  1                      1                                             12345                         1

23456                  2                      0                                             34567                         1

34567                  1                      1                                             23456                         0 

45678                 4                       1                                             45678                         1

Based on the specific value of Employee (name) I created the IsNeeded column value. However the Employee column is not in the Product table.

Therefore I would like to lookup and compare matching values of the two Customer ID's and if they match, output the value that is written on the Customer[IsNeeded]

Thank you,

Daniël

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may use Merge Queries (Table.NestedJoin) in Query Editor.