W dzisiejszym poście zajmiemy się grupowaniem danych z wielu arkuszy. W przykładowych danych mamy imiona i nazwiska osób (klientów), z którymi się spotkaliśmy bądź im coś sprzedaliśmy. Dane te są podzielone na różne arkusze, np. pod względem miesięcy. Dane te chcemy połączyć, podliczyć, np. wyznaczyć ile razy odbyliśmy spotkań z konkretnym klientem lub ile sprzedaliśmy takich samych produktów. Część danych (jeden z arkuszy), na którym będziemy pracować, został przedstawiony na rysunku nr 1.

rys. nr 1 — przykładowy arkusz z danymi

Naszym zadaniem jest uzyskanie podsumowania przedstawionego na rysunku nr 2

rys. nr 2 — Nasz cel — pogrupowane dane

Do tych obliczeń chcemy wykorzystać Power Query. Zaczynamy od pustego arkusza, gdzie wybieramy polecenie Nowe zapytanie (punkt nr 2 na rysunku nr 3) z karty Dane, a następnie Z pliku (punkt nr 3) i Ze skoroszytu (punkt nr 4).

rys. nr 3 — Nowe zapytanie ze skoroszytu

Otworzy nam się okno Importowanie pliku, gdzie musimy wybrać lokalizację naszego pliku z danymi (rys. nr 4). Wybieramy plik i zatwierdzamy nasz wybór przyciskiem Otwórz.

rys. nr 4 — Importowanie danych

Łączymy się z tym plikiem, z którego chcemy pobierać informacje. Otworzy nam się okno Nawigator. W opcjach wyświetlania wybieramy np. Styczeń – zaznaczony na rysunku zieloną strzałką (nie ma to większego znaczenia, bo nasze nowe zapytanie będziemy edytować) nasz wybór zatwierdzamy przyciskiem Edytuj (zaznaczone zielonym prostokątem na rysunku nr 5).

rys. nr 5 — Nawigator

Czekamy aż nasze dane ze stycznia zostaną wczytane do Power Query. Otworzy nam się Edytor zapytań z wczytaną tabelą z danymi ze stycznia. Dane te zostały przedstawione na rysunku nr 6. Nie interesują nas na tym etapie te dane, a jedynie Zastosowane kroki (oznaczone na rysunku nr 6 zieloną strzałką). Usuwamy kroki Zmieniono typ i Nawigacja, a zostawiamy krok Źródło.

rys. nr 6 — Edytor zapytań

Otrzymamy dane przedstawione na rysunku nr 7.

rys. nr 7 — dane

Dzięki temu nie będziemy musieli sami wpisywać skomplikowanej formuły, tylko skorzystamy w wbudowanych funkcjonalności Power Query, czyli będziemy wprowadzać zmiany w istniejącej formule na pasku formuły. W naszych danych mamy podział na Kind, czyli rodzaje danych w pliku. Sheet to arkusze, Table to tabela, natomiast DefinedName to zdefiniowane zakresy danych. Rozwijamy ikonkę ze strzałką w tytule kolumny Kind i w podręcznym menu zaznaczamy tylko Sheet, bo interesują nas tylko arkusze (rys. nr 8). Podane parametry filtru zatwierdzamy klikając przycisk OK.

rys. nr 8 — Filtry

W naszych danych mamy jeszcze niepotrzebny ukryty arkusz, którego nie chcemy widzieć w naszych danych, więc klikamy na ikonkę strzałki w tytule kolumny Hidden i odznaczamy checkbox True (rys. nr 9). Nasz filtr zatwierdzamy klikając przycisk OK.

rys. nr 9 — Filtr

Z kolumny Item interesują nas dane tylko z konkretnych arkuszy : styczeń, luty i marzec. Chcemy odfiltrować dane (arkusze) domyślne, czyli takie które mają w sobie jakąś nazwę, w naszym przykładzie niepotrzebne arkusze mają w nazwie litery PQ. Klikamy na ikonkę ze strzałką w tytule kolumny Item, a następnie wybieramy opcje Filtr tekstu i Nie zawiera (rys. nr 10).

rys. nr 10 — Nie zawiera

Otworzy nam się okno Filtrowanie wierszy, gdzie wpisujemy w pole Nie zawiera zestaw liter PQ (miejsce zaznaczone strzałka na rysunku nr 11). Musimy tutaj pamiętać, że Power Query jest Case sensitive, czyli zwraca uwagę na wielkość liter. Nasz filtr zatwierdzamy klikając przycisk OK.

rys. nr 11 — Okno filtrowanie wierszy

Otrzymamy interesujące nas dane (arkusze) przedstawione na rysunku nr 12.

rys. nr 12 — Interesujące nas arkusze

Dane te z poszczególnych arkuszy znajdują się w kolumnie Data pod nazwą Table. Na tym etapie interesuje nas tylko kolumna Data, zawierająca tabelki z danymi. Klikamy prawym przyciskiem myszy na tytuł tej kolumny i z podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 13).

rys. nr 13 — Usuń inne kolumny

Otrzymamy jedną kolumnę Data, następnie klikamy na ikonkę ze strzałkami w jej nazwie i z podręcznego menu wybieramy polecenie Rozwiń. Nasze parametry zatwierdzamy przyciskiem OK (rys. nr 14).

rys. nr 14 — Rozwiń

Otrzymamy dane bez nagłówków, więc musimy wybrać polecenie Użyj pierwszego wiersza jako nagłówków z karty Narzędzia główne (rys. nr 15).

rys. nr 15 — Użyj pierwszego wiersza jako nagłówków

W danych mamy 96 wierszy. Nasze dane będą się prezentować następująco (rys. nr 16):

rys. nr 16

W kolejnym etapie wybieramy polecenie Grupowanie według z karty Narzędzia główne (rys. nr 17).

rys. nr 17 — Grupowanie według

Otworzy nam się okno Grupowanie według. Power Query automatycznie wstawi parametry grupowania, czyli Nazwisko i Imię (według czego ma grupować), bo mamy w danych dwie kolumny. W polu Nazwa nowej Kolumny automatycznie zostało wstawione Liczność (nie musimy zmieniać bo chodzi nam o policzenie ilości takich samych danych). W polu operacja wybieramy polecenie Zlicz wiersze. Pole Kolumna pozostaje w naszym przykładzie pusta (jest to pole do innych operacji). Ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 18).

rys. nr 18 — Okno grupowanie według

Po przefiltrowaniu danych zostało 75 wierszy. Z karty Narzędzia główne wybieramy polecenie Sortuj od Z do A (rys. 19).

rys. nr 19 — Sortuj od Z do A

Nasze dane posortowane od największej do najmniejszej pokazaliśmy na rysunku nr 20.

rys. nr 20 — Posortowane dane

Następnie z karty Narzędzia główne wybieramy polecenie Zamknij i załaduj do (punkt nr 2 na rysunku nr 21).

rys. nr 21 — Zamknij i załaduj do

Otworzy nam się w Excelu okno Ładowanie do, gdzie wybieramy sposób przedstawienia danych jako Tabela oraz miejsce załadowania danych – Istniejący arkusz komórka $A$1 (rys. nr 22). Ustawione parametry zatwierdzamy klikając przycisk Załaduj.

rys. nr 22 — Okno Ładowanie do

Otrzymamy dane w Excelu przedstawione na rysunku nr 23.

rys. nr 23 — Dane załadowane do Excela

Jeśli dodamy kolejny miesiąc do danych bazowych, to filtry w Power Query zadziałają prawidłowo i wstawią dodatkowe dane do tabeli w Excelu.


Właśnie dodałem mój kurs o Power BI Desktop firmy Microsoft na Udemy.com.
W związku z tym, możesz dostać ten kurs w promocyjnej Cenie Na Start za zaledwie 34,99 PLN.
To najniższa cena jaką mogę ustawić na platformie edukacyjnej Udemy!

Kurs Power BI Desktop to:
- Ponad 6 godziny nagrań wideo, które krok po kroku wprowadzają Cię w tajniki pobierania, łączenia i analizy danych, a na koniec ich wizualizacji.
- Pliki do pracy razem z filmami.
- Dożywotni dostęp.
- Elektroniczny certyfikat ukończenia

Spis treści kursu o PowerBI Desktop:

Kurs jest podzielony na 6 rozdziałów, które pozwolą Ci wejść w tematykę analizy i wizualizacji danych za pomocą odpowiednio stworzonych zapytań i relacji w PowerBI Desktop.

  1. Wstęp do aplikacji PowerBI Desktop i jej możliwości
  2. Tworzenie i modyfikowanie zapytań (pobieranie danych)
  3. Modelowanie danych w PowerBI Desktop
  4. Wizualizacja danych i tworzenie raportów
  5. Usługa internetowa
  6. PowerBI Pro — kilka słów o płatnej części usługi PowerBI

Wejdź na stronę kursu PowerBI Desktop i zobacz szczegóły kursu
oraz udostępnione do podglądu filmy,
żeby przekonać się czy to kurs dla Ciebie.