Compare 2 tables and return results

[hemasnaidu]

Hi, I have 2 tables: 

Table 2 able to link to Table 1 using 'Table1ID'.

I want to have 2 results... UnMatch and Match as below 

Adding logics:

The match are only for State='DOWN'. For other than State='DOWN', can be ignored.

For any State = 'DOWN' in Table 1, it should have a follow up record in Table 2. 

In Table2, each new record referenced to Table1 using 'Table1ID' column. 

[lbendlin]

what is the logic for your unmatch?  What happened to 11111 and 44444 ? Should the State="UP"  be ignored?

[hemasnaidu]

Added this logics to original post 

Adding logics:

The match are only for State='DOWN'. For other than State='DOWN', can be ignored.

For any State = 'DOWN' in Table 1, it should have a follow up record in Table 2. 

In Table2, each new record referenced to Table1 using 'Table1ID' column. 

[Anonymous · Accepted solution]

Hi @hemasnaidu 

You can use Merge queries feature. For match results, select Inner Join kind. For unmatched results, select Left Anti kind. Then select rows with State 'DOWN'. Use Merge Queries as New to have match and unmatch results in new queries separately. You can see an example in the attached pbix file.  

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!