Forum Discussion
Add Column Based on Whether Value Exists in Other Table
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:
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!
2 Replies
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!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!
