PQ Slowness, parameter and vba

[Thamtchou5]

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 ?

[Thamtchou5 · Accepted solution]

Not sure why I can not see my reply here

[v-dineshya · Accepted solution]

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

[Thamtchou5]

Thank you Dinesh. Indeed, i will follow each of your detailed step. Thanks a lot for your time and clarity.

[Thamtchou5 · Accepted solution]

Not sure why I can not see my reply here

[Thamtchou5]

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 

[Thamtchou5]

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).

[ralf_anton]

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

[nathancwatkins]

Buffer that list. ...each List.ContainsAny(List.Buffer(FilterListe), [Buchstabe]))...

--Nate

[Thamtchou5]

Not sure I can not see my reply here

[Thamtchou5]

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 

[ralf_anton]

...ah... kann es sein, dass Du die Daten aus 50 Dateien zusammenführen aber nur die Summen oder die Anzahl (, andere oder keine Aggregation...) jedes Einzelproduktes sehen möchtest? Dann solltest Du Deine #3 nach Produkt gruppieren.

Oder möchtest Du für jedes Einzelprodukt eine separate Zusammenfassung aus allen 50 "Filial"-Dateien in 50 neue "Produkt"- Dateien? 

Jetzt ergibt der Einsatz von VBA auch einen Sinn... wobei das Speichern von 50 Dateien dennoch geraume Zeit beanspruchen wird...

In diesem Fall würde ich eher die Verwendung von Pivottables empfehlen. Die Produktliste selbst kannst Du aber statt über VBA viel einfacher via List.Distinct( Quelle#3[Produktspalte]) in Power Query erstellen

[ralf_anton]

---

@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...

---

[Thamtchou5]

Thanks again for your time Ralf. Indeed as first stage, I would need to improve structure of my queries, then once I have the right way to merge all data, I need to have a filter and save outputs in different sheets.

Basically, once structure is ok, I need to have a global view of full set of data, and I need to split them and share them with respective salesperson.

For this split, I am thinking at vba, new sheet etc... but o am of course happy to challenge my perspective.

[ralf_anton]

---

@Thamtchou5 wrote:

Thanks again for your time Ralf. Indeed as first stage, I would need to improve structure of my queries, then once I have the right way to merge all data, I need to have a filter and save outputs in different sheets..

---

Was ist denn an der Struktur Deiner Abfragen falsch? Das kann ja keiner beurteilen, ohne sie zu kennen. Kannst Du sie hier mal posten?

---

@Thamtchou5 wrote:

Basically, once structure is ok, I need to have a global view of full set of data, and I need to split them and share them with respective salesperson.

For this split, I am thinking at vba, new sheet etc... but o am of course happy to challenge my perspective.

---

Oder meinst Du vielleicht, dass die einzulesenden Dateien unterschiedliche Strukturen haben, die Du vereinheitlichen willst und musst?

Ich denke, ohne Beispiele kommen wir hier nicht weiter.

Zur Aufteilung für die Verkäufer: Ja, da bietet sich VBA an.

Allerdings könntest Du jedem Verkäufer auch ein PQ Template bereitstellen, dass sich die entsprechend gefilterten Daten selbständig aus Deiner #3 zieht.

[GeraldGEmerick]

@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. 

[Thamtchou5]

Hi @GeraldGEmerick 

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.