Show matching values from different queries

[fingalbrad]

HI I have a column called Assembly Part Failure ID from one query (excel sheet) and I have another query (excel sheet) with the same Assembly Part Failure ID. 

I want to create a custom column showing yes or no as to whether the assembly part failure ID matches a value from the other query and vice versa. 

How do I do this??

[v-frfei-msft · Accepted solution]

Hi @fingalbrad ,

Merge the two tables by both columns, M code for your reference.

let
    Source = Table.NestedJoin(Table1, {"Assembly Part", "id"}, Table2, {"Assembly Part", "id"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Assembly Part"}, {"Table2.Assembly Part"})
in
    #"Expanded Table2"

[d_gosbell]

In the query editor on the far right hand side of the Home ribbon is a section called "Combine" and in this section is a button called Merge. If you choose this you can do a Left join between your 2 queries joining on the "Failure Part ID" so that you get all the rows from Query 1 and only rows from Query 2 where the selected Part Failure ID exists. Then you could add a calculated column that returns Yes if the ID from query 2 is not blank and a no if it is blank.

[fingalbrad]

HI @d_gosbell 

After I create the merged table could I just then filter out the blanks? And have it do all these steps each time I refresh the data?

---

---

[d_gosbell]

---

After I create the merged table could I just then filter out the blanks? 

---

---

---

Yes, you can do whatever you like with the merged queries. You'd basically bring in one or more columns from the second query then it's up to you what you do with these column,

---

And have it do all these steps each time I refresh the data?

---

---

---

I'm not sure what you mean by "do all these steps". If you mean setting up the merge join, then no, you can save that as part of the steps for query 1 and then it will do the join to query 2 automatically each time you run a refresh.