Forum Discussion

fingalbrad's avatar
fingalbrad
Icon for Helper I rankHelper I
7 years ago
Solved

Show matching values from different queries

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??

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    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"

     

4 Replies

  • d_gosbell's avatar
    d_gosbell
    Icon for Super User rankSuper User
    7 years ago

    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's avatar
      fingalbrad
      Icon for Helper I rankHelper I
      7 years ago

      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's avatar
        d_gosbell
        Icon for Super User rankSuper User
        7 years ago

        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.

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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"