Solved: Filter one query by a column of another query

Terrassa · ‎09-19-2023

Hello,

I have two queries in Power Query. One query imports all the material master table from the ERP, which includes materials of all the plants of the company (almost 3 million rows and increasing very fast). This table doesn't have any field to filter only the materials of the plant I'm interested in.

The other query imports a table of specific parameters of materials per plant, so it has include the field for the materials (to be used as a link with the material master table) and a field for the plant. This query only has 80K rows.

I'd like to filter the query of the material master to import only the materials that exist in the query of materials per plant, so only 80K rows are imported in both queries. I don't want to merge both tables in one single query but to keep both tables separated.

Is this possible in Power Query?

ImkeF · ‎09-19-2023

Hi @Terrassa ,
yes, that's possible:
You start from having both tables imported.
Then add a step to the first materials master table that filters that table to only include that rows with matching entries in the 2nd table.
This can be done by a merge with inner join for example.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

ImkeF · ‎09-20-2023

Hi @Terrassa5 ,
that would be called "query folding".
If both tables come from the same source/database, folding should happen.
But if the small tables comes from a non-sql-source, there is a limitation unfortunately.
You would have to create a filter statement like described here:
Solved: Parameterized SQL Query with query folding - Microsoft Fabric Community
for more information about it, please check out this article as well:
SQL-query folding bug still alive and sucking in PowerBI and PowerQuery in Excel – (thebiccountant.c...

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

ImkeF · ‎09-19-2023

Hi @Terrassa ,
yes, that's possible:
You start from having both tables imported.
Then add a step to the first materials master table that filters that table to only include that rows with matching entries in the 2nd table.
This can be done by a merge with inner join for example.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Terrassa5 · ‎09-19-2023

I would like to avoid importing the whole material master table because it's increasing very quick and soon it will be more than 10 million records and the updates will be very slow (incremental update is not possible).

Is it possible to do the filtering before importing so only the records of the materials that exist in the materials per plant table are imported?