PQ Slowness, parameter and vba

Not sure why I can not see my reply here

Hi Thamtchou5 ,

Thank you for reaching out to the Microsoft Community Forum.

Please try below things.

1. Power Query should load and merge everything once. Produce one clean, final fact table. Splitting should happen in Excel (PivotTables / formulas) or downstream logic (VBA, Power Pivot, Power BI).

2. Parameters are for environment config (paths, dates, cutoffs), Not for iterating business entities (products, salespeople, regions). If you need 50 outputs --> PQ returns 1 table, not 50 queries.

Please try below target architecture.

Query 1: Static data (OneDrive)

Load once, Clean types, Remove unused columns early and Disable load (staging query).

Query 2 : Folder source (50 files)

Combine files, Promote headers once, Normalize column names, Remove unnecessary columns and Disable load (staging query).

Query 3 : Merge Query 1 + Query 2

Apply all joins once, Apply all business logic once.

Note: Load this query only to Excel Data Model or worksheet. Everything else references this result, not re-runs logic.

Query 4: Instead of 50 filtered PQ queries, You can create ONE master table

and Split downstream.

To split per salesperson try below thing.

Load Query 3 to Data Model and Create a PivotTable. Add Salesperson as a filter. Copy PivotTable --> change filter --> export.

Please try below things to increase Power Query performance.

1. Reduce columns BEFORE merge.

2. Avoid repeated references.

3. Merge order: First filter rows and then Merge and Expand only needed columns.

4. Disable load for staging queries, Only one query should load.

Note: “parameter table” should become a normal dimension table and used for filtering downstream, not query execution.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh