Bezpłatny kurs Power Query: grupowanie, przestawianie i anulowanie przestawienia kolumn

W skrócieGrupowanie zamienia linie paragonów w sprzedaż sklepu w miesiącu, a anulowanie przestawienia zamienia budżet z miesiącami w kolumnach w tabelę, którą da się połączyć ze sprzedażą. Na końcu liczymy realizację budżetu i budujemy macierz sklepów i miesięcy.

Grupowanie, przestawianie i anulowanie przestawienia

Mamy 9250 linii paragonów. Raport realizacji budżetu potrzebuje sumy sprzedaży na sklep i miesiąc oraz budżetu w tym samym układzie. Zbudujemy to jako osobne zapytanie, żeby szczegółowe dane zostały dostępne do innych analiz.

Odwołanie zamiast duplikatu

1. Kliknij prawym przyciskiem zapytanie Sprzedaz_miesieczna w panelu zapytań i wybierz Odwołanie.

Różnica jest ważna. Duplikat to niezależna kopia wszystkich kroków: jeśli później poprawisz czyszczenie w oryginale, kopia tej poprawki nie dostanie. Odwołanie zaczyna się od wyniku oryginału, więc każda poprawka w zapytaniu bazowym od razu trafia do wszystkich zapytań, które się do niego odwołują. Nowe zapytanie ma jeden krok Źródło z formułą = Sprzedaz_miesieczna. Nazwij je Sprzedaz_wg_sklepu_i_miesiaca.

Dodaj początek miesiąca i pogrupuj

2. Zaznacz kolumnę Data, na karcie Dodaj kolumnę rozwiń Data > Miesiąc i wybierz Początek miesiąca. Każda data zamieni się w pierwszy dzień swojego miesiąca, co jest wygodniejszym kluczem niż osobne kolumny rok i miesiąc.

3. Na karcie Strona główna kliknij Grupowanie według i przełącz okno w tryb Zaawansowane. Grupuj po Sklep i (przyciskiem Dodawanie grupowania) po Początek miesiąca. W części agregacji ustaw kolumnę Sprzedaż netto z operacją Suma na kolumnie Wartość netto, a przyciskiem Dodawanie agregacji drugą: Liczba pozycji z operacją Zlicz wiersze.

Operacja Wszystkie wiersze zasługuje na osobną wzmiankę. Zamiast liczby zwraca dla każdej grupy zagnieżdżoną tabelę ze wszystkimi wierszami tej grupy. Na takiej kolumnie można potem wykonać operacje, których interfejs grupowania nie oferuje, na przykład wybrać najdroższy paragon w każdym sklepie albo dodać numer kolejny w obrębie grupy.

Ranking zbudujesz, sortując pogrupowaną tabelę malejąco po sprzedaży i dodając Kolumnę indeksu od 1. Sumy narastające i udziały w całości też da się policzyć w Power Query (funkcjami List.Sum i List.FirstN na posortowanej tabeli), ale przy dużych danych i wtedy, gdy wynik ma reagować na filtry raportu, lepiej zostawić je miarom DAX w Power BI albo tabeli przestawnej.

Anuluj przestawienie budżetu

Budżet ktoś przygotował tak, jak wygodnie się go czyta: sklepy w wierszach, dwanaście miesięcy w kolumnach. Tak się go jednak nie da połączyć ze sprzedażą ani filtrować po miesiącu.

4. Zaimportuj tabelę tBudzet (Nowe źródło > Plik > Skoroszyt programu Excel). Zaznacz kolumnę Sklep, na karcie Przekształć rozwiń Anuluj przestawienie kolumn i wybierz Anuluj przestawienie innych kolumn.

Dlaczego innych kolumn, a nie zaznaczonych? Bo Power Query zapamiętuje w kroku kolumnę, której nie przestawia (Sklep), a nie listę miesięcy. Kiedy budżet zostanie rozszerzony o kolejny rok albo dodatkową kolumnę, zapytanie obejmie ją bez zmian. Wariant tylko zaznaczonych kolumn zapisuje listę nazw i nowe kolumny pomija.

5. Zmień nazwy kolumn dwuklikiem w nagłówku: Atrybut na Miesiąc, Wartość na Budżet. Nazwa miesiąca to tekst, a żeby połączyć budżet ze sprzedażą, potrzebujemy daty. Dodaj kolumnę niestandardową Początek miesiąca z formułą:

= #date(2026, List.PositionOf({"Styczeń", "Luty", "Marzec", "Kwiecień", "Maj", "Czerwiec", "Lipiec", "Sierpień", "Wrzesień", "Październik", "Listopad", "Grudzień"}, [Miesiąc]) + 1, 1)

Scal sprzedaż z budżetem po dwóch kolumnach

6. Wróć do zapytania Sprzedaz_wg_sklepu_i_miesiaca i kliknij Scal zapytania. Tym razem kluczem są dwie kolumny: w górnej tabeli kliknij Sklep, a potem z wciśniętym Ctrl kliknij Początek miesiąca. W tabeli tBudzet zaznacz te same kolumny w tej samej kolejności. Małe cyfry 1 i 2 przy nagłówkach pokazują, które kolumny tworzą parę.

7. Rozwiń kolumnę tBudzet, zostawiając tylko Budżet i bez prefiksu. Dodaj kolumnę niestandardową Realizacja budżetu z formułą [Sprzedaż netto] / [Budżet] i nadaj jej typ Wartość procentowa.

Przestaw kolumnę, czyli droga w drugą stronę

Czasem potrzebujesz odwrotności: z układu długiego zrobić macierz, na przykład sklepy w wierszach i miesiące w kolumnach do wydruku. Służy do tego Kolumna przestawna na karcie Przekształć. W naszym skoroszycie zrobiliśmy to na odwołaniu do zestawienia (zapytanie Macierz_sklep_miesiac), zostawiając tylko kolumny Sklep, Początek miesiąca i Sprzedaż netto.

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

✕