Suma bieżąca wg grup w Power Query

Sumę bieżącą (ang. running total) możesz łatwo uzyskać w zwykłej tabeli przestawnej (Pokaż wartości jako > Suma bieżąca w…). Trudności nie będzie też w POWER tabeli przestawnej wstawionej z Power Pivotem oraz w Power BI, gdzie mamy pełną elastyczność w języku DAX. Ale jak ją uzyskać w Power Query, jeśli suma bieżąca jest nam potrzebna jeszcze na etapie przygotowania danych?

Przykładowy scenariusz do sumy bieżącej w Power Query

Oto przykładowa tabela.

Naszym zadanie jest zbudowanie układu, w którym Sprzedaż po dniach narasta dla każdej kategorii produktu oddzielnie (dlatego będzie to suma bieżąca wg grup). Czuję, że bez pomocy języka M się nie obejdzie, dlatego w Internecie znalazłem inspirację do tego artykułu. Dla uproszczenia przykład pokażę po latach (choć po dniach będzie działać dokładnie tak samo).

Krok 1: Dodaj nowe puste zapytanie

Aby utworzyć puste zapytanie, kliknij prawym przyciskiem w panelu zapytania, a następnie znajdź opcję Puste zapytanie.

Krok 2: Wklej tekst funkcji

= (wartosci as list, grupowanie as list) as list =>
let SumaBiezacaLista = List.Generate(
()=> [SumaBiezaca = wartosci{0}, i=0],
each [i] < List.Count(wartosci),
each try if grupowanie {[i]} = grupowanie{[i]+1}
then [SumaBiezaca = [SumaBiezaca]+wartosci{[i]+1}, i = [i]+1]
else [SumaBiezaca = wartosci{[i]+1},i = [i]+1]
otherwise [i = [i]+1],
each [SumaBiezaca])
in
SumaBiezacaLista

Co robi ta funkcja?

Ta funkcja z wykorzystanie List.Generate działa jak pętla (np. w VBA). Tzn. zaczyna od pierwszej wartości (i = 0) i zaczyna dodawać wartości. Jednak gdy napotka nową grupę, startuje od początku.

Po wciśnięciu Enter pojawi się ikona funkcji.

Krok 3: Posortuj tabelę i zmień nazwę ostatniego kroku w zapytaniu

U mnie suma bieżąca ma być po latach. Dlatego sortuję tabelę po Kategorii produktu, a później po Dacie zamówienia. Dla ułatwienia nadaję ostatniemu krokowi w zapytaniu nazwę Tabela.

Krok 4: Wczytaj dane z kolumn z wartościami i grupowaniami do pamięci

Potrzebne są nam 2 nowe kroki.

= List.Buffer(Tabela[Kategoria produktu])

= List.Buffer(Tabela[Sprzedaż])

Możesz edytować kod języka M w Edytorze zaawansowanym (karta Narzędzia główne) lub dodać nowe kroki po ostatnim kroku.

Dzięki tym krokom Power Query szybko przeliczy dość kosztowną jakby nie patrzeć operację sumowania.

Krok 5: Dodaj ostatni krok – Suma bieżąca

Na koniec jeszcze raz kliknij Wstaw krok po, a następnie wklej odwołanie do naszej funkcji wraz z zaznaczeniem kolumn, na których będziemy pracować oraz ich nowych nazw.

= Table.FromColumns(
    {Tabela[Kategoria produktu], Tabela[Data zamówienia], Tabela[Sprzedaż], SumaBiezaca(BuforWartosci,BuforKategorii)},{"Kategoria produktu","Data zamówienia", "Sprzedaż", "Suma bieżąca"}
)

Praca na listach w Power Query

Jak widać na powyższym przykładzie, praca na listach w Power Query znacznie poszerza nasze możliwości i pozwala uzyskać rzeczy, których nie da się wyklikać. Co więcej, dzięki niektórym technikom (jak buforowanie list) można znacznie przyspieszyć pracę w edytorze zapytań. Dlatego listy jeszcze nie raz pojawią się na naszym blogu.

Pobierz plik Excel z zapytaniem wykonującym sumę bieżącą

Tu możesz pobrać plik Excel i przetestować działanie sumy bieżącej w Power Query.