how to reduce load size of multiple tables based on table 1 to Table 2 and then table 2 to many

[Artofdax]

So I manage to filter Table2 by merging Table 1 content and applying filtering value based filter now i need to apply table 2 filter to additional 10 tables

when applying same steps, based on Table 2 Im keep getting connection errors saying that duplicated values are not permitted and iam trying to avoid many to many relations.

all i really need is efficient way to filter content of table 2 based on table 1 and then filter rest on Table 2

[Artofdax]

@ Amira

Thank you for pointing me in right direction

Additional steps to make this work - listlbuffer /distinct

[v-kpoloju-msft]

Hi @Artofdax,
Thank you for reaching out to the Microsoft fabric community forum.  Thank you @AmiraBedh, for your inputs on this issue. 

After reviewing the details you provided, I have identified few workarounds that may help resolve the issue. Please follow these steps:

To filter Table2 based on Table1 using Merge Queries instead of List.Contains, follow these steps in Power BI's Power Query Editor: Select Table2, navigate to Home → Merge Queries, and merge Table2 with Table1 on the KeyID column using Inner Join (only matching rows). Expand the merged column to remove unnecessary fields and rename the filtered table to FilteredTable2.

To filter other tables based on FilteredTable2, repeat the merge process for each additional table (e.g., Table3, Table4, etc.): Select the table you need to filter (e.g., Table3). Navigate to Home → Merge Queries. Merge the selected table with FilteredTable2 using the appropriate key (e.g., ProductID or TransactionID).

Expand the merged column and remove any unnecessary fields. Rename the table as FilteredTable3 or another relevant name.

Kindly refer to the below following link for more information:
Merge queries overview - Power Query | Microsoft Learn

I hope this could resolve your issue, if you need any further assistance, feel free to reach out. If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[AmiraBedh · Accepted solution]

You can use Power Query.

Let’s assume:

• Table1 has a column KeyID

• Table2 also has KeyID

// Reference Table1
let
    Table1Keys = Table.Distinct(Table1[KeyID])
in
    Table1Keys

Then in Table 2 :

= Table.SelectRows(Source, each List.Contains(Table1Keys, [KeyID]))

Let’s say the other tables also have a shared column with Table2 for example, TransactionID, ProductID...

You create a distinct list from the filtered Table2:

let
    Table2Keys = Table.Distinct(FilteredTable2[ProductID])
in
    Table2Keys

Then for each of the other tables, use:

= Table.SelectRows(OtherTable, each List.Contains(Table2Keys, [ProductID]))

[Artofdax]

not sure what I’m doing wrong but loading takes forever

[lbendlin]

What's the data source?  Are the queries folding?

[Artofdax]

sql and I think flooding is disabled ..  not sure how to solve this when i do merge / filter / remove data it takes few min per table but when using  SelectRows/List.Contains it takes  forever. all I really want is to reduce data size based on Table1  keys to many tables ..

not sure what I’m doing wrong