Wielu użytkowników Excela dzieli swoje życie analityczne na „PRZED Power Query” i „PO Power Query”. Power Query zastaje firmę manualnie aktualizowaną, a zostawia zautomatyzowaną (przynajmniej w obszarze raportowania). Ci bardziej zaawansowani wracają jednak po czasie z pytaniem, jak sprawić, aby zapytania Power Query działały szybciej. Jeśli wyłączyłeś już ładowanie niepotrzebnych danych (Załaduj do… > Tylko połączenie) i zastanawiasz się, jak dać zapytaniom dodatkowego kopa, oto 11 praktycznych pomysłów.

Pomysł #1: Przejdź na 64-bitowy Excel lub Power BI

Wersja 32-bitowa ogranicza się do 4GB RAM, a wersja 64-bitowa potrafi efektywniej wykorzystać dostępną pamięć roboczą. Jak sprawdzić swoją wersję Excela?

Reinstalację w dużej firmie trzeba zwykle zlecić w IT. Zanim to zrobisz, sprawdź z kolegami z IT, czy Excel 64-bitowy nie wchodzi w konflikt z wykorzystywanymi w Twojej organizacji nakładkami na Excel od dostawców zewnętrznych, które często są tylko 32-bitowe.

Pomysł #2: Przejdź z plików Excel na CSV

Pobierając dane z systemu źródłowego (np. SAP) masz wybór co do rozszerzenia pliku i jego typu? Zamiast pliku programu Excel wybierz plik tekstowy (np. CSV). Dzięki prostszej strukturze pliki tekstowe są kilkukrotnie szybciej procesowane przez Power Query.

Pomysł #3: Zignoruj sprawdzanie prywatności

Jeśli pracujesz z danymi na dysku sieciowym, Excel zapyta, jak bezpiecznie ma je traktować. Okazuje się, że nasz wybór ma wpływ na wydajność Power Query, czym pisałem już w artykule o poziomach prywatności w Power Query. Jeśli pojawi się okienko z poziomami prywatności, wybierz Ignoruj.

Pomysł #4: Pomyśl nad kolejnością operacji i liczbą kroków

W uproszczeniu: każdy krok przechowuje pełen obraz naszych danych. Jeśli kroków i zapytań jest dużo, ich przetwarzanie w pamięci będzie trwało. Dlatego pomyśl nad kolejnością operacji i ich powtarzalnością:

• najpierw nagraj usuwanie kolumn, grupowanie czy filtrowanie
• nagraj kroki podobne jednocześnie (np. Zmieniono typ danych, Zmieniono nazwy nagłówków)

Pomysł #5: Włącz szybkie ładowanie danych

Szybkie ładowanie danych jest opcją zapytania w programie Excel, dzięki której zapytania wykonują się szybciej, ale okienko Excela jest blokowane, podobnie jak przy VBA.

Pomysł #6: Sprawdź, jak Power Query radzi sobie z tym samym zapytaniem w Power BI

Dlaczego? Power Query w Excelu wczytuje te same pliki wiele razy, podczas gdy Power Query w Power BI potrafi je przechować w pamięci podręcznej. Dlatego warto sprawdzić (np. metodą kopiuj-wklej między Power Query Excela a Power Query Power BI), jak szybko wykona się zapytanie w Power BI. Jeśli jest różnica, w Excelu może pomóc Pomysł #10.

Pomysł #7: Ubierz transformacje w funkcje

Najprostszy przykład funkcji możemy zaobserwować w działaniu Kolumny z przykładów (1).

Zamiast tworzyć oddzielne kroki, Power Query próbuje w tej operacji wykonać wszystko w jednym kroku.

Jeśli przekształceń jest sporo, użytkownik zaawansowany może spróbować zagnieździć funkcję w ramach kolumny i wywołać ją poleceniem Dodaj kolumnę > Wywołaj funkcję niestandardową (2).

Pomysł #8: Wystaw źródło w bazie danych SQL

Co daje natywne połączenie z bazą danych SQL? Query folding. Oznacza to, że operacje wykonywane są na serwerze, a nie na naszym komputerze. Dane potrzebujemy przechowywać w bazie danych SQL (Access nie pomoże) i dobierać się do nich w taki sposób, aby opcja Wyświetl zapytanie natywne nie zgasła. To samo źródło danych, obsługiwane przez ODBC, okazywało się często wielokrotnie wolniejsze niż polecenie Pobierz dane > Z bazy danych.

Pomysł #9: Rozbij proces na kilka etapów

To rozwiązanie wymaga trochę więcej pracy przy odświeżaniu, ale może przynieść oczekiwane efekty. Zamiast wykonywać skomplikowane operacje w jednym pliku Excel z wieloma zapytaniami Power Query, możesz zbudować rozwiązanie kaskadowe:

• zapytanie 1 łączy się z plikiem 0
• zapytanie 1 ląduje w pliku 1 jako tabela
• zapytanie 2 łączy się z tabelą z pliku 1
• zapytanie 2 ląduje w pliku 2 jako tabela
• itd.

Pomysł #10: Wczytaj wszystkie dane do pamięci RAM Power Query

Jedna z zaawansowanych technik wykorzystuje operację wczytania danych do pamięci RAM poprzez operację Table.Buffer. Dzięki temu źródło danych wczyta tam się jeden raz i nie będzie pobierane wiele razy na potrzeby różnych zapytań.

Pomysł #11: Użyj operacji Odwołanie zamiast Duplikuj

Jeśli na jednym źródle tworzysz wiele zapytań, lepiej posłużyć się operacją Odwołanie niż Duplikuj.

Dzięki temu zapytanie bazowe wykona się do pewnego momentu, a potem rozejdzie się po zapytaniach zależnych.

Znasz inne sposoby na przyspieszenie Power Query w Excelu lub Power BI?

Podziel się w komentarzu.