Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mahra-in
Helper II
Helper II

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

1 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.

Untitled.png

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
jdbuchanan71
Super User
Super User

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.

rowcount.jpg

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

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.

Untitled.png

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

Helpful resources

Announcements
PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors