cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
justin22pogi
Regular Visitor

Filter table based on another table

Just want to ask something. I have 2 tables in Powe BI. One contains a data set of 13 million rows and the other table contains one columb with the list of values I want to filter in the main table. I'm trying to create a filtered table containing only all the values in my 2nd table. Is it possible to filter based on the whole list of my 2nd table?
8 REPLIES 8
justin22pogi
Regular Visitor

I'll try doing the calculate table. But why do we need to use EXCEPT?

For the Power Query using Merge as New and Inner Join, do you have a link on the guide on how to donit?

Thanks for the reply?

Below screenshots should help you:

 

Click on Edit Queries Which will take you to Power Query ModeClick on Edit Queries Which will take you to Power Query ModeHave your Table 1 and Table 2 in order and use the Join Condition as neededHave your Table 1 and Table 2 in order and use the Join Condition as needed

Go to  Query Editor - > Select Table 1->Home -> Combine->Merge Queries-> YOur Table1 will appear default, Select Table 2, Select the joining column, Select the join Type as inner join.

 

You should be able to get lots of example in internet. Check the 'Combine Data ' section in this link.  Its just straight forward.

 

https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data

 

Thanks

Raj

nirvana_moksh
Post Prodigy
Post Prodigy

FYI you can also do this at the Power Query level, by doing a Merge as New and using Inner Join 

Is one of these methods faster or more efficent than the other?

If you filter in PowerQuery, then you avoid loading all the "useless" date to the model.

nirvana_moksh
Post Prodigy
Post Prodigy

Can you try this : Table = CALCULATETABLE('TABLE 1',EXCEPT(VALUES('TABLE 1'[ID]),VALUES(TABLE 2 [ID])))

This worked for my particular case. Thank you for posting.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors