Compare 2 Tables and filter in Query

[mahra-in]

Hi

I have 2 Tables

Table 1

Project No	Country	Values
573819391	Russia	121
129292BN	India	2324
923TSTD1	Denmark	545
123456	Denmark	213
123456	Denmark	232
	India	832
12111	Italy	121

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 No	Country	Values
573819391	Russia	121
129292BN	India	2324
923TSTD1	Denmark	545
123456	Denmark	213
123456	Denmark	232

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

Regards

Mahra

[jdbuchanan71]

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]

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

[v-piga-msft · Accepted solution]

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

[jdbuchanan71]

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.