Edytor Power Query zacina się przy każdym kliknięciu - podgląd i pamięć podręczna

Power Query w Excelu, Power BI i Fabric

Edytor Power Query zacina się przy każdym kliknięciu - podgląd i pamięć podręczna

W skrócie

Edytor Power Query wolno działa zwłaszcza wtedy, gdy zapytanie czyta duże pliki, folder z wieloma plikami albo API. Każde kliknięcie kroku na liście Zastosowane kroki oznacza wtedy nowe liczenie podglądu. Pokazujemy, skąd bierze się to czekanie, które ustawienia edytora na nie wpływają i jak budować zapytanie na mniejszej próbce danych, nie zmieniając wyniku końcowego.

Jak to wygląda w praktyce

Klikasz krok na liście Zastosowane kroki, dodajesz filtr albo zmieniasz typ kolumny i czekasz, aż podgląd się wypełni. Po prawej stronie paska stanu edytora pojawia się godzina pobrania podglądu, a po przełączeniu na inne zapytanie wszystko zaczyna się od nowa. Przy wyłączonych podglądach w tle możesz zobaczyć komunikat: „Odświeżanie podglądu zostało anulowane. Jeśli wyłączyłeś pobieranie podglądów danych w tle, to jest oczekiwane, ponieważ w danej chwili będzie można odświeżać tylko jedną wersję zapoznawczą. Nie ma to wpływu na załadowane dane.” (ang. „Preview refresh was canceled.”).

Bywa też odwrotnie: podgląd pojawia się szybko, ale pokazuje stare dane, a u góry okna edytora widać żółty pasek z informacją „Ten podgląd może pochodzić maksymalnie sprzed 3 dni.” (ang. „This preview may be up to 3 days old.”).

Dlaczego tak się dzieje

Podgląd w edytorze liczy się od źródła dla każdego kroku. Microsoft opisuje, że operacje takie jak filtrowanie działają strumieniowo i czytają tylko tyle danych, ile potrzeba do wypełnienia podglądu. Sortowanie, grupowanie, scalanie i przestawianie muszą najpierw przeczytać wszystkie wiersze, bo pierwsze posortowane wiersze mogą leżeć na końcu źródła. Krok sortowania postawiony wcześnie spowalnia więc każdy kolejny podgląd. Przy źródłach składających zapytania nowy krok bywa wysyłany do źródła od nowa, zamiast korzystać z poprzedniego wyniku.

Edytor domyślnie pokazuje i profiluje pierwsze 1000 wierszy. Po przełączeniu profilowania na cały zestaw danych każdy krok przetwarza wszystkie wiersze. Gdy pobieranie podglądów w tle jest włączone, Power Query może odświeżać podglądy kilku zapytań naraz. Po jego wyłączeniu, jak wynika z komunikatu edytora, w danej chwili odświeża się tylko jeden.

Power Query przechowuje kopie wyników podglądu na dysku lokalnym, żeby szybciej je wyświetlać, i według Microsoftu nie odświeża tej pamięci podręcznej automatycznie. Stąd szybki, ale nieaktualny podgląd z żółtym paskiem.

Jak to rozwiązać krok po kroku

1. Wróć do profilowania 1000 wierszy. Kliknij napis o profilowaniu na pasku stanu edytora i wybierz Profilowanie kolumn w oparciu o następującą liczbę pierwszych wierszy: 1000. Pełny zestaw danych włączaj tylko na chwilę kontroli jakości.
2. Ogranicz dane na czas budowy. Na karcie Strona główna rozwiń Zarządzaj parametrami, wybierz Nowy parametr, nazwij go LimitWierszy, ustaw typ liczbowy i wartość bieżącą 1000. Kliknij prawym przyciskiem pierwszy krok, który zwraca wiersze danych (przy pliku CSV zwykle Źródło), wybierz Wstaw krok po i wpisz w pasku formuły = if LimitWierszy > 0 then Table.FirstN(Źródło, LimitWierszy) else Źródło, podstawiając nazwę tego kroku. Wartość 0 przywraca pełne dane.
3. Albo użyj kroku tymczasowego. Microsoft zaleca prostszy wariant: zaraz po wczytaniu danych użyj Zachowaj wiersze, Zachowaj pierwsze wiersze, a po dodaniu wszystkich kroków usuń ten krok. Przy łączeniu plików z folderu zamiast wierszy ogranicz pliki: parametr ścieżki, jak w lekcji o parametrach, skieruj na folder z jednym plikiem próbnym.
4. Ciężkie kroki przenieś na koniec. Filtry i usuwanie kolumn ustaw jak najwcześniej, a sortowanie, grupowanie i scalanie jak najpóźniej. Kolejność zmienisz poleceniami Przenieś przed i Przenieś po w menu kroku.
5. Wyłącz podglądy w tle dla ciężkiego pliku. W Excelu na karcie Dane rozwiń Pobierz dane i wybierz Opcje dodatku Query. W części Bieżący skoroszyt otwórz Ładowanie danych i odznacz Zezwalaj na pobieranie podglądów danych w tle. Edytor będzie wtedy odświeżał tylko podgląd zapytania, które oglądasz.
6. Zarządzaj pamięcią podręczną. W tym samym oknie, w sekcji Opcje zarządzania pamięcią podręczną danych, sprawdzisz, ile miejsca zajmują zapisane podglądy (Obecnie używane:), i usuniesz je przyciskiem Wyczyść pamięć podręczną. Gdy podgląd jest nieaktualny, wystarczy na karcie Strona główna kliknąć Odśwież podgląd.

Jak sprawdzić, że zadziałało

Kliknij po kolei kilka kroków na liście Zastosowane kroki. Z ograniczeniem do 1000 wierszy podgląd powinien pojawiać się wyraźnie szybciej niż wcześniej, a godzina pobrania podglądu na pasku stanu zmienia się po każdym odświeżeniu. Formułę ograniczającą sprawdziliśmy w Excelu: przy wartości 3 z pięciu wierszy zostały 3, przy wartości 0 wszystkie 5.

Przed załadowaniem wyniku ustaw parametr LimitWierszy na 0 albo usuń krok Zachowano pierwsze wiersze, a po odświeżeniu porównaj liczbę wierszy w okienku Zapytania i połączenia z oczekiwaną. To główna pułapka tej metody: zapomniane ograniczenie ładuje do arkusza tylko próbkę.

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