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 dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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??
Solved! Go to 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"
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"
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.
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?
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.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
58 | |
45 | |
42 |