Add Column Based on Whether Value Exists in Other Table

[Jerid421]

I have a query called Brand that I want to essentially LEFT OUTER JOIN  (if these were SQL tables) to another query called xrefKeyBrand to add one of it's columns (KeyBrandFlag).  The xrefKeyBrand is really just a list of all the "key" brands.  These two queries are joined on the Minor Brand Code field that exists in both queries.  I was able to write an M Query to "look up" the column in the related table, but it acts more like an INNER JOIN and reduces the resulting query's record count down to only what matches. I want all of the records in the Brand query and a new column that has a "1" if there is a matching Minor Brand Code record in the xrefKeyBrand query and a "0" if there isn't.   

Here is my query: 
= Table.AddColumn(#"Filtered Rows", "KeyBrandFlag",
(tblBrnd) => Table.First(
Table.SelectRows(xrefKeyBrand, each Text.StartsWith(tblBrnd[Minor Brand Code], [Minor Brand Code])) [KeyBrandFlag], Int64.Type)

Tables:

[Jerid421]

This isn't exactly how I accomplished it, but it did get me close enough to do what I was trying to do.     I used the merge query.  Just not as new query.  I didn't want a new query.   But thanks for the help!

[Migasuke · Accepted solution]

Hi,

If I understand your problem correctly, the solution should be easy:
1. Create a Merged Table:

2. Use LEFT join (as you can see, only 3 records are matching out of 5)

3.In new Query, expand the second table and use your join column (in my case is Order ID)

4. Create a conditional column, where Order ID == OrderID.1

Let me know if this helps!