Pierwszy kontakt z opcją Załaduj do… w Power Query nie jest łatwy. Co więcej, okno w różnych Excelach wygląda inaczej. Jak się w tym nie zgubić i wybrać odpowiednią opcję? Oto krótkie porównanie wszystkich sposobów ładowania danych do tabeli przestawnej dostępnych na końcu zapytania Power Query.

Porównanie wagi plików przy źródle z 1 mln rekordów

Zacznijmy od końcowych wniosków nt. rozmiaru plik. Przykład dla 10 kolumn x 1 mln wierszy. Zamiast 50 MB możemy mieć z danymi mniej niż 1 MB (opcja 3.), a nawet mniej niż 100 kB (opcja 2 i pół – bez danych). Wszystko zależy oczywiście od wielu czynników (m.in. powtarzalność danych, liczba kolumn i ich typ danych), ale możemy poniższe porównanie traktować jako optymistyczną prognozę.

Opcja 1. Tabela

Domyślna opcja, której wybranie powoduje, że wszystkie dane z zapytania lądują w tabeli Excel. Na tabeli wstawiamy zaś tabelę przestawną.

 

Zalety

• Widać efekt zapytania
• Widać liczbę załadowanych wierszy

Wady

• Dane przechowywane są dwukrotnie (raz w tabeli, drugi raz w pamięci podręcznej tabeli przestawnej)
• Ograniczenie do wielkości arkusza: 1 048 575 wierszy danych + nagłówek
• Nie działa prawidłowo przycisk Odśwież wszystko, a odświeżanie tabeli przestawnej musi się odbyć na dwa kliknięcia: najpierw odśwież zapytanie, potem odśwież tabelę przestawną (inaczej tabela przestawna odświeża się na starej tabeli, nie czekając na odświeżenie zapytania)

Opcja 2. Tabela przestawna

Ta opcja funkcjonuje od niedawna w Excelu z Office 365. Dodanie nowego przycisku pozwoliło bardziej transparentnie lądować z zapytaniem jako połączeniem od razu do tabeli przestawnej.

W Excelu 365 pojawia się wówczas liczba wierszy (1), a pola sortują się alfabetycznie (2), co możemy zmienić w opcjach tabeli przestawnej (3).

Jak wstawić tabelę przestawną w Excelu 2010, 2013 lub 2016?

Zapisujemy zapytanie jako Tylko połączenie.

Następnie wstawiamy tabelę przestawną > Użyj zewnętrznego źródła danych > Wybierz połączenie.

Wskaż zapytanie.

Zalety

• Dane są przechowywane tylko 1 raz – w pamięci podręcznej tabeli przestawnej
• Można w ten sposób załadować ponad 1 mln wierszy, choć nie jest to rekomendowane z uwagi na opcję z modelem danych (w moim Excelu udało się prawie 7 mln)
• Przycisk Odśwież wszystko działa prawidłowo
• Widać liczbą załadowanych wierszy (tylko w Office 365)

Wady

• Nie widać danych (czasem użytkownicy chcą je obejrzeć i filtrować; w tej opcji muszą umieć wykonać drążenie na Sumie końcowej tabeli przestawnej, aby rozpakować dane do tabeli).
• Nie da się załadować większej liczby danych (np. kilkunastu milionów rekordów)

Opcja 2 i pół. Tabela przestawna + odznacz Zapisz dane źródłowe z plikiem

Warto przy okazji tej opcji wspomnieć o wariancie Opcji 2. z wyłączonym ładowaniem danych. Jest to opcja tabeli przestawnej, która powoduje, że Excel kasuje w momencie wyłączenia pliku pamięć podręczną tabeli przestawnej. Dzięki temu plik staje się bardzo lekki, ale tabela przestawna przestaje być interaktywna (brakuje jej danych) i działa zupełnie statycznie.

Kiedy ta opcja może się przydać?

Wysyłanie ciężkiego pliku – czasem plik jest za duży, żeby dane przesłać, ale użytkownik końcowy ma dostęp do źródła. Wówczas można wysłać mu lekki plik (wyłączone ładowanie danych), a po odświeżeniu zapytań na swoim komputerze i pobraniu danych, tabela przestawna staje się na moment responsywna. Po zapisaniu przez odbiorcę pliku po skończonej analizie, dane znów są przez Excela kasowane.

Usunięcie danych źródłowych – jeśli chcemy usunąć dane źródłowe z zapytania i godzimy się na brak możliwości modyfikacji tabel przestawnych, opcja ta będzie prostym zabezpieczeniem naszych danych.

Opcja 3. Utwórz tylko połączenie i dodaj dane do modelu danych

W Power BI jest to opcja domyślna. W Excelu trzeba ją włączyć dwoma kliknięciami. Elementy zaznaczone niebieskimi strzałkami też zadziałają jak połączenie, ale od razu pozwolą na wstawienie tabeli przestawnej lub wykresu przestawnego.

Wstawianie tabel przestawnych na modelu danych jest od 2016 r. bardzo proste.

Zalety

• Dane przeładowane przez model danych są maksymalnie skompresowane
• Można załadować kilkanaście / kilkadziesiąt milionów wierszy (w zależności od wersji Excela i  pamięci)
• Odświeżanie na jeden „klik” (Odśwież wszystko)
• Wszystkie inne zalety pracy z Power Pivot i modelem danych

Wady

• Dane widać tylko w Power Pivot, więc potrzebna jest wersja Excela z tym dodatkiem, żeby je obejrzeć
• Brak pól obliczeniowych tabel przestawnych (co dla niektórych użytkowników może być wadą, bo jedyną opcją jest miara w języku DAX)

Oczywiście dla użytkowników Excela z Power Pivot, wad tych nie ma.

A Ty z której opcji korzystasz?

Podziel się w komentarzu.