Suma narastająca w Power Query działa bardzo wolno - jak ją policzyć szybciej

Power Query w Excelu, Power BI i Fabric

Suma narastająca w Power Query działa bardzo wolno - jak ją policzyć szybciej

W skrócie

Suma narastająca w Power Query to częsty krok w raportach sprzedaży: wartość od początku okresu liczona wiersz po wierszu. Najpopularniejszy wzorzec z kolumną indeksu oraz funkcjami List.Sum i List.FirstN działa poprawnie na kilkudziesięciu wierszach, ale na pełnych danych odświeżanie wydłuża się nieproporcjonalnie. Pokazujemy, skąd bierze się spowolnienie, i podajemy szybszy wzorzec sprawdzony w Excelu, z pomiarem czasu obu podejść.

Jak to wygląda w praktyce

Zapytanie ma kolumnę indeksu dodaną poleceniem Dodaj kolumnę, Kolumna indeksu, Od 1 oraz kolumnę niestandardową z formułą:

= List.Sum(List.FirstN(#"Dodano indeks"[Wartość netto], [Indeks]))

Wyniki są poprawne: dla wartości 10, 20, 30 i 40 kolumna pokazuje 10, 30, 60 i 100. Problemem jest czas. Na kilkudziesięciu wierszach wynik pojawia się od razu, a na pełnych danych odświeżanie wyraźnie się wydłuża, choć Excel nie pokazuje żadnego błędu. W naszym teście w Excelu na 9256 wierszach (tyle linii paragonów mają cztery miesięczne pliki sprzedaży z kursu) sama ta kolumna liczyła się 18,5 sekundy.

Dlaczego tak się dzieje

Formuła w wierszu numer k bierze z kolumny k pierwszych wartości i sumuje je od nowa. Wiersz 1 to jedno dodawanie, wiersz 2 dwa, wiersz 1000 tysiąc. Przy n wierszach daje to n(n+1)/2 dodawań: dla 9256 wierszy 42 841 396, a dla 100 000 wierszy 5 000 050 000. Dwa razy więcej danych oznacza mniej więcej cztery razy więcej pracy.

Funkcja List.Buffer wczytuje listę do pamięci i według dokumentacji Microsoftu zwraca stabilną listę. W naszym teście samo zbuforowanie kolumny w starej formule skróciło czas z 18,5 do 1,3 sekundy, ale liczba dodawań została ta sama. Pracę zmniejsza dopiero zmiana algorytmu: każda suma to poprzednia suma plus bieżąca wartość, więc na n wierszy wystarczy n dodawań. Ten sam test ze wzorcem poniżej trwał 0,02 sekundy.

Suma narastająca zależy też od kolejności wierszy. Microsoft zaznacza, że kolejność sortowania nie musi przetrwać grupowania (Table.Group), scalania (Table.NestedJoin) ani usuwania duplikatów (Table.Distinct). Dlatego sortowanie stoi we wzorcu tuż przed obliczeniem:

Posortowano = Table.Sort(#"Ostatni krok",
        {{"Data", Order.Ascending}, {"Nr paragonu", Order.Ascending}}),
    Wartosci = List.Buffer(List.Transform(Posortowano[Wartość netto], each _ ?? 0)),
    Narastajaco = List.Generate(
        () => [i = 0, s = Wartosci{0}],
        each [i] < List.Count(Wartosci),
        each [i = [i] + 1, s = [s] + Wartosci{[i] + 1}],
        each [s]),
    Typ = Value.Type(Table.AddColumn(Posortowano, "Narastająco", each null, type number)),
    Wynik = Table.FromColumns(Table.ToColumns(Posortowano) & {Narastajaco}, Typ)
in
    Wynik

Jak to rozwiązać krok po kroku

1. Usuń stare kroki. Na liście Zastosowane kroki kliknij krzyżyk przy kroku z kolumną niestandardową (zwykle Dodano kolumnę niestandardową), a potem przy Dodano indeks, jeśli indeks nie jest potrzebny do niczego innego.
2. Otwórz kod zapytania. Na karcie Strona główna kliknij Edytor zaawansowany. Zapamiętaj nazwę kroku, który stoi po słowie in, na przykład #"Zmieniono typ".
3. Wklej wzorzec. Dopisz przecinek na końcu ostatniego kroku przed in, wklej pod nim kroki z bloku powyżej i usuń stare zakończenie, bo wzorzec ma własne in Wynik. W kroku Posortowano zamiast #"Ostatni krok" wpisz zapamiętaną nazwę i kliknij Gotowe.
4. Dopasuj kolumny. Wzorzec sortuje po kolumnach Data i Nr paragonu z danych kursu i sumuje Wartość netto. Drugi poziom sortowania sprawia, że kolejność paragonów z tego samego dnia jest zawsze taka sama. U siebie podstaw własne nazwy.
5. Zrozum, co robi każdy krok. List.Buffer wczytuje kolumnę raz, a each _ ?? 0 zamienia puste wartości na zero, bo dodanie null daje null i psuje wszystkie kolejne sumy. List.Generate idzie po liście z licznikiem i i sumą s. Value.Type z Table.FromColumns dokleja wynik jako kolumnę typu liczbowego. Bez tego nowa kolumna miałaby typ dowolny.
6. W Power BI rozważ miarę DAX. Jeśli suma narastająca ma reagować na filtry raportu, na przykład na wybór sklepu albo miesiąca, policz ją miarą DAX zamiast kolumną w Power Query. Kolumna z Power Query liczy się raz, przy odświeżeniu, dla całej tabeli.

Jak sprawdzić, że zadziałało

Ostatnia wartość w kolumnie Narastająco musi być równa sumie całej kolumny Wartość netto. Sprawdzisz to bez kalkulatora: kliknij prawym przyciskiem ostatni krok, wybierz Wstaw krok po i wpisz w pasku formuły = List.Sum(Posortowano[Wartość netto]) = List.Last(Wynik[Narastająco]). Jeśli formuła zwróci prawdę (true), sumy się zgadzają. W naszym teście z pustą wartością w danych kontrola zwróciła prawdę, a sumy rosły 10, 10, 40, 80. Potem usuń krok kontrolny.

Porównaj też czas: odśwież zapytanie przed zmianą i po niej na tych samych danych. Żeby problem nie wrócił, nie dodawaj za krokiem sumy narastającej grupowania, scalania ani usuwania duplikatów. Jeśli są potrzebne, umieść je przed sortowaniem.

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