Forum Discussion

mahra-in's avatar
mahra-in
Helper II
7 years ago
Solved

Compare 2 Tables and filter in Query

Hi

 

I have 2 Tables

 

Table 1

Project NoCountryValues
573819391Russia121
129292BNIndia2324
923TSTD1Denmark545
123456Denmark213
123456Denmark232
 India832
12111Italy121

 

 

Table 2

Project No
573819391
129292BN
923TSTD1
123456

 

Now by comparing Table 2, I want to filter the Table 1 (in Query) with values matching of Project No column

 

the resulted Table 1 shall be as below

 

Project NoCountryValues
573819391Russia121
129292BNIndia2324
923TSTD1Denmark545
123456Denmark213
123456Denmark232

 

After filter the rows where there is no match with Project No column in Table 2 is removed in Table 1

 

Regards

Mahra

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

    Hi mahra-in ,

    Have you solved your problems with the suggestion of jdbuchanan71?

    By my tests with merging the table in query editor, we could get your desired output.

    If you have solved the problem, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

    If you still need help, please feel free to ask.

    Best Regards,

    Cherry

4 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    7 years ago

    You can use a measure to limit the rows returned from Table2.

    RowCount = 
    CALCULATE ( 
        COUNTROWS(Table1),
        INTERSECT(
            VALUES(Table1[Project No]),
            VALUES(Table2[Project No])
        )
    )

    Then you can add it to the table visual or use it as a filter on the visual.

    • mahra-in's avatar
      mahra-in
      Helper II
      7 years ago

      Hi jdbuchanan71

       

      Thanks for you reply.

       

      I dont want in the visual. I want to limit the rows in Power Query Editor itself if not least at Report filter

       

      Because after limiting rows in Table 1 based on Table 2, I need to develop various visuals using Table 1

      • jdbuchanan71's avatar
        jdbuchanan71
        Super User
        7 years ago

        Ahh, in power query you can do that using an innner join from table1 to table2.  This will return only the rows that are in both.