Power Query bardzo wolno się odświeża - od czego zacząć szukanie przyczyny

Power Query w Excelu, Power BI i Fabric

Power Query bardzo wolno się odświeża - od czego zacząć szukanie przyczyny

W skrócie

Wolne odświeżanie w Power Query może mieć kilka przyczyn naraz, dlatego najlepiej sprawdzać je w stałej kolejności: najpierw pomiar, potem rzeczy, które najczęściej zabierają czas. Pokazujemy taką listę kontrolną opartą na dobrych praktykach z kursu i dokumentacji Microsoftu, z poleceniami, które klikasz w Excelu i Power BI Desktop.

Jak to wygląda w praktyce

Typowe sygnały wyglądają tak:

• Odśwież wszystko w Excelu trwa kilka minut, choć wynik ma kilkadziesiąt wierszy,
• odświeżanie wydłuża się z każdym miesiącem danych dopisywanych do folderu albo bazy,
• edytor długo liczy podgląd po każdym kroku,
• odświeżanie kończy się błędem Expression.Error z komunikatem „Za mało pamięci, nie można kontynuować obliczania.” (ang. „Evaluation ran out of memory and can't continue.”).

Sam czas nie mówi, gdzie leży problem. To samo opóźnienie może wynikać z pobierania milionów wierszy z bazy, z wielokrotnego czytania tych samych plików albo z jednego kroku liczonego osobno dla każdego wiersza.

Dlaczego tak się dzieje

Czas odświeżania składa się z pobrania danych ze źródła, przekształceń wykonywanych przez silnik Power Query i załadowania wyniku. Składanie zapytań (ang. query folding), czyli zamiana kroków na jedno zapytanie wykonywane przez bazę, ogranicza dwa pierwsze składniki. W przykładzie z dokumentacji Microsoftu trzy warianty zapytania dającego ten sam wynik odświeżały się 361 sekund bez składania, 184 przy częściowym i 31 przy pełnym.

Pozostałe przyczyny są powtarzalne. Kilka zapytań odwołujących się do tego samego, ciężkiego źródła może je wczytywać osobno. Zapytania pośrednie ładowane do arkusza albo modelu dokładają pracy przy każdym odświeżeniu. Funkcja wywołana poleceniem Wywołaj funkcję niestandardową działa osobno dla każdego wiersza, a jeśli w środku ma Web.Contents, każdy wiersz może oznaczać osobne zapytanie do serwera. Łącznik folderu SharePoint pokazuje pliki z całego wskazanego folderu razem z podfolderami.

Błąd braku pamięci Microsoft wiąże z operacjami pamięciożernymi na dużych tabelach: sortowaniem, scalaniem, grupowaniem i usuwaniem duplikatów. W 32-bitowym Excelu silnik ma do dyspozycji około 1 GB.

Jak to rozwiązać krok po kroku

1. Zmierz, zanim cokolwiek zmienisz. W Excelu odśwież zapytania pojedynczo poleceniem Odśwież w menu zapytania w okienku Zapytania i połączenia i zanotuj czas każdego. W Power BI Desktop na karcie Narzędzia użyj Diagnozuj krok albo Rozpocznij diagnostykę i Zatrzymaj diagnostykę. Zapytanie Detailed pokaże czas każdego zdarzenia.
2. Filtry i usuwanie kolumn na początek. Odfiltruj zbędne okresy i usuń niepotrzebne kolumny zaraz po kroku źródła. Przy bazach te kroki się złożą, przy plikach przynajmniej zmniejszą ilość danych dla kolejnych kroków.
3. Sprawdź składanie przy bazach. Kliknij prawym przyciskiem ostatni krok i sprawdź, czy Wyświetl zapytanie natywne jest aktywne. Kroki przerywające składanie, takie jak indeks czy scalenie z plikiem, przenieś za filtry i grupowanie.
4. Jedno źródło, jeden odczyt. Gdy kilka zapytań korzysta z tego samego ciężkiego źródła, zbuduj jedno zapytanie bazowe i twórz z niego kolejne poleceniem Odwołanie zamiast kopiowania kroków. W Dataflow Gen2 w Fabric pomaga tu przemieszczanie (ang. staging).
5. Nie ładuj zapytań pośrednich. W Excelu ustaw je jako Utwórz tylko połączenie, w Power BI odznacz Włącz ładowanie. Do arkusza i modelu trafiają tylko tabele, z których ktoś korzysta.
6. Zastąp funkcje wołane dla wierszy jednym pobraniem. Zamiast pytać API o kurs waluty w każdym wierszu, pobierz raz całą tabelę kursów, na przykład tabelę A z API NBP pokazaną w lekcji o źródłach danych, i dołącz kurs poleceniem Scal po kodzie waluty.
7. Pliki z SharePointa filtruj od razu. Zaraz po kroku źródła dodaj filtr po kolumnie Folder Path, tak jak w lekcji o Dataflow Gen2, żeby dalsze kroki pracowały tylko na plikach z właściwego folderu.
8. Przy błędzie pamięci odchudź operacje ciężkie. Sortowanie, scalanie, grupowanie i usuwanie duplikatów przenieś do źródła albo usuń, jeśli nie są potrzebne. Microsoft zaznacza, że sortowanie często jest zbędne. W 32-bitowym Excelu rozważ wersję 64-bitową.

Jak sprawdzić, że zadziałało

Po każdej zmianie powtórz pomiar tą samą metodą i na tych samych danych: czas odświeżenia pojedynczego zapytania w Excelu albo wynik Diagnozuj krok w Power BI Desktop. Zmieniaj jedną rzecz naraz, wtedy wiesz, co dało efekt. Przy zapytaniach do baz sprawdź w oknie Zapytanie natywne, czy filtr i grupowanie trafiły do SQL.

Żeby problem nie wracał, buduj każde nowe zapytanie w tej samej kolejności: źródło, filtry i wybór kolumn, operacje składane do bazy, a na końcu kroki liczone lokalnie. W polu Opis we właściwościach zapytania zapisz, skąd bierze dane, a raz na jakiś czas przejrzyj okienko Zapytania i połączenia pod kątem zbędnych tabel w arkuszu.

Wróć do listy: 88 najczęstszych pytań i problemów związanych z Power Query

Szkolenie Microsoft Excel - Power Query --> Dwa dni warsztatów z Excela: pobieranie danych z plików, folderów, SharePointa i baz SQL, ich czyszczenie i łączenie w Power Query, praca w języku M, a na koniec model danych i oparta na nim tabela przestawna. Prowadzi Sebastian Stasiak.

To szkolenie może być dofinansowane dla Ciebie z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5