W dzisiejszym poście nauczymy jak w Power Query znaleźć pary produktów, które wystąpiły razem w danych. Nie ma znaczenia czy będziemy bazować na Id produktu czy na jego nazwie. Załóżmy, że mamy listę zakupów z produktami oznaczonymi numerami Id. Ręczne wyszukiwanie par jest pracochłonne i męczące. Rozwiązanie tego problemu omówimy na podstawie przykładowych danych przedstawionych na rysunku nr 1.

rys. nr 1 — Przykładowe dane

W pierwszym kroku musimy wczytać nasze dane do Power Query, w tym celu wybieramy polecenie Z tabeli (punkt nr 2 na rysunku nr 2) z karty Dane.

rys. nr 2 — Z tabeli

Otworzy nam się Edytor zapytań z wczytaną tabelą tIdProduktu. W polu Zastosowane kroki usuwamy krok Zmiana typu, ponieważ nie jest nam potrzebny. Otrzymamy dane przedstawione na rysunku nr 3.

rys. nr 3 — edytor zapytań

W kolejnym etapie musimy zduplikować tą kolumnę. Wybieramy polecenie Duplikuj kolumnę (punkt nr 2 na rysunku nr 4) z karty Dodaj kolumnę.

rys. nr 4 — duplikuj kolumnę

Następnie dokładamy jeszcze kolumnę z indeksem, żebyśmy widzieli które to były zakupy. Wybieramy polecenie Kolumna indeksu (punkt nr 2 na rysunku nr 5) a następnie od 01 (punkt nr 3 na rysunku nr 5) z karty Dodaj kolumnę.

rys.. nr 5 — Dodaj kolumnę indeksu

Aby w Power Query znaleźć pary produktów musimy podzielić dane po ogranicznikach a następnie odpowiednie dane połączyć. Wybieramy polecenie Podziel kolumny (punkt nr 2 na rysunku nr 6) z karty Narzędzia główne, a następnie polecenie Według ogranicznika (punkt nr 3 na rysunku nr 6).

rys. nr 6 — Podziel kolumny według ogranicznika

Otworzy nam się okno Dzielenia kolumny według ogranicznika, gdzie musimy określić parametry tego dzielenia. W polu Wybierz lub wprowadź ogranicznik wybieramy Niestandardowy (punkt nr 1 na rysunku nr 7), a następnie wpisujemy użyty u nas ogranicznik, czyli przecinek i spacja. W opcji Podziel przy wybieramy checkbox przy Każdym wystąpieniu ogranicznika (punkt nr 2 na rysunku nr 7). Natomiast w Opcjach zaawansowanych Podziel na (punkt nr 3 na rysunku nr 7) zaznaczamy checkbox przy Wierszach. Tak dobrane ustawienia zatwierdzamy przyciskiem OK.

rys. nr 7 — Dzielenie kolumny według ogranicznika

Otrzymamy dane, których fragment został przedstawiony na rysunku nr 8.

rys. nr 8 Podzielone dane

Power Query przy okazji dzielenia kolumny zamienił nam dane na liczby. Nie ma to większego znaczenia. Analogicznie postępujemy dla drugiej kolumny (kopii pierwszej). Wybieramy polecenie Podziel kolumny, a następnie według ogranicznika z karty Narzędzia główne (jak na rysunku nr 6). Pojawi się nam okno Dzielenia kolumny według ogranicznika, w której ustawiamy takie same parametry jak dla pierwszej kolumny (jak na rysunku nr 7) i wybrane parametry zatwierdzamy klikając przycisk OK. Otrzymamy dane przedstawione na rysunku nr 9.

rys. nr 9 — takie same wartości w dwóch kolumnach

W danych tych pojawia się problem – mamy zduplikowane pary (zaznaczone na rysunku nr 9 zielonym prostokątem). Można łatwo rozwiązać ten problem, ponieważ interesuje nas sytuacja, kiedy danej z kolumny pierwszej są mniejsze od danych z kolumny drugiej. Wystarczy dodać kolumnę warunkową z karty Dodaj kolumnę (rys. nr 10)

rys. nr 10 — Dodaj kolumnę warunkową

Otworzy nam się okno Dodawania kolumny warunkowej, gdzie musimy określić Nazwę kolumny – Pary (punkt nr 1 na rysunku nr 11), Następnie wypełnić warunek w punkcie 2 na rysunku nr 11. Ważne jest, że jeśli warunek zostanie spełniony to chcemy otrzymać wartość z pierwszej kolumny, natomiast jeśli nie zostanie spełniony chcemy otrzymać wartość null (punkt nr 3 na rysunku nr 11). Tak dobrane parametry kolumny warunkowej zatwierdzamy przyciskiem OK.

rys. nr 11 — okno dodawania kolumny warunkowej

Konkretnie chcemy otrzymać wartość z pierwszej kolumny tylko wtedy kiedy wartość z pierwszej kolumny jest mniejsza od wartości z drugiej kolumny. Otrzymamy dane przedstawione na rysunku nr 12. Nie jest to jeszcze wynik docelowy, bo chcemy otrzymać parę, ale ważne jest, że para pokazuje się dla jednej sytuacji w pierwszym zakupie.

rys. nr 12 — Dane z kolumną warunkową

Aby otrzymać jako wynik parę, musimy wprowadzić zmiany w zapisie formuły w pasku formuły. Dołożyć z ogranicznikiem wartość z kolumny drugiej. Zapis formuły został przedstawiony na rysunku nr 13 (zieloną strzałką zaznaczono wprowadzoną zmianę). Wprowadzoną zmianę zatwierdzamy przyciskiem Enter.


rys. nr 13 — zapis formuły

W wyniku otrzymamy błąd ponieważ nasze dane zostały zmienione na liczby. Aby obejść ten problem musielibyśmy użyć funkcji Number.ToText. My użyjemy łatwiejszego sposobu – z zastosowanych kroków usuniemy krok zmiany typu danych z tekstu na liczby – Zmieniono typ i Zmieniono typ1 (rys. nr 14).

rys. nr 14 — usunięcie kroków

Otrzymamy wtedy prawidłowe dane przedstawione na rysunku nr 15.

rys. nr 15 — Dane w formie tekstowej

Pozostaje nam pogrupować te produkty po ilości, użyjemy do tego polecenia Grupowanie według (punkt nr 2 na rysunku nr 16) z karty Narzędzia główne.

rys. nr 16 — Grupowanie według

Otworzy się nam okno Grupowania według, w którym musimy określić nazwę nowej kolumny i rodzaj operacji jaką chcemy wykonać. Wybrane parametry zatwierdzamy przyciskiem OK (rys. nr 17).

rys. nr 17 — Okno Grupowania według

Otrzymamy dane przedstawione na rysunku nr 18.

rys. nr 18 — pogrupowane dane

W danych tych wiele razy powtórzyła nam się wartość null, więc wystarczy przefiltrować dane, żeby odfiltrować wyniki dla null. Klikamy na ikonkę trójkącika w nazwie kolumny Pary i odznaczamy w filtrach null (rys. nr 19).

rys. nr 19 — Odfiltrowanie danych dla null

W kolejnym etapie chcemy posortować dane malejąco w obu kolumnach. Korzystamy z podręcznego menu po naciśnięciu ikonki trójkącika w tytule kolumn, gdzie wybieramy polecenie Sortuj malejąco (rys. nr 20).

rys. nr 20 — Sortuj malejąco

Otrzymamy końcowe dane, które możemy załadować do Excela. Korzystamy z polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 21).

rys. nr 21 — Zamknij i załaduj do

W Excelu otworzy nam się okno Ładowania do, gdzie ustawiamy parametry wstawianych danych. Sposób wyświetlania danych jako tabela i miejsce wstawienia danych jako istniejący arkusz i wskazujemy konkretną komórkę. Powyższe parametry zatwierdzamy przyciskiem Załaduj (rys. nr 22).

rys. nr 22 — Okno Ładowanie do

Otrzymamy dane wstawione do Excela przedstawione na rysunku nr 23.

rys. nr 23 — dane wczytane do Excela

Książka Mistrz Excela + promo na 35 urodziny

Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.

Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".

Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.

Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.

W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY

Książka Mistrz Excela reklama