Power Query 43 — Ile ciasteczek było w paczkach

W dzisiejszym poście poznamy sposób jak policzyć komórki zawierające konkretny tekst na przykładzie ilości paczek zawierających ciasteczka. Przykładowe dane do tego zadania zostały przedstawione na rysunku nr 1.

rys. nr 1 — Przykładowe dane

Rozwiązanie tego zadania w Excelu zostało omówione w pytaniu od widzów, w filmie Excel — Jak policzyć komórki zawierające konkretny tekst — Widzowie #89 (https://www.youtube.com/watch?v=LghJGhKo9Gw). Pytanie od widzów polegało na znalezieniu formuły, aby policzyć komórki, w których dany produkt wystąpił więcej niż jeden raz.

W Power Query rozwiązanie tego zadania jest proste. W pierwszym kroku musimy zaczytać nasze dane bazowe do Power Query , w tym celu klikamy polecenie Z tabeli/zakresu z karty Dane (rys. nr 2).

rys. nr 2 — Z tabeli/zakresu

Otworzy nam się edytor zapytań z wczytaną tabelą tPaczki2 (rys. nr 3).

rys. nr 3 — Edytor zapytań

Kolumna Data jest nam niepotrzebna, więc możemy ją sobie usunąć. Zaznaczamy tą kolumnę i klikamy prawym przyciskiem myszy na jej nazwę, następnie z podręcznego menu wybieramy polecenie Usuń (rys. nr 4).

rys. nr 4 — Polecenie Usuń

Otrzymamy dane przedstawione na rysunku nr 5, zawierające kolumnę z nazwą sprzedawcy oraz zawartością paczki dla danego sprzedawcy.

rys. nr 5 — Dane po usunięciu kolumny

W Power Query zadanie wyznaczenia ilości występowania produktu ciasteczka jest bardzo łatwe. Zaznaczamy kolumnę Produkt, rozwijamy polecenie Podziel kolumny (punkt 2 na rys. nr 6) z karty Narzędzia główne, a następnie wybieramy polecenie Według ogranicznika (punkt 3 na rys. nr 6).

rys. nr 6 — Według ogranicznika

Otworzy nam się okno Dzielenia kolumny według ogranicznika, gdzie w polu Wybierz lub wprowadź ogranicznik z listy rozwijanej musimy wybrać niestandardowe, a następnie w polu niżej wpisać nasz ogranicznik, czyli przecinek i spację. Rozwijamy również opcje zaawansowane, aby podzielić dane na Wiersze, ponieważ jest to idealne rozwiązanie dla naszego przykładu. Tak przygotowane parametry podziału kolumny zatwierdzamy przyciskiem OK (rys. nr 7).

rys. nr 7 — Dzielenie według ogranicznika

Otrzymamy dane przedstawione na rysunku nr 8. W tych danych każda paczka zostaje podzielona na pojedyncze produkty.

rys. nr 8 — Podzielone dane

Power Query zmienił automatycznie typ danych, więc usuwamy krok Zmieniono typ z Zastosowanych kroków.

Na tym etapie zaznaczamy obie kolumny, a następnie wybieramy polecenie Grupowanie według z karty Narzędzia główne (rys. nr 9).

rys. nr 9 — Grupowanie według

Otworzy nam się okno Grupowania według, gdzie Power Query automatycznie wybrał zaawansowane grupowanie z obiema kolumnami. W polu nazwa nowej kolumny wpisujemy Ilość, wybieramy operację Zlicz wiersze. Tak przygotowane parametry zatwierdzamy przyciskiem OK (rys. nr 10).

rys. nr 10 — Parametry grupowania według

Otrzymamy dane przedstawione na rysunku nr 11.

rys. nr 11 — Pogrupowane dane

Otrzymaliśmy w kolumnie Ilość wynik grupowania, czyli ilość wystąpienia w danych konkretnych produktów. Tak przygotowane dane wystarczy załadować do Excela. Wybieramy polecenie Zamknij i załaduj do z karty Narzędzia główne (rys. nr 12).

rys. nr 12 — Zamknij i załaduj do

Otworzy nam się okno Importowania danych, gdzie musimy określić sposób wyświetlania danych – tabela oraz miejsce ich wstawienia – Istniejący arkusz i wskazać konkretną komórkę. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 13).

rys. nr 13 — Importowanie danych

Otrzymamy dane w Excelu przedstawione na rysunku nr 14.

rys. nr 14 — dane zaimportowane do Excela

Kiedy posortujemy dane w kolumnie Produkt, możemy zauważyć, że produkt ciasteczka występuje na liście tylko 3 razy (rys. nr 15).

rys. nr 15 — posortowane dane

Wynika to z tego, że jeden sprzedawca (Robert) nie sprzedał żadnych ciasteczek. Grupowanie Power Query działa podobnie jak grupowanie w tabelach przestawnych, czyli jeśli jakiegoś produktu jest zero, to nie pokaże się on na liście.

W Power Query jest również możliwość wypisania wszystkich sprzedawców wraz ze wszystkimi produktami, mimo że ich ilość wynosi 0, ale jest to skomplikowane zadanie dla bardziej zaawansowanych użytkowników.

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.

Power Query 42 — Dodawanie kolumn z wartościami null

W dzisiejszym poście nauczymy się dodawać kolumny z wartością null. Temat ten omówimy na podstawie przykładowych danych z rysunku nr 1.

przykładowe dane
rys. nr 1 — przykładowe dane

W Power Query pojawia się problem kiedy zaczytujemy dane z pustymi komórkami. Przykładowo w Excelu takiego problemu nie ma, kiedy dodamy wszystkie wartości z danego wiersza otrzymamy prawidłowy wynik (dla wiersza 10 w wyniku otrzymamy wartość 0). Możemy to sprawdzić korzystając ze skrótu klawiszowego Alt+= (suma po wierszach). Otrzymamy dane przedstawione na rysunku nr 2. Kiedy mamy w danych pustą komórkę, Excel traktuje ją jako zero.

pusta komórka traktowana jako zero
rys. nr 2 — pusta komórka traktowana jako zero

Dane z rysunku nr 1 zaczytujemy do Power Query, czyli wybieramy polecenie Z tabeli/zakresu z karty Dane (rys. nr 3).

Z tabeli/zakresu
rys. nr 3 — Z tabeli/zakresu

W Power Query pojawia się problem z pustymi komórkami, ponieważ dodatek ten zwraca uwagę na typy danych.

Otworzy nam się edytor zapytań z wczytaną tabelą tSprzedaż. Standardowo musimy zmienić format danych. Klikamy na ikonkę kalendarza przy nazwie pierwszej kolumny i z podręcznego menu wybieramy format Data (rys. nr 4).

zmiana typu danych
rys. nr 4 — zmiana typu danych

Pojawi nam się komunikat o zmianie typu kolumny, którym musimy zatwierdzić przyciskiem Zamień bieżącą (rys. nr 5).

Zmień typ kolumny
rys. nr 5 — Zmień typ kolumny

Naszym zadaniem w Power Query jest dodanie do siebie trzech kolumn: Jabłek, Gruszek i Śliwek. Możemy to zrobić na różne sposoby. W pierwszym przykładzie dodamy kolumnę niestandardową. Wybieramy polecenie kolumna niestandardowa z karty Dodaj kolumnę (rys. nr 6).

kolumna niestandardowa
rys. nr 6 — kolumna niestandardowa

Otworzy nam się okno Kolumny niestandardowej, w którym musimy określić jej parametry. W polu nazwa nowej kolumny wpiszemy Suma, a następnie w polu Formuła kolumny niestandardowej wpiszemy następującą formułę:

=[Jabłka]+[Gruszki]+[Śliwki]

Tak przygotowane parametry zatwierdzamy przyciskiem OK (rys. nr 7).

okno kolumny niestandardowej
rys. nr 7 — okno kolumny niestandardowej

Otrzymamy dane przedstawione na rysunku nr 8, gdzie od razu można zauważyć błędy. W każdym wierszu, w którym chodź raz pojawiła się wartość null, otrzymaliśmy tę właśnie wartość w wyniku (rys. nr 8).

dane z kolumną niestandardową
rys. nr 8 — dane z kolumną niestandardową

Taki wynik jest nie do zaakceptowania, ale możemy sobie z tym poradzić. Usuwamy ostatni krok z Zastosowanych kroków, czyli stworzoną kolumnę niestandardową.

Zaznaczamy tylko dwie kolumny (Gruszki i Jabłka), a następnie rozwijamy polecenie Standardowy (punkt 2 na rys. nr 9) z karty Dodaj kolumnę i wybieramy polecenie Dodaj (punkt 3 na rys. nr 9).

polecenie Dodaj
rys. nr 9 — polecenie Dodaj

Po raz kolejny z takiego dodawania w każdym wierszu, w którym wartość null, otrzymujemy taką właśnie wartość (rys. nr 10). Zapis formuły wygląda następująco:

=Table.AddColumn(#"Zmieniono typ", "Dodawanie" each [Jabłka] + [Gruszki], Int64.Type)

działanie polecenia Dodaj dla 2 kolumn
rys. nr 10 — działanie polecenia Dodaj dla 2 kolumn

Tym razem zaznaczamy wszystkie trzy kolumny z produktami i korzystamy z tego samego polecenia co poprzednio, czyli z polecenia Dodaj. Otrzymujemy dane przedstawione na rysunku nr 11.

działanie polecenia Dodaj dla 3 kolumn
rys. nr 11 — działanie polecenia Dodaj dla 3 kolumn

Możemy łatwo zauważyć, że otrzymane wyniki są prawidłowe. Wartość null otrzymaliśmy tylko w wierszu, w którym wszystkie wartości wynosiły null.

Dzieje się tak dlatego, że zamiast zwykłego dodawania Power Query skorzystał z jednej ze swoich funkcji – List.Sum (rys. nr 12).

funkcja List.Sum
rys. nr 12 funkcja List.Sum

Funkcja List.Sum sumuje listę elementów. Funkcja ta w inny sposób zapisuje argumenty. Poprzednio nazwy kolumn były zapisane tylko w nawiasach kwadratowych oraz dodane za pomocą znaku plus. W tej funkcji dane są zapisane dodatkowo w nawiasach klamrowych, które oznaczają, że jest to lista elementów, ponadto elementy są oddzielone od siebie przecinkami (rys. nr 13). Dzięki temu Power Query radzi sobie z wartościami null.

dodatkowe nawiasy klamrowe w zapisie funkcji List.Sum
rys. nr 13 — dodatkowe nawiasy klamrowe w zapisie funkcji List.Sum

Co ważne, przy dwóch dodawanych kolumnach, formuła zadziałała jak zwykłe dodawanie (zapis ze znakami +). Nawet przy tak łatwych obliczeniach w Power Query mogą pojawić się różnice, a tym samym błędne wyniki.

Możemy zmodyfikować formułę dla dodawania dwóch kolumn, wstawić funkcję List.Sum, plusy zastąpić przecinkami oraz dołożyć nawiasy klamrowe. Zapis formuły wyglądać będzie następująco:

=Table.AddColumn(#"Zmieniono typ", "Dodawanie" each List.Sum({[Jabłka],[Gruszki]}), Int64.Type)

Otrzymamy prawidłowe wyniki dla dodawania dwóch kolumn przedstawione na rysunku nr 14.

Dodane dane
rys. nr 14 — Dodane dane

Podsumowując musimy pamiętać jak radzi sobie Power Query z danymi, w których występują wartości null, aby nie popełnić błędów w obliczeniach.


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.