Wynik zapytania jest zbyt duży, aby załadować go do arkusza - limit 1 048 576 wierszy

Power Query w Excelu

Wynik zapytania jest zbyt duży, aby załadować go do arkusza - limit 1 048 576 wierszy

W skrócie

Power Query bez trudu przetwarza więcej niż 1 048 576 wierszy, ale arkusz Excela więcej nie pomieści. Na wynik większy niż ten limit Power Query w Excelu ma komunikat, który odsyła do modelu danych. Pokazujemy, co dokładnie zostaje w arkuszu, jak przełączyć ładowanie na model danych i kiedy lepiej zmniejszyć wynik grupowaniem jeszcze w zapytaniu.

Jak to wygląda w praktyce

Zapytanie w edytorze działa i pokazuje podgląd, a kłopot zaczyna się przy ładowaniu wyniku do arkusza. Power Query w Excelu opisuje tę sytuację komunikatem: „Wynik tego zapytania jest zbyt duży, aby można było załadować go do określonej lokalizacji w arkuszu. Arkusze mają limit 1 048 576 wierszy i 16 384 kolumn. Załaduj to zapytanie do modelu danych.” (ang. „The result of this query is too large to be loaded to the specified location on the worksheet. Worksheets have a limit of 1,048,576 rows and 16,384 columns. Please load the query to the Data Model instead.”).

Sprawdziliśmy w Excelu, co zostaje w arkuszu, gdy wynik ma dokładnie 1 048 576 wierszy. Tabela zaczynająca się w komórce A1 zajęła całą kolumnę do ostatniego wiersza arkusza i miała 1 048 575 wierszy danych, bo jeden wiersz zajął nagłówek. Ostatni wiersz wyniku do arkusza nie trafił. Każda suma albo tabela przestawna liczona z takiej tabeli pomija więc część danych.

Dlaczego tak się dzieje

Limit dotyczy arkusza, a nie Power Query. Microsoft podaje w specyfikacji Power Query dla Excela, że do arkusza trafi najwyżej 1 048 576 wierszy, a tabela może mieć 16 384 kolumny. Rozmiar danych przetwarzanych przez sam silnik ogranicza dostępna pamięć wirtualna w 64-bitowym Excelu albo około 1 GB w wersji 32-bitowej. W naszym teście zapytanie policzyło 2 000 000 wierszy bez błędu, a pogrupowanie 1 100 000 wierszy według 15 sklepów dało 15 wierszy wyniku.

Tabela w arkuszu zajmuje też wiersz nagłówka, więc tabela wstawiona od komórki A1 zmieści najwyżej 1 048 575 wierszy danych. Model danych Excela, czyli silnik Power Pivot, przyjmuje miliony wierszy i nie podlega limitowi arkusza. Dlatego komunikat odsyła właśnie do modelu danych.

Jak to rozwiązać krok po kroku

1. Sprawdź, ile wierszy zwraca zapytanie. W edytorze na karcie Przekształć, w grupie Tabela, kliknij Zlicz wiersze. Zamiast tabeli zobaczysz jedną liczbę. Zapamiętaj ją i usuń ten krok krzyżykiem na liście Zastosowane kroki.
2. Zmień miejsce ładowania. W Excelu na karcie Dane otwórz Zapytania i połączenia, kliknij zapytanie prawym przyciskiem i wybierz Załaduj do. W oknie Importowanie danych zaznacz Utwórz tylko połączenie oraz pole Dodaj te dane do modelu danych i kliknij OK.
3. Potwierdź usunięcie tabeli z arkusza. Jeśli zapytanie miało już tabelę w arkuszu, Excel pokaże okno Ostrzeżenie o możliwej utracie danych z informacją, że wyłączenie ładowania do arkusza usunie tabelę tego zapytania, a dostosowania i odwołania do niej zostaną utracone. Kliknij Kontynuuj.
4. Zbuduj analizę na modelu. W tym samym oknie Importowanie danych możesz zamiast Utwórz tylko połączenie zaznaczyć Raport w formie tabeli przestawnej, zostawić pole Dodaj te dane do modelu danych i wybrać Nowy arkusz. Tabela przestawna policzy wszystkie wiersze, a do arkusza trafią tylko jej wyniki.
5. Albo zmniejsz wynik w zapytaniu. Jeśli potrzebujesz zestawienia, a nie pojedynczych transakcji, na karcie Strona główna kliknij Grupowanie według i zsumuj wartości na przykład według sklepu i miesiąca, jak w lekcji o grupowaniu. Wcześniej odfiltruj zbędne okresy i usuń niepotrzebne kolumny.
6. Przepnij formuły. Formuły, które czytały dawną tabelę w arkuszu, przenieś na tabelę przestawną z modelu albo na mniejszą, pogrupowaną tabelę wynikową. Odwołania do usuniętej tabeli przestaną działać.

Jak sprawdzić, że zadziałało

Po zmianie w okienku Zapytania i połączenia przy zapytaniu zobaczysz Tylko połączenie., bo Microsoft opisuje, że zapytanie załadowane do modelu danych, a nie do arkusza, jest oznaczane właśnie tak. Zlicz w tabeli przestawnej wiersze dowolnej kolumny bez pustych wartości i porównaj wynik z liczbą z kroku Zlicz wiersze. Obie liczby muszą być równe, także wtedy, gdy przekraczają 1 048 576.

Żeby problem nie wracał, zapytania z danymi transakcyjnymi, które z miesiąca na miesiąc rosną, od razu ładuj do modelu danych. Do arkusza ładuj tylko zestawienia, na które ktoś będzie patrzył, tak jak w lekcji o ładowaniu wyniku.

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