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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Isambard
Frequent Visitor

Filter table by another table in power query

I have several tables each with [Index ID] as a collumn. I also have one table 'Index' which is just a list of valid IDs. I want to filter all my other tables to include only the IDs from the 'Index' table. All the index values are integars but there are lots of them and they change periodically so its not pratical to enter the filter details manually. 

 

I know there are measures that could do this within a visual, but I really want to do it within power query rather than having to make loads of measures. If the filtered out values could simply not be loaded into the model at all that would be great. 

 

I also know this could be done by merging the 'Index' table into all the others and then filtering that way, but I'd prefer to keep them unmerged if possible.

 

Is there an effecient way to do this?

 

Thanks.

3 REPLIES 3
Caelan
Frequent Visitor

I'd think the best way to filter all reports with the index table would be to set relationships between the tables in power bi then apply a filter to all pages of your report to include all of the index IDs. Would this not work for you?

 

I note also that you've mentioned you don't want to merge as it will slow things down. I don't think merging will show it down very much. You're not going to be loading any of the index table data onto the other tables - you'll just be merging with it to filter the other tables, after which you can delete the extra column. You could give it a go and see how much it slows things down - I doubt it'll be significant.

dufoq3
Super User
Super User

Hi @Isambard, why don't you want to use Merge? It is fast solution. Just merge your tables based on Index value and then use filter.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

I've always assume dit would slow things down, basically loading in the same data twice. Is this not the case?

 

Thank you.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors