Table.Buffer - kiedy przyspiesza zapytanie, a kiedy je spowalnia

Power Query w Excelu, Power BI i Fabric

Table.Buffer - kiedy przyspiesza zapytanie, a kiedy je spowalnia

W skrócie

Po funkcję Table.Buffer w Power Query sięgasz zwykle wtedy, gdy zapytanie odświeża się zbyt długo albo Usuń duplikaty zostawia nie ten wiersz, którego się spodziewasz. Ta sama funkcja w jednym zapytaniu skraca odświeżanie, a w innym je wydłuża. Pokazujemy, co dokładnie robi, w których dwóch sytuacjach pomaga, kiedy szkodzi i jak sprawdzić efekt na własnym zapytaniu.

Jak to wygląda w praktyce

Problem pojawia się w czterech sytuacjach.

• Wolne zapytanie z tabelą pomocniczą. Kolumna niestandardowa w każdym wierszu przeszukuje małą tabelę z innego zapytania, na przykład progi segmentów cenowych albo cennik. Odświeżanie trwa długo, choć tabela pomocnicza ma kilka wierszy.
• Wolniejsze zapytanie do bazy po dodaniu bufora. Po objęciu tabeli z SQL Server funkcją Table.Buffer filtr i grupowanie przestają trafiać do serwera. W Power BI Desktop pozycja Wyświetl zapytanie natywne w menu kroków za buforem jest wyszarzona.
• Błąd pamięci. Przy bardzo dużej tabeli 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.”).
• Nieprzewidywalne usuwanie duplikatów. Sortujesz sprzedaż malejąco po dacie, usuwasz duplikaty w kolumnie Sklep i oczekujesz najnowszej transakcji każdego sklepu. Często tak wychodzi, ale Microsoft nie gwarantuje, który duplikat zostanie zachowany.

Dlaczego tak się dzieje

Opis funkcji w Power Query mówi, że Table.Buffer „buforuje tabelę w pamięci, izolując ją od zewnętrznych zmian podczas oceny”. Funkcja wymusza obliczenie wszystkich wartości w komórkach i od tego miejsca kolejne kroki pracują na kopii w pamięci, a nie na źródle. Z tego wynikają oba skutki.

Zysk. Tabela pomocnicza użyta w funkcji wywoływanej dla każdego wiersza może być bez bufora odczytywana wielokrotnie. Po buforowaniu kolejne odczyty korzystają z jednej kopii w pamięci, a przy kilkudziesięciu wierszach progów czy cennika taka kopia jest mała. Tak wygląda zapytanie, w którym bufor pracuje na Twoją korzyść:

let
    Progi = Table.Buffer(tProgi),
    Wynik = Table.AddColumn(Sprzedaz_miesieczna, "Segment",
        each Table.SelectRows(Progi,
            (p) => p[Od] <= [Wartość netto] and [Wartość netto] < p[Do]){0}[Segment],
        type text)
in
    Wynik

Ten wzorzec uruchomiliśmy w Excelu na przykładowych danych: wartości 45,5, 250 i 1200 dostały segmenty niski, średni i wysoki.

Strata. Według opisu funkcja może spowolnić zapytanie „z powodu dodatkowego kosztu odczytu wszystkich danych i przechowywania ich w pamięci, a także dlatego, że buforowanie uniemożliwia składanie podrzędne”. Składanie zapytań to zamiana kroków na jedno zapytanie wykonywane przez bazę. Za buforem filtr na dużej tabeli faktów nie trafia już do serwera: Power Query pobiera całą tabelę i filtruje ją u siebie.

Kolejność. Microsoft zaznacza, że kolejność sortowania nie musi przetrwać grupowania (Table.Group), scalania (Table.NestedJoin) ani usuwania duplikatów (Table.Distinct), bo Power Query może pominąć część operacji albo przekazać je do źródła. Opis Table.Distinct zaleca: „Jeśli chcesz, aby usunięcie duplikatów działało w sposób przewidywalny, najpierw zbuforuj tabelę przy użyciu funkcji Table.Buffer”.

Jak to rozwiązać krok po kroku

1. Ustal, który przypadek masz przed sobą. Otwórz Edytor zaawansowany i znajdź Table.Buffer. Sprawdź, co jest buforowane: mała tabela pomocnicza czytana w każdym wierszu, posortowana tabela przed usuwaniem duplikatów czy duża tabela z bazy danych.
2. Mała tabela pomocnicza: zbuforuj ją raz, w osobnym kroku. Dodaj na początku zapytania krok Progi = Table.Buffer(tProgi), a w funkcji wywoływanej dla wierszy odwołuj się do Progi, nie do tProgi. Bufor wpisany wewnątrz each powstawałby od nowa przy każdym wierszu.
3. Usuwanie duplikatów zależne od kolejności: bufor między sortowaniem a Usuń duplikaty. Posortuj tabelę poleceniem Sortuj malejąco na kolumnie Data. Kliknij prawym przyciskiem krok Posortowano wiersze, wybierz Wstaw krok po i w pasku formuły wpisz = Table.Buffer(#"Posortowano wiersze"). Dopiero na tym kroku zaznacz kolumnę Sklep i na karcie Strona główna rozwiń Usuń wiersze, a potem wybierz Usuń duplikaty.
4. Wynik niezależny od kolejności: grupowanie. Formuła Table.Group(Sprzedaz_miesieczna, {"Sklep"}, {{"Ostatnia", each Table.Max(_, "Data"), type record}}) zwraca dla każdego sklepu cały wiersz z najpóźniejszą datą, a Table.ExpandRecordColumn rozwija go do kolumn. Microsoft wymienia też sortowanie po operacji i kolumnę rangi jako drogi bez bufora.
5. Duża tabela z bazy: usuń bufor. Usuń krok z Table.Buffer krzyżykiem na liście Zastosowane kroki i przenieś filtry oraz usuwanie kolumn na początek zapytania, żeby wykonał je serwer. Jeśli bufor miał tylko zatrzymać składanie, użyj Table.StopFolding, która według opisu „zapobiega uruchamianiu jakichkolwiek operacji podrzędnych względem oryginalnego źródła danych” i niczego nie buforuje.
6. Błąd pamięci: szukaj ciężkich operacji na pełnej tabeli. Sortowanie, scalanie, grupowanie i usuwanie duplikatów zużywają najwięcej pamięci. Microsoft zaleca, żeby takie operacje składały się do źródła, a zbędne, na przykład sortowanie bez celu, usunąć.

Jak sprawdzić, że zadziałało

W zapytaniu do bazy w Power BI Desktop kliknij prawym przyciskiem ostatni krok. Aktywna pozycja Wyświetl zapytanie natywne oznacza, że kroki do tego miejsca składają się do serwera. Czas przed zmianą i po niej porównasz na karcie Narzędzia: Diagnozuj krok mierzy wykonanie zaznaczonego kroku. Edytor w Excelu tej karty nie ma, więc tam porównaj czas odświeżenia całego zapytania.

Przy usuwaniu duplikatów policz wiersze przed zmianą i po niej oraz sprawdź ręcznie kilka sklepów: każdy ma zachować wiersz z najpóźniejszą datą. W naszym teście w Excelu sortowanie, bufor i Usuń duplikaty zostawiły Nordvella Gdańsk z datą 02.03.2026 i Nordvella Łódź z datą 09.02.2026, czyli to samo co grupowanie z Table.Max. Żeby po miesiącu było jasne, po co bufor stoi w zapytaniu, nadaj krokowi opisową nazwę klawiszem F2, na przykład Bufor progów segmentu.

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