Forum Discussion
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
Hi,
im Beispiel nutze ich 2 Tabellen. Quelle (tblDatentabelle) enthält alle Quelldaten und die Tabelle tblFilter mit einer Spalte "Gesetzter Filter", in welcher untereinander alle zu filternden Werte aufgeführt sind.
Die Quelltabelle hat eine Spalte "Buchstabe", die gefiltert werden soll-
Die Spalte "Buchstabe" der Quelltabelle wird im Schritt "Filtern" nach allen Vorkommen dieser Werteliste gefiltert. In einem Schritt. Unabhängig von der Anzahl der zu filternden Werte.
let
//Quelldaten einlesen
Quelle = Excel.CurrentWorkbook(){[Name="tblDatentabelle"]}[Content],
//Liste mit Filterwerten aus der Spalte "Gesetzte Filter" der formatierten Tabelle "tblFilter" erstellen
FilterListe = Excel.CurrentWorkbook(){[Name="tblFilter"]}[Content][Gesetzter Filter],
//Die Spalte "Buchstabe" der Quelltabelle nach allen in der Filterliste vorkommenden Werten filtern
Filtern = Table.SelectRows(Quelle, each List.ContainsAny(FilterListe, {[Buchstabe]}))
in
Filtern
Not sure I can not see my reply here