Dlaczego Power Query nagle zwalnia i kiedy w ogóle się tym przejmować
Typowe objawy, że jedno zapytanie lub krok dusi cały raport
W pewnym momencie praca z Power Query przestaje być płynna: Excel lub Power BI Desktop zaczyna „mielić” przez długie minuty, okno podglądu nie reaguje, a przy próbie zmiany jednego kroku pojawia się komunikat „Oczekiwanie na zapytanie…”. Otwierasz Menedżera zadań i widzisz, że Excel lub Power BI zjada większość procesora i coraz więcej pamięci RAM. Czasem wszystko kończy się komunikatem o braku pamięci lub zwykłym zawieszeniem.
Najczęstszy scenariusz wygląda tak: raport, który działał w kilka sekund, po kilku miesiącach używania nagle potrzebuje kilku minut na odświeżenie. Dane urosły, ktoś dodał kilka kolumn obliczeniowych, pojawiło się nowe źródło, a jedno niepozorne przekształcenie zaczęło być wykonywane na setkach tysięcy wierszy. Dla użytkownika końcowego to „Power Query jest wolne”. W praktyce winny jest zwykle jeden krok lub mała grupa kroków w konkretnej gałęzi zapytań.
Inny objaw to różnica między pracą na podglądzie a pełnym odświeżeniem. Przeskakujesz po krokach, wszystko działa znośnie, ale podczas pełnego odświeżenia raportu całość zatrzymuje się na jednym zapytaniu. Po jego zatrzymaniu lub wyłączeniu inne części modelu nagle działają dużo szybciej. To znak, że warto wejść głębiej i zidentyfikować konkretny krok, który spowalnia Power Query.
Jednorazowe „przemielenie” a codzienna praca – gdzie leży granica bólu
Nie każdy długi czas odświeżania jest problemem wartym naprawy. Jednorazowe przeliczenie dużej bazy (np. migracja z systemu A do B, import historycznych danych z kilku lat) może trwać nawet godzinę i być w pełni akceptowalne, jeśli wykonujesz to raz na rok. Z drugiej strony raport odświeżany codziennie o poranku, który „stoi” 20 minut, bardzo szybko zacznie kosztować realne pieniądze i nerwy.
Praktyczne rozróżnienie:
Proces jednorazowy lub rzadki – jeśli potrzebujesz uruchomić go raz na kilka miesięcy, niewielka optymalizacja zwykle nie ma sensu. Można go puścić w tle lub na dedykowanej maszynie i zająć się inną pracą.
Proces cykliczny – odświeżany codziennie, tygodniowo lub przy każdej aktualizacji raportu. Tu nawet małe skrócenie czasu (np. z 8 do 4 minut) szybko skumuluje się w oszczędność czasu i energii zespołu.
W pracy biurowej granica komfortu często przebiega przy kilku minutach czekania. Jeśli odświeżanie trwa 2–3 minuty i uruchamiasz je raz dziennie, ingerencja w złożony model może być nieopłacalna. Jeśli jednak co godzinę ktoś losowo trafi w przycisk „Odśwież wszystko” i przez 15 minut nic nie może robić, diagnoza i naprawa kroków spowalniających Power Query staje się inwestycją, która bardzo szybko się zwraca.
Prosty rachunek: ile naprawdę kosztuje wolne zapytanie
Najłatwiej podjąć decyzję, czy w ogóle bawić się w optymalizację, kiedy policzysz, ile czasu znika na czekaniu. Wystarczy kartka lub prosty arkusz:
Czas jednego odświeżenia (np. 12 minut).
Liczba odświeżeń dziennie (np. 3 razy).
Liczba dni roboczych w miesiącu (np. 20).
Przy takim scenariuszu pracownik czeka:
12 minut × 3 × 20 = 720 minut, czyli 12 godzin miesięcznie.
Jeśli jesteś w stanie w 2–3 godziny przeanalizować i zmodyfikować zapytanie tak, aby zejść do 5 minut, to każdy kolejny miesiąc daje kilkanaście godzin „odzyskanego” czasu. Na poziomie całego zespołu ta wartość rośnie wielokrotnie. Z perspektywy „budżetowego pragmatyka” pytanie nie brzmi „czy warto optymalizować?”, tylko „od którego raportu zacząć, aby zyskać najwięcej przy najmniejszym nakładzie pracy”.
Kiedy zaakceptować, a kiedy przeprojektować zapytanie
Rozsądne podejście do optymalizacji Power Query zakłada selekcję. Nie warto walczyć o każdą sekundę, jeśli raport i tak startuje raz w tygodniu z automatu na mocnym serwerze. Natomiast trzeba reagować, gdy:
odświeżanie trwa „nienormalnie” długo względem liczby wierszy (np. 15 minut dla 50 tys. wierszy z prostego Excela),
czas odświeżania rośnie w sposób nieliniowy – miesiąc temu było 3 minuty, teraz jest 12, a liczba danych urosła tylko o 30–40%,
zapytanie regularnie wywołuje błędy pamięci lub „wycina” innym programom RAM,
przy każdym odświeżeniu komputer jest przez długi czas praktycznie bezużyteczny.
Jeśli któryś z tych punktów pojawia się w codziennej pracy, najwyższa pora namierzyć krok, który spowalnia Power Query. Zamiast przepisywać wszystko od zera, często wystarczy przeorganizować kilka transformacji, przesunąć filtrację bliżej źródła lub zrezygnować z jednego „niepotrzebnego” sortowania na pełnej tabeli.
Raport miesięczny, który rośnie razem z firmą
Dobrym, bardzo częstym przykładem jest raport miesięczny sprzedaży. Na początku w firmie jest kilkadziesiąt faktur dziennie, a zapytania Power Query pracują na jednym pliku CSV dziennie. Całość odświeża się w kilka sekund, więc nikt nie myśli o wydajności. Po kilku latach:
danych jest już kilkanaście razy więcej,
ktoś dołożył obliczanie marży, grupowanie po produktach, dodatkowe tabele kursów walut,
pojawiło się kilka złączeń typu merge po kolumnach tekstowych.
Nagle raport, który kiedyś odświeżał się w 30 sekund, wymaga 20 minut. Co gorsza, co miesiąc będzie coraz gorzej, bo danych przybywa, a zapytanie nie było projektowane z myślą o takiej skali. Zamiast co kwartał kupować szybszy komputer, lepiej poświęcić parę godzin na identyfikację kroków, które spowalniają Power Query, oraz przebudowę potoku tak, by ciężkie operacje wykonywały się jak najbliżej źródła danych i na możliwie najmniejszych zestawach.
Źródło: Pexels | Autor: RDNE Stock project
Jak działa Power Query „pod maską” – minimum teorii potrzebne do diagnozy
Przepływ danych przez kolejne kroki transformacji
Power Query można traktować jak prosty potok: dane wpływają z jednego lub wielu źródeł, przechodzą przez kolejne kroki, a na końcu lądują w tabeli w Excelu lub w modelu danych. Każdy krok w Edytorze zapytań to funkcja, która przyjmuje tabelę z poprzedniego etapu i zwraca nową tabelę. Łańcuch tych funkcji to w praktyce kod M, który Power Query generuje automatycznie.
Transformacje nie są wykonywane od razu, w chwili ich tworzenia. Power Query stosuje tzw. leniwe wykonywanie (lazy evaluation). Dopóki nie nastąpi potrzeba faktycznego pobrania i przetworzenia danych (np. podgląd kroków, odświeżenie zapytania), edytor zapisuje tylko plan przekształceń. To ważne przy diagnozie: sam fakt, że dodałeś dziesięć kroków, nie jest jeszcze problemem. Problemem jest to, co się dzieje, kiedy ten plan zostanie uruchomiony na pełnym zestawie danych.
Query folding vs transformacje lokalne – klucz do wydajności
Największy wpływ na szybkość działania ma to, gdzie wykonywane są poszczególne przekształcenia:
przekształcenia składane (query folding) – Power Query tłumaczy kroki na język natywny źródła (np. SQL) i zleca ich wykonanie bazie,
przekształcenia lokalne – dane są pobierane do pamięci i transformowane po stronie klienta (Excel, Power BI Desktop).
Jeśli folding działa, to:
do komputera trafia mniej danych (są już przefiltrowane, zgrupowane, zjoinowane),
ciężkie operacje (sortowanie, grupowanie) wykonuje zwykle serwer, który jest zaprojektowany do takich zadań,
zużycie pamięci RAM po stronie użytkownika jest znacznie mniejsze.
Gdy składanie zapytań zostanie przerwane przez jeden „egzotyczny” krok, cała reszta przekształceń musi zostać wykonana lokalnie. Przy małych tabelach różnicę trudno zauważyć. Przy milionach wierszy lokalne przetwarzanie nagle staje się wąskim gardłem i jednym z głównych powodów, dla których pojedynczy krok spowalnia Power Query.
Pamięć RAM kontra procesor – co naprawdę boli przy dużych tabelach
W typowych biurowych scenariuszach to pamięć RAM szybciej staje się problemem niż sam procesor. Power Query tworzy w tle kolejne wersje tabel, czasem trzymając w pamięci kilka dużych obiektów jednocześnie (oryginał, wynik sortowania, wynik złączenia). Gdy brakuje fizycznej pamięci, system zaczyna intensywnie korzystać z pliku wymiany na dysku, co drastycznie spowalnia każdy kolejny krok.
Procesor również ma znaczenie, szczególnie przy:
kolumnach obliczanych opartych na zagnieżdżonych funkcjach,
customowych funkcjach M wywoływanych dla każdego wiersza,
Jednak nawet mocny procesor nie pomoże, jeśli w jednym momencie próbujesz przetworzyć w pamięci zbyt duży, nieprzefiltrowany zestaw danych z wieloma dodatkowymi kolumnami. Dlatego jednym z głównych celów optymalizacji jest zmniejszanie objętości danych możliwie wcześnie: filtrowanie, agregowanie, wybieranie tylko potrzebnych kolumn przed ciężkimi operacjami.
Kiedy zapytanie jest faktycznie wykonywane
Power Query nie „liczy” zapytania przy każdym dodaniu kroku. Wykonanie następuje w kilku momentach:
przy odświeżeniu zapytania (ręcznym lub automatycznym),
przy wyświetlaniu podglądu danych po dodaniu/zmianie kroku,
przy ładowaniu wyników do arkusza lub modelu danych.
To dlatego czasem długi „młyn” pojawia się dopiero przy konkretnym kroku, choć kilka wcześniejszych kroków wydaje się równie ciężkich. Kiedy w podglądzie klikniesz dany krok, Power Query musi wykonać całe zapytanie od początku aż do tego kroku. Jeśli w międzyczasie folding przestał działać, a do tego doszło rozwinięcie kolumny zagnieżdżonej lub join z bardzo dużą tabelą, to właśnie na tym etapie pojawi się zauważalne spowolnienie.
Dlaczego pojedynczy niewinny krok potrafi „zabić” cały proces
Wyobraź sobie, że:
łączysz się z bazą SQL z kilkoma milionami wierszy,
na początku filtrujesz po dacie i kilku kolumnach, dzięki czemu folding działa, a do klienta trafia tylko 5% danych,
w którymś momencie dodajesz niestandardową kolumnę z logicznym warunkiem napisaną „z głowy” w M,
od tego kroku folding przestaje działać, więc wszystkie kolejne kroki wykonują się po stronie Excela.
Skutek jest taki, że serwer już nie filtruje wszystkiego „u siebie”, tylko wysyła do ciebie znacznie większy zestaw danych, a komputer użytkownika musi wykonać pracę, z którą lepiej poradziłby sobie serwer. Ten jeden krok jest więc nie tylko wolny sam w sobie, ale także blokuje optymalizacje dla wszystkich następnych. Dokładnie dlatego diagnoza musi koncentrować się nie na tym, który krok wydaje się ciężki, ale na tym, po którym zapytanie przestaje się składać lub nagle rośnie liczba przetwarzanych wierszy.
Szybka diagnoza: które zapytania i kroki są winne
Najprostsze wskaźniki: czas odświeżania i obciążenie komputera
Pierwszy krok diagnostyki to zwykła obserwacja. Uruchom „Odśwież wszystko” i patrz:
które zapytanie najdłużej widnieje jako „Trwa odświeżanie”,
czy CPU jest stale na wysokim poziomie (100% na jedno lub kilka rdzeni),
czy zużycie RAM rośnie skokowo przy konkretnym etapie.
W Excelu można włączyć okno „Stan odświeżania” i zobaczyć, ile czasu potrzebował każdy z procesów. W Power BI Desktop logi odświeżania dają podobną informację. To pozwala szybko odsiać zapytania lekkie od tych, które spowalniają całość. W pierwszej kolejności zajmij się właśnie tymi najcięższymi – efekt będzie największy względem wysiłku.
Pomiar czasu ładowania pojedynczych zapytań
Gdy już wiesz, które zapytanie jest głównym winowajcą, warto zmierzyć, ile zajmuje jego samodzielne odświeżenie. Wyłącz automatyczne odświeżanie zależnych zapytań, a następnie:
kliknij prawym na wybrane zapytanie i wybierz „Odśwież”,
zmierz czas (zwykły stoper w telefonie jest wystarczający),
sprawdź, czy przy kolejnych odświeżeniach czas jest podobny (aby wykluczyć chwilowe spowolnienie sieci lub serwera).
Odcinanie zależnych zapytań i testowanie w izolacji
Diagnozowanie wolnego kroku jest dużo łatwiejsze, gdy zapytanie działa w oderwaniu od reszty. W rozbudowanych modelach często jedno zapytanie zasila kilka innych, które dorzucają kolejne kroki i złączenia. Jeśli każde odświeżenie ciągnie za sobą łańcuch kilkunastu tabel, trudno zrozumieć, gdzie naprawdę ginie czas.
Praktyczny schemat pracy:
na czas diagnozy wyłącz ładowanie wyników zapytań, które nie są aktualnie analizowane,
skopiuj problematyczne zapytanie (prawy przycisk → „Duplikuj”) i pracuj na kopii,
w kopii usuń kroki związane z eksportem (np. zmiany typów, zaokrąglenia, sortowanie „pod raport”), aby testować tylko „rdzeń” pobierania i łączenia danych.
Taka izolacja często pokazuje, że źródłem problemu nie jest sama baza czy plik źródłowy, ale kilka ostatnich kroków służących wyłącznie do „upiększenia” raportu. Wtedy możesz zdecydować, czy naprawdę potrzebne jest sortowanie po trzech kolumnach i rozbudowane formatowanie danych, czy da się to przenieść do Excela lub DAX, gdzie koszt bywa mniejszy.
Stopniowe komentowanie kroków w kodzie M
Gdy zwykła obserwacja nie wystarcza, najprościej „wysadzić w powietrze” część zapytania i sprawdzić, co się zmienia w czasie odświeżania. W praktyce oznacza to tymczasowe komentowanie fragmentów kodu M.
Szybki sposób:
Otwórz zaawansowany edytor.
Zlokalizuj końcową linię: in NazwaKroku.
Zmień ją na wcześniejszy krok, np. in #"Połączone tabele".
Jeśli po takiej zmianie odświeżenie przyspiesza z kilku minut do kilkunastu sekund, masz jasny sygnał, że problem leży w krokach pośrednich – tych „odciętych” od wyniku. Zrób to kilka razy, przesuwając granicę coraz bliżej końca. Ten prosty „binary search” pozwala w kilka minut zawęzić źródło problemu do 1–2 kroków zamiast szukać po omacku.
Wizualne namierzanie złączek, które dławią odświeżanie
Złączenia (merge) to klasyczny punkt zapalny. Dwa podobne zapytania mogą różnić się tylko typem joinu albo kluczem, a czasy odświeżania będą drastycznie inne. Zanim zaczniesz przebudowywać całą logikę, sprawdź kilka prostych rzeczy:
czy złączenie odbywa się po kolumnach o tym samym typie (tekst z tekstem, liczba z liczbą),
czy obie tabele są wcześniej odchudzone do niezbędnych kolumn,
czy kolejność kroków nie wymusza lokalnego joinu na wielkiej, nieprzefiltrowanej tabeli.
Dobry test „za grosze” czasowe: tymczasowo ogranicz obie tabele do np. 1000 wierszy (filtr na górze kroków), odpal odświeżenie i zobacz, czy różnica w czasie nadal jest ogromna. Jeśli przy małej próbce zapytanie działa błyskawicznie, problemem jest głównie ilość danych, a nie skomplikowanie logiki – wtedy największy zysk przyniesie wcześniejsze filtrowanie i usuwanie kolumn, nie pisanie wszystkiego od nowa.
Podgląd vs pełny odczyt – dwa różne światy
Podgląd danych w Edytorze Power Query często działa szybciej niż pełne odświeżenie. Dzieje się tak, bo Power Query pobiera tylko fragment tabeli (domyślnie kilka pierwszych tysięcy wierszy). Jeśli podczas zwykłego klikania po krokach wszystko wygląda „znośnie”, a pełne odświeżenie trwa wieczność, trzeba sprawdzić zachowanie na całym zbiorze.
Przy diagnozie używaj dwóch trybów:
Podgląd – do szybkiego orientowania się, czy krok działa poprawnie logicznie.
Pełne odświeżenie – do faktycznego pomiaru czasu po każdej większej zmianie w konstrukcji zapytania.
Nie opieraj decyzji optymalizacyjnych wyłącznie na tym, co widać w podglądzie. Krok, który na 1000 wierszy działa w sekundę, na 10 mln może zająć pół godziny, szczególnie jeśli wymusza przetwarzanie lokalne.
Źródło: Pexels | Autor: Firmbee.com
Najczęstsze typy kroków, które spowalniają Power Query
Sortowania na dużych tabelach w „złym miejscu”
Sortowanie samo w sobie nie jest złe, ale bardzo często ląduje w najmniej korzystnym momencie: na pełnej, już połączonej tabeli. Jeśli tabela ma kilka milionów wierszy, a do raportu w Excelu używasz ostatecznie tylko fragmentu, takie sortowanie nie ma żadnego sensu ekonomicznego.
Bezpieczniejsza strategia:
sortuj jak najbliżej źródła, jeśli folding pozwala (np. w SQL ORDER BY z limitem lub w widoku),
jeśli sortowanie jest potrzebne tylko do „ładnego” wyświetlenia raportu, zrób je już w Excelu lub w Power BI na wizualizacji,
unikaj sortowania kombinacji kilku kolumn, gdy wystarczy jedna – każda dodatkowa kolumna zwiększa koszt operacji.
Prosty test: wyłącz krok sortowania (krzyżyk przy kroku, ewentualnie cofnij w edytorze M) i odpal odświeżenie. Jeśli czas spada radykalnie, lepiej przenieść sortowanie do warstwy prezentacji niż płacić minutami przy każdym refreshu.
Grupowanie (Group By) na surowych, niefiltrowanych danych
Kolejny klasyk to grupowanie na „wszystkim, jak leci”. Tabela z kilku systemów zostaje zjoinowana, rozwinięta, a dopiero potem ktoś dodaje krok Group By, licząc sumy czy średnie. To prosta droga do sytuacji, w której Power Query pracuje na wielokrotnie powiększonym zestawie danych, mimo że na końcu i tak potrzebujesz jednego wiersza na klienta lub dzień.
Grupowanie działa o wiele lżej, gdy:
przed nim wykonasz filtrację po datach, statusach, oddziałach,
usuniesz zbędne kolumny, których i tak nie użyjesz w agregacjach ani jako kluczy grupowania,
wykonasz kilka mniejszych grupowań zamiast jednego ogromnego (np. osobno po kliencie i po produkcie, jeśli to możliwe).
Jeśli źródłem jest baza SQL lub inny „inteligentny” serwer, spróbuj przenieść grupowanie do widoku lub zapytania SQL. Nawet proste SELECT Klient, SUM(Wartosc) FROM ... GROUP BY Klient przed podaniem danych do Power Query potrafi skrócić odświeżanie z kilku minut do kilku sekund.
Rozwijanie kolumn zagnieżdżonych przy zbyt dużej liczbie kolumn
Rozwijanie kolumn zawierających tabele lub rekordy (np. po merge) często dramatycznie zwiększa liczbę kolumn i objętość danych. Gdy „rozwiniesz wszystko”, nagle zamiast 20 kolumn masz 150, z czego większości nie użyjesz nigdy.
Takie rozwinięcie kosztuje nie tylko czas, ale także pamięć RAM – Power Query musi przetworzyć i zapamiętać każdy dodatkowy bajt, nawet jeśli później większość kolumn usuniesz. Rozsądniejszy schemat to:
zaraz po merge rozwiń tylko te kolumny, których faktycznie potrzebujesz,
jeśli nie jesteś pewien, zrób dwa zapytania: jedno „bogate” do analizy jednorazowej, drugie odchudzone do produkcyjnego odświeżania,
nie używaj opcji „Zaznacz wszystko”, tylko świadomie wybieraj pola.
Przy dużych źródłach każdy niepotrzebny bajt w kolumnach to dodatkowe milisekundy przy każdym odświeżeniu. Po roku takich „oszczędności” różnica w czasie pracy jest bardzo odczuwalna.
Kolumny niestandardowe z rozbudowaną logiką M
Kolumna niestandardowa, która w jednym wierszu wywołuje kilka funkcji, zapętla się po listach i jeszcze odpytuje inną tabelę, może brzmieć jak genialne, „sprytne” rozwiązanie. W praktyce staje się najdroższym krokiem całego procesu.
Zanim napiszesz wszystko w jednym potężnym wyrażeniu:
rozbij logikę na kilka prostszych kolumn, które da się łatwiej zoptymalizować i przetestować,
zobacz, czy część z tych obliczeń nie nadaje się lepiej do DAX lub do formuł w Excelu,
unikaj wywoływania dodatkowych funkcji M dla każdego wiersza, jeśli da się zrobić przeliczenie na całej tabeli (np. join + prosta kolumna zamiast funkcji wyszukującej).
Jeśli źródłem jest baza danych, część warunków logicznych uwagę lepiej przenieść do widoku SQL (CASE WHEN, prosty mapping kodów). Serwer zrobi to raz, a Power Query dostanie już gotową, przeliczoną kolumnę. Czas odświeżenia często spada wtedy „za darmo”, bez mozolnej optymalizacji samego M.
Złączenia po kolumnach tekstowych i nieindeksowanych
Łączenie po kolumnie tekstowej (np. nazwie klienta) jest wygodne, ale kosztowne. Tekst trudniej zoptymalizować niż liczby, a każdy drobny błąd w nazwie powoduje, że join nie zadziała, więc i tak trzeba robić dodatkowe czyszczenie danych.
Jeśli to możliwe:
łącz po kluczach liczbowych, a nazwy trzymaj tylko jako kolumny „opisowe”,
upewnij się, że po stronie bazy kolumna klucza jest zindeksowana (to nie wymaga zazwyczaj wielkich inwestycji, a daje ogromny zysk w czasie joinów),
przed joinem znormalizuj teksty (obcięcie spacji, jednolita wielkość liter) raz, a nie za każdym razem w kolumnie niestandardowej.
Jeżeli nie masz wpływu na strukturę bazy, drobną, ale skuteczną sztuczką jest dodanie pomocniczej kolumny liczb całkowitych (np. hash lub prosty identyfikator) w Power Query i operowanie dalej na niej przy połączeniach między zapytaniami lokalnymi. Koszt wygenerowania takiej kolumny płacisz raz, a późniejsze złączenia są już mniej obciążające.
Duplikowanie logiki w kilku podobnych zapytaniach
Część spowolnień nie wynika z jednego „ciężkiego” kroku, tylko z powielania tych samych czasochłonnych operacji. Klasyczny scenariusz: trzy podraporty (sprzedaż, koszty, marża) mają osobne zapytania, które każde z osobna importuje te same surowe dane, filtruje je i łączy z tymi samymi słownikami.
Lepszy układ, wygodny także przy późniejszej diagnostyce:
zbuduj jedno zapytanie bazowe, które pobiera i wstępnie czyści dane (filtrowanie po dacie, wybór kolumn, podstawowe joiny),
kolejne zapytania (raportowe) opieraj na referencji do tej bazy, a nie na ponownym imporcie,
ciężkie kroki trzymaj jak najwyżej, w zapytaniu bazowym, aby były wykonane tylko raz przy odświeżaniu.
Taki podział często zmniejsza całkowity czas odświeżania bez ingerencji w pojedyncze kroki. W dodatku każdy kolejny raport „doklejasz” tanio – korzysta z tej samej, zoptymalizowanej bazy.
Źródło: Pexels | Autor: Lukas Blazek
Narzędzia pod ręką: jak technicznie namierzyć wolny krok
W nowszych wersjach Power Query pojawił się wskaźnik składania zapytań. Przy nazwie kroku w okienku „Ustawienia kroków” zobaczysz ikonę (np. rozwijaną strzałkę), która informuje, czy dany krok nadal się składa do źródła, częściowo się składa, czy folding jest już całkowicie wyłączony.
Szybkie zastosowanie:
przejdź po kolei po krokach i sprawdź, przy którym z nich wskaźnik zmienia się z „składane” na „nieskładane”,
zapamiętaj ten krok jako potencjalny „spust”, który przenosi obliczenia na stronę klienta,
przesuwaj ten krok w dół lub upraszczaj jego logikę, obserwując, czy folding wraca.
Każde „odzyskanie” składania na wcześniejszych etapach oznacza mniej danych przesłanych do komputera i mniej pracy wykonywanej w pamięci lokalnej. Z perspektywy czasu i kosztu jest to zwykle najbardziej opłacalna modyfikacja.
Query Diagnostics i logi odświeżania
W Power BI Desktop i nowszych dodatkach do Excela dostępne są narzędzia diagnostyczne, które pozwalają zobaczyć, jak długo trwają poszczególne etapy zapytania. Funkcje typu „Start Diagnostics” / „Stop Diagnostics” generują raport, w którym widać:
czas pobierania danych ze źródła,
czas transformacji lokalnych,
ewentualne błędy i ponowne próby odczytu.
Takie logi są trochę „ciężkie” w lekturze, ale przy większych modelach opłaca się poświęcić godzinę, aby wychwycić kroki z najdłuższym czasem. Daje to twarde dane zamiast zgadywania, a zmiany można później mierzyć, powtarzając diagnostykę.
Monitor zasobów systemowych jako prosty „radar”
Nie zawsze trzeba sięgać po zaawansowane raporty. Zwykły Menedżer zadań (Windows) albo inny monitor zasobów pokazuje, czy w czasie odświeżania:
Obserwacja CPU, RAM i dysku podczas odświeżania
Kilka minut patrzenia na Menedżera zadań podczas odświeżania często mówi więcej niż długi raport diagnostyczny. Chodzi o prostą obserwację, co jest głównym wąskim gardłem:
CPU 100% przez większość czasu – kosztowne transformacje po stronie klienta, złożone kolumny niestandardowe, sortowania i grupowania na dużych tabelach;
pamięć RAM rośnie i dochodzi do granic – zbyt wiele kolumn/wierszy w jednym zapytaniu, rozwinięte „na bogato” merge, wiele zapytań odświeżanych równolegle;
dysk (szczególnie HDD) stale na wysokim poziomie – intensywne użycie pliku wymiany, kompresja/dekompresja, brak miejsca na dysku systemowym;
sieć aktywna przez długi czas przy niskim CPU – wąskie gardło to źródło danych (wolna baza, VPN, chmura).
Szybkie wnioski z takiego „radaru”:
przy zapchanym CPU i RAM większy sens ma uproszczenie logiki M niż wymiana całego sprzętu,
przy przeciążonym dysku często wystarczy wyłączyć równoległe odświeżanie wielu raportów albo ograniczyć liczbę zapytań tła,
przy wolnej sieci bardziej opłaca się ograniczyć zakres dat czy liczbę kolumn przed pobraniem niż dokładać kolejne rdzenie procesora.
Przy jednym z projektów raport odświeżał się ponad 25 minut. Menedżer zadań pokazał niski CPU, ale sieć „przyklejona” do stałego transferu. Okazało się, że Power Query pobiera komplet danych z pięciu lat, a użytkownicy i tak raportowali jedynie bieżący i poprzedni rok. Dodanie prostego filtru po dacie już w widoku SQL skróciło odświeżanie do kilku minut – bez kupowania mocniejszej maszyny.
Porównywanie czasu poszczególnych kroków „na oko”
Bez formalnej diagnostyki można wykonać tani, ale skuteczny test czasu kroków:
Skopiuj zapytanie (duplikat lub referencja – referencja jest lżejsza).
W jednym z nich usuń połowę kroków od dołu i odśwież podgląd.
Zmierz orientacyjnie czas (nie sekundnikiem laboratoryjnym, wystarczy stoper w telefonie).
W drugim usuń odwrotną połowę kroków i też zmierz.
Jeżeli:
wersja „krótsza” działa błyskawicznie, a oryginał mieli się długie minuty, to „ciężar” siedzi w dolnej części kroków,
obie wersje są wolne – problemem będzie już samo pobranie i pierwsze transformacje.
Potem można jeszcze ciaśniej zawężać poszukiwania: odcinać mniejsze kawałki kroków i patrzeć, w którym zestawie czas skacze. To metoda mało elegancka, ale przy niewielkim nakładzie pracy szybko ujawnia kilka najbardziej kosztownych miejsc.
Testowanie alternatywnych wersji kroku obok siebie
Gdy wiadomo już, który krok boli, opłaca się zrobić mini-laboratorium:
stwórz zapytanie referencyjne oparte na tym samym etapie wejściowym (np. przed ciężkim Group By),
w nowym zapytaniu zbuduj alternatywną logikę – inne sortowanie, inny sposób joinu, mniej kolumn,
porównaj czas podglądu/odświeżenia między wersją oryginalną a alternatywną.
Zyskujesz prostą odpowiedź, czy zmiana podejścia faktycznie przynosi korzyść, zanim przebudujesz całą strukturę. To znacznie tańsze niż ślepe przerabianie całego modelu na coś „teoretycznie lepszego”.
Kiedy query folding przestaje działać i jak to naprawić
Typowe operacje, które zrywają składanie zapytań
Składanie zapytań (query folding) polega na tym, że Power Query przekłada twoje kroki na zapytanie wykonywane przez źródło (np. SQL Server). Gdy folding się urywa, cała logika od danego kroku w dół wykonuje się lokalnie. Kilka typów operacji bardzo często „odcina” ten mechanizm:
niestandardowe kolumny z funkcjami M działającymi wiersz po wierszu, których nie da się zamapować na SQL (np. operacje na listach, skomplikowany tekst, własne funkcje),
konwersje typu w nietypowe formaty (np. tekst na datę z egzotycznymi formatami),
operacje na listach i rekordach wyciągane z tabeli (Table.ToList, List.Generate, zagnieżdżone struktury),
połączenia z innymi źródłami (np. merge między bazą SQL a plikiem Excela),
niektóre funkcje tekstowe oraz operacje na czasach/strefach, których nie obsługuje konkretny konektor.
Odczyt folding indicatora przy każdym kroku szybko pokaże, który z nich był „ostatnim składanym”. Celem nie zawsze jest przywrócenie składania dla wszystkiego – często wystarczy zadbać o to, aby najcięższe kroki (filtry, joiny, agregacje) pozostały po stronie źródła.
Przenoszenie ciężkich kroków nad „łamiące” folding
Najtańszy sposób ratowania składania to przesunięcie kolejności kroków. Jeśli masz w łańcuchu:
Source (tabela z bazy),
Filtered Rows (po dacie),
Removed Columns (kilka zbędnych kolumn),
Added Custom (złożona logika M – tu folding się urywa),
Grouped Rows,
Sorted Rows,
rozwiązanie bywa banalne:
przesuń Group By i Sortprzed złożoną kolumnę niestandardową,
upewnij się, że filtry po dacie i podstawowe joiny też są możliwie wysoko.
W praktyce oznacza to, że duży wolumen danych zostanie zredukowany jeszcze zanim wejdziesz w strefę braku folding. Lokalne przeliczenia będą liczone na mniejszej tabeli, co robi dużą różnicę, zwłaszcza przy słabszych komputerach.
Zastępowanie złożonych funkcji M prostszymi odpowiednikami
Część logiki powinna zostać w M (np. specyficzne przekształcenia tekstu), ale wiele „sprytnych” rozwiązań da się zastąpić prostszą operacją, którą źródło rozumie lepiej. Kilka typowych zamian:
zamiast zagnieżdżonej funkcji wyszukującej (np. własna implementacja VLOOKUP w M) – Merge Queries po kluczu i prosta kolumna warunkowa,
zamiast kilku etapów przemapowania kodów w M – tabela słownikowa w bazie i join po kodzie,
zamiast niestandardowego parsowania dat po stronie Power Query – konwersja typów już w SQL, często jednym CAST/CONVERT.
Te zmiany kosztują zwykle trochę rozmów z administratorem bazy lub prostą korektę w widoku SQL, ale w zamian odciążają Power Query z pracy, której i tak nie wykona tak szybko jak serwer.
Parametry i filtry wykorzystywane w folding
Dużo wydajności leży w umiejętnym wykorzystaniu parametrów. Zamiast pobierać całą historię i filtrować ją lokalnie:
utwórz parametr zakresu dat (np. „DataOd”, „DataDo”),
użyj go w kroku filtrowania tuż nad źródłem,
upewnij się, że folding indicator pokazuje, iż filtr się składa (najczęściej zielona ikona).
Efekt: baza zwraca tylko interesujący wycinek, a nie pełną historię. Przy źródłach z chmury lub przez VPN oszczędność na transferze i czasie bywa ogromna. Jeżeli nie można użyć parametrów dynamicznych, nawet stały filtr typu „ostatnie 2 lata” zastosowany w widoku SQL jest tańszy niż przerzucanie wszystkiego do Power Query.
merge tych dwóch dopiero na końcu, w mniejszej skali – najlepiej po wcześniejszym ograniczeniu danych z bazy.
W efekcie to, co serwer umie zrobić szybko, robi u siebie. Power Query zostaje głównie z łączeniem wyników i drobnymi transformacjami, a nie z pełnym odtwarzaniem logiki bazy w pamięci lokalnej.
Wykorzystanie widoków i procedur po stronie bazy
Jeżeli masz choć minimalny wpływ na bazę, jednym z najefektywniejszych ruchów jest dodanie widoku pod Power Query:
do widoku wrzucasz filtry po dacie, wstępne joiny, podstawowe agregacje,
w Power Query podłączasz się tylko do tego widoku i robisz lżejszą obróbkę „na deser”,
folding jest prostszy, bo Power Query i tak widzi już częściowo przetworzone dane.
Widoki są tańsze w utrzymaniu niż złożone procedury składowane, a i tak zdejmują z Power Query 80% roboty. Dla analityka to często jedno spotkanie z działem IT i kilka linijek SQL, które potem procentują latami przy każdym odświeżeniu.
Świadome rezygnowanie z folding tam, gdzie to się opłaca
Nie każdą operację trzeba za wszelką cenę „składać” do źródła. Czasem taniej jest:
pobrać niewielki, już mocno przefiltrowany wycinek danych z bazy,
wyłączyć folding jednym nietypowym, ale bardzo użytecznym krokiem (np. customowa logika w M),
pogodzić się z tym, że ostatnie 5% transformacji dzieje się lokalnie, bo tak jest po prostu szybciej w implementacji i wystarczająco szybko w działaniu.
Klucz to zachować folding tam, gdzie potrafi on drastycznie zmniejszyć rozmiar danych – przy filtrach i agregacjach na dużych tabelach. Reszta to bilans zysków i kosztów: jeśli zyskujesz godzinę pracy developera dzięki prostszemu M, a tracisz 10 sekund na odświeżeniu, bilans najczęściej jest na plus.
Najczęściej zadawane pytania (FAQ)
Dlaczego Power Query nagle działa dużo wolniej niż kiedyś?
Najczęściej przyczyną jest wzrost danych i kilka nowych kroków, które zaczęły być wykonywane na znacznie większej liczbie wierszy. Raport, który początkowo działał na kilku plikach i tysiącach rekordów, po roku może obrabiać dziesiątki plików i setki tysięcy rekordów przy niezmienionej logice.
Często dokładany jest nowy merge, dodatkowe kolumny obliczeniowe, sortowanie lub grupowanie – i nagle to, co wcześniej trwało sekundy, zaczyna liczyć się w minutach. Technicznie zwykle winny jest jeden konkretny krok lub mała gałąź zapytań, a nie „Power Query jako takie”.
Jak rozpoznać, który krok spowalnia Power Query?
Najprostsza metoda to ręczne przechodzenie po krokach w Edytorze zapytań i obserwowanie, na którym momencie podgląd „mieli” najdłużej lub pojawia się komunikat „Oczekiwanie na zapytanie…”. Jeśli pojedynczy krok liczy się w minutach, to dobry kandydat do analizy.
Drugi sygnał to pełne odświeżenie raportu: jeśli całość zatrzymuje się na jednym zapytaniu, spróbuj wyłączyć jego ładowanie (Disable load) i sprawdzić, czy reszta przyspiesza. Wtedy zawężasz obszar poszukiwań do kroków wewnątrz tego konkretnego zapytania.
Kiedy w ogóle opłaca się optymalizować zapytania Power Query?
Optymalizacja ma sens przede wszystkim w procesach cyklicznych: dziennych, tygodniowych, odpalanych po kilka razy dziennie. Jeśli raport odświeża się trzy razy dziennie po kilkanaście minut, to bardzo szybko robi się z tego wiele godzin czekania miesięcznie, często droższych niż kilka godzin pracy nad usprawnieniem modelu.
Jednorazowe lub rzadkie procesy (np. roczna migracja danych) można zwykle zaakceptować, nawet jeśli trwają godzinę, byle nie blokowały całej pracy. Tu „budżetowo” bardziej opłaca się puścić je w tle lub na innym komputerze, niż inwestować dużo czasu w dopieszczanie wydajności.
Jak policzyć, czy wolne zapytanie naprawdę generuje koszt?
Najprostszy rachunek to pomnożyć czas jednego odświeżenia przez liczbę odświeżeń dziennie i dni roboczych w miesiącu. Jeśli wychodzi z tego kilka–kilkanaście godzin czekania miesięcznie, masz twardy argument, że warto poświęcić parę godzin na optymalizację.
Dobrym podejściem jest też porównanie: ile czasu zajmie przebudowa zapytania vs ile czasu miesięcznie „oddaje” szybszy raport. Jeśli dwie godziny dłubania skracają odświeżanie z 12 do 5 minut przy kilku odświeżeniach dziennie, to zwrot z inwestycji pojawia się praktycznie od razu.
Jakie objawy wskazują, że zapytanie jest nienaturalnie wolne?
Alarmujące są sytuacje, gdy:
odświeżanie trwa po kilkanaście minut przy relatywnie małej liczbie wierszy (np. dziesiątki tysięcy z prostego Excela),
czas odświeżania rośnie szybciej niż liczba danych – np. danych przybyło o 30%, a czas wzrósł 4-krotnie,
pojawiają się błędy pamięci lub inne programy zaczynają się zawieszać przy każdym odświeżeniu,
komputer podczas odświeżania staje się praktycznie bezużyteczny na dłużej.
Jeśli widzisz któryś z tych symptomów, zamiast kupować mocniejszy sprzęt, lepiej najpierw namierzyć i przeprojektować kilka najcięższych kroków w Power Query.
Co mogę zrobić, żeby przyspieszyć Power Query bez całkowitej przebudowy raportu?
Na start warto skupić się na tanich wprowadzeniach zmian: przesuń filtrowanie jak najbliżej źródła (np. ogranicz zakres dat już w zapytaniu do bazy), usuń zbędne sortowania na pełnych tabelach i uprość kolumny obliczeniowe, które liczą się na milionach wierszy. Czasem samo usunięcie nieużywanego merge’a daje zauważalny skok wydajności.
Dobrym nawykiem jest też podział złożonego zapytania na kilka prostszych oraz ponowne wykorzystanie przefiltrowanych, mniejszych tabel zamiast wielokrotnego liczenia wszystkiego „od zera”. To stosunkowo małe zmiany, a potrafią mocno skrócić czas odświeżania bez przepisywania całego modelu.
Czym jest query folding i jak wpływa na szybkość Power Query?
Query folding to sytuacja, w której Power Query „tłumaczy” Twoje kroki (filtry, grupowania, joiny) na język źródła danych, np. SQL, i zleca ich wykonanie po stronie serwera. Dzięki temu do Excela czy Power BI trafia już przefiltrowany, odchudzony zestaw danych, a ciężkie operacje wykonuje silnik bazy, który jest do tego optymalizowany.
Gdy jakiś „egzotyczny” krok przerwie query folding, kolejne transformacje są wykonywane lokalnie, w pamięci Twojego komputera. Przy małych tabelach zwykle tego nie widać, ale przy milionach wierszy może to kompletnie zatkać odświeżanie. Dlatego opłaca się układać kroki tak, by jak najwięcej z nich dało się „złożyć” i zepchnąć do źródła danych.
Kluczowe Wnioski
Najczęściej spowalnia nie „cały Power Query”, tylko jeden konkretny krok lub gałąź zapytań, która nagle zaczyna działać na dużo większym wolumenie danych niż na początku.
Nie każdy długi czas odświeżania wymaga akcji – jednorazowe lub rzadkie procesy można „przemielić” w tle, a optymalizację zostawić dla cyklicznych raportów, które regularnie blokują ludziom pracę.
Decyzję o optymalizacji najlepiej oprzeć na prostym rachunku: czas jednego odświeżenia × liczba odświeżeń dziennie × liczba dni w miesiącu pokazuje realny koszt czekania w godzinach pracy.
Sygnał, że zapytanie wymaga przeprojektowania, pojawia się wtedy, gdy czas odświeżania rośnie nieliniowo względem przyrostu danych, raport często dobija pamięć RAM albo na długie minuty „zamraża” komputer.
Zamiast przepisywać całość, zwykle wystarczy przeorganizować kilka kroków: przenieść filtrowanie jak najbliżej źródła, ograniczyć sortowania i ciężkie operacje na pełnych tabelach, usunąć zbędne transformacje.
Raporty, które na początku działają „w locie”, z czasem duszą się na rosnącej bazie (np. miesięczna sprzedaż z dodatkowymi obliczeniami i złączeniami po tekście), więc trzeba je projektować z myślą o skali, a nie tylko o dzisiejszym wolumenie.
Największy zwrot z inwestycji daje skupienie się najpierw na raportach odświeżanych często i przez wiele osób – skrócenie czasu z kilkunastu do kilku minut miesięcznie oddaje całe roboczogodziny bez kupowania nowego sprzętu.
