Zapytania pośrednie ładują się do arkuszy - plik rośnie i zwalnia
Udostępnij na:
Spis treści
Power Query w Excelu
Zapytania pośrednie ładują się do arkuszy - plik rośnie i zwalnia
W skrócie
Każde nowe zapytanie po kliknięciu Zamknij i załaduj trafia do arkusza jako osobna tabela, także słowniki, budżet i zapytania pomocnicze, więc skoroszyt rośnie, a odświeżanie wpisuje do arkuszy zbędne dane.
Zwykłe Zamknij i załaduj wstawia wynik na nowym arkuszu, a zapytania pośrednie nie muszą być w arkuszu, żeby inne zapytania mogły się do nich odwoływać.
W Power Query dla zapytań pośrednich tylko utwórz połączenie: zaznacz Utwórz tylko połączenie w oknie Importowanie danych, a w Power BI odznacz Włącz ładowanie.
Zapytania pośrednie, takie jak słowniki produktów i sklepów, budżet czy zapytania pomocnicze z łączenia plików z folderu, są potrzebne tylko jako etapy obliczeń. Jeśli każde z nich ląduje w osobnym arkuszu, plik rośnie, a w skoroszycie przybywa kart, na które nikt nie patrzy. W Power Query dla takich zapytań tylko utwórz połączenie. Pokazujemy, jak przestawić istniejące zapytania, jak obsłużyć ostrzeżenia Excela i jak sprawić, żeby nowe zapytania nie ładowały się do arkusza.
Jak to wygląda w praktyce
Po kliknięciu Zamknij i załaduj w skoroszycie przybywa arkuszy z tabelami, których nikt nie czyta: katalog produktów, lista sklepów, budżet, wyniki zapytań pomocniczych. W okienku Zapytania i połączenia przy prawie każdym zapytaniu widać liczbę załadowanych wierszy zamiast informacji Tylko połączenie.
Każda taka tabela to dane zapisane w pliku i wpisywane do arkusza przy każdym Odśwież wszystko. W oknie Zależności zapytań, które otwierasz na karcie Widok w edytorze, przy takich zapytaniach widać stan Załadowano do arkusza. Excel nie zgłasza błędu, plik jest po prostu cięższy, niż musi.
Dlaczego tak się dzieje
Zwykłe Zamknij i załaduj wstawia każde nowe zapytanie jako osobną tabelę na nowym arkuszu, także słowniki i budżet. Do arkusza trafia więc wszystko, co powstało w edytorze, choć zapytanie pośrednie wcale nie musi tam być. Zapytanie zapisane jako samo połączenie dalej istnieje w skoroszycie, a inne zapytania mogą się do niego odwoływać, scalać je i dołączać.
Koszt ponosi arkusz i czas odświeżania. Kilka zapytań korzystających z tego samego, ciężkiego źródła może je przy tym wczytywać osobno, o czym piszemy w lekcji o wydajności. Dlatego wśród dobrych praktyk wydajności jest zasada: nie ładuj zapytań pośrednich. W Excelu wybierasz samo połączenie, w Power BI odznaczasz Włącz ładowanie. W Power BI każde dodatkowe zapytanie w modelu powiększa plik i wydłuża odświeżanie.
Jak to rozwiązać krok po kroku
Ustal, co ma trafić do arkusza. Do arkusza ładujesz tylko to, na co ktoś będzie patrzył. Słowniki, budżet, zapytania z grupy pomocniczej łączenia plików i kroki pośrednie zostają jako samo połączenie.
Przestaw istniejące zapytanie. Na karcie Dane otwórz Zapytania i połączenia, kliknij zapytanie pośrednie prawym przyciskiem, wybierz Załaduj do, w oknie Importowanie danych zaznacz Utwórz tylko połączenie i kliknij OK.
Potwierdź usunięcie tabeli. Excel pokaże okno Ostrzeżenie o możliwej utracie danych: wyłączenie ładowania do arkusza usunie tabelę tego zapytania z arkusza, a dostosowania i odwołania do niej zostaną utracone. Upewnij się, że żadna formuła nie czyta tej tabeli, i kliknij Kontynuuj. Jeśli arkusz zostanie pusty, usuń go.
Albo usuń cały arkusz z tabelą. Excel zapyta wtedy: Usunięto tabelę skojarzoną z zapytaniem („tProdukty”). Czy chcesz wyłączyć ładowanie zapytania do arkusza lub usunąć zapytanie? Wybierz Wyłącz ładowanie, żeby zapytanie zostało w skoroszycie jako samo połączenie.
Zmień zachowanie dla nowych zapytań. Przy pierwszym ładowaniu zamiast zwykłego przycisku wybierz Zamknij i załaduj do i zaznacz Utwórz tylko połączenie. Ustawienie zastosuje się do wszystkich nowych zapytań, a wybrane potem załadujesz osobno. Stałą zmianę zrobisz w Pobierz dane, Opcje dodatku Query, w sekcji Globalne, Ładowanie danych: zaznacz Określ niestandardowe domyślne ustawienia ładowania i odznacz Załaduj do arkusza.
W Power BI odznacz Włącz ładowanie. W edytorze Power Query w Power BI Desktop kliknij zapytanie pośrednie prawym przyciskiem i odznacz Włącz ładowanie. Nazwa zapytania w panelu zapytań będzie zapisana kursywą, a pole Uwzględnij w odświeżeniu raportu stanie się nieaktywne.
Dwie opcje zamknięcia edytora Power Query: Zamknij i załaduj oraz Zamknij i załaduj do, która pozwala wybrać sposób załadowania.
Jak sprawdzić, że zadziałało
W okienku Zapytania i połączenia przy wszystkich zapytaniach pośrednich powinna stać informacja Tylko połączenie., a liczbę wierszy pokazują tylko zapytania z wynikiem, na który ktoś patrzy. W kursie po tej zmianie wszystkie 9 zapytań było samym połączeniem, a do arkusza trafiło potem tylko zestawienie realizacji budżetu z 60 wierszami. Kliknij Odśwież wszystko i sprawdź, czy wynik końcowy dalej się aktualizuje: zapytania pośrednie są obliczane jako jego część.
Na przyszłość każde nowe zapytanie ładuj przez Zamknij i załaduj do i od razu wybieraj sposób załadowania. Co jakiś czas przejrzyj listę w okienku: każde zapytanie z liczbą wierszy powinno mieć powód, żeby być w arkuszu.
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.
Czy zapytanie ustawione jako samo połączenie dalej się odświeża?
Tak, jeśli korzysta z niego zapytanie ładowane do arkusza albo do modelu danych. Przy odświeżeniu wynik końcowy oblicza wszystkie zapytania, do których się odwołuje, więc słowniki i kroki pośrednie liczą się razem z nim.
Jak sprawdzić, które zapytania ładują się do arkusza?
Otwórz okienko Zapytania i połączenia na karcie Dane. Zapytania ładowane do arkusza mają pod nazwą liczbę załadowanych wierszy, a pozostałe informację Tylko połączenie. Stan ładowania widać też w oknie Zależności zapytań na karcie Widok w edytorze.
Czy zapytanie bez tabeli w arkuszu może zasilać model danych?
Tak. W oknie Importowanie danych zaznacz Utwórz tylko połączenie oraz pole Dodaj te dane do modelu danych. Wynik trafi wtedy do modelu danych, a w okienku Zapytania i połączenia Excel nadal opisze zapytanie jako tylko połączenie.
Czym w Power BI różni się Włącz ładowanie od Uwzględnij w odświeżeniu raportu?
Włącz ładowanie decyduje, czy zapytanie w ogóle trafia do modelu. Uwzględnij w odświeżeniu raportu dotyczy tabel, które są w modelu, ale nie muszą się pobierać przy każdym odświeżeniu, na przykład rzadko zmienianego słownika albo archiwum.
Komentarze (0)
Brak komentarzy...