Forum Discussion
PQ Slowness, parameter and vba
Hi PQ champions,
I have been using PQ for 2 years now as self starter/self learner. I am quite happy for simple tasks but struggle for complex one as below.
I have 4 queries.
2 of them are main sources (static data and Figures) - # 1 and 2
1 of them is merge of both of above. As a result I have only 1200 rows and around 100 columns. #3
The last query #4 is a reference of #3 and filter one column (around 50 times to filter 50 different values stored in parameter).
It painful and super long 1) to update all queries and 2) to do same for the following value in parameter.
- How do u assess my approach, what /how/where can I change order add to decrease significantly update time ?
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
15 Replies
Thamtchou5 What is the source of the data? This could be important as there may be an opportunity to use query folding depending on the source. I feel like if you could store those filter values in a file or table then you could import it and use a join to filter out your values instead of what sounds like 50 different filtering steps.
Source of #1 is one Drive
Source of 2 is my folder root and it combines 50 files from folder.
I would need both
- to have full view of data
- to filter data after merge step so I can also split, save and share separate sheet via vba to sales person for their own checks.
happy to know how I can make all this stuff faster, more simple and more controlled. Thanks.
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 FilternBuffer that list. ...each List.ContainsAny(List.Buffer(FilterListe), [Buchstabe]))...
--Nate
Viele Danke Ralf,
I have translated ur reply.
My query #3 (merged query) is your query 1 (table) and my #4 is your filter, right ?
What do u mean when " filter is done at once " ? Does this allow me to see also result of each filter, copy output to a new sheet, name/save new sheet, and go on following filter
Thamtchou5 wrote:What do u mean when " filter is done at once " ? Does this allow me to see also result of each filter, copy output to a new sheet, name/save new sheet, and go on following filter
Nein, Du kannst keine einzelnen Filterergebnisse sehen, weil es nur einen Filterschritt für alle Filterwerte gibt. Ich dachte, Du wolltest die 50 einzelnen Filterschritte vermeiden? Wenn Du unterschiedliche Filter verwenden willst, dann müssen die zu filternden Werte entsprechend angepasst werden. Das ginge z. Bsp. über den Einsatz von Pivottables und Slicern. Aber hier kann ich nur spekulieren was Du vorhast...
Not sure I can not see my reply here
Hi Gerald,
Source of #1 is one Drive
Source of 2 is my folder root and it combines 50 files from folder.
I would need to filter after merge step so I can also make checks for the whole set of data (and not for only one product separately).
Also Gerald, my parameter is on excel table that I also load as a query. One of the vba task is to go through the list of products and store them one by one as parameter to filter in query 4
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
Thank you Dinesh. Indeed, i will follow each of your detailed step. Thanks a lot for your time and clarity.
