Power Query #28 — Pary produktów, które wystąpiły razem

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

Przedsprzedaż Mistrz Excela II wydanie

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 przedsprzedaży możesz mieć Mistrza Excela w obniżonej cenie:
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 przedsprzedaży możesz mieć całą książkę za jedyne 30 zł (łącznie z darmową wysyłką listem ekonomicznym), a jeśli zależy Ci na dedykacji wystarczy, że dołożysz 10 zł.

Książka Mistrz Excela reklama

Power Query #29 — Jak połączyć listy osób zapisanych na różne kursy

W dzisiejszym poście nauczymy się jak połączyć listy osób zapisanych na różne kursy za pomocą Power Query – dodatku do Excela. W przykładowych danych do tego zagadnienia przedstawionych na rysunku nr 1 mamy podane trzy listy adresów mailowych związanych z różnymi kursami. Naszym zadaniem jest połączenie tych list i stworzenie skumulowanej tabeli z adresami mailowymi z zaznaczonymi kursami, na jaki właściciel danego maila się zapisał.

rys. nr 1 — Przykładowe dane

Nasze zadanie ma również na celu to, żeby konkretna osoba która zapisała się na wszystkie trzy kursy w naszej tabeli występowała jeden raz (np. Andrzej@gazeta.pl zapisał się na trzy kursy).

Pierwszym krokiem jest zaczytanie naszych danych bazowych, w tym celu zaznaczamy dowolną komórkę w pierwszej tabeli i wybieramy polecenie Z tabeli (punkt nr 2 na rysunku nr 2) z karty Dane. Ułatwieniem tutaj jest, że tabele wejściowe zostały nazwane Tabela1, Tabela2 i Tabela3.

rys. nr 2 — Z tabeli

Otworzy nam się Edytor zapytań z wczytaną tabelą Tabela1 (rys. nr 3). Z Zastosowanych kroków (Ustawienia zapytania) usuwamy krok Zmieniono typ, ponieważ jest on niepotrzebny.

rys. nr 3 — Edytor zapytań

Pozostałe tabele możemy wczytać w łatwy sposób, wystarczy że zduplikujemy sobie Zapytanie. Klikamy prawym przyciskiem myszy na nazwę Zapytanie1 i z podręcznego menu wybieramy polecenie Duplikuj (rys. nr 4). Czynność tę powtarzamy dwa razy ponieważ potrzebujemy trzech tabel.

rys. nr 4 — Duplikuj zapytanie

Zaznaczamy sobie zapytanie Tabela1 (2) i w pasku formuły zmieniamy nazwę Tabela1 na Tabela2 (oznaczone zieloną strzałką na rysunku nr 5). Wprowadzoną zmianę zatwierdzamy przyciskiem Enter.

rys. nr 5 — Zmiany w pasku formuły

Otrzymamy zapytanie Tabela1 (2) z wczytaną drugą tabelą z danymi (osoby zapisane na kurs Dashboardy) przedstawione na rysunku nr 6. Po raz kolejny usuwamy niepotrzebny krok Zmieniono typ z Zastosowanych kroków.

rys. nr 6 — Połączone zapytania

Analogicznie wygląda sytuacja dla trzeciego zapytania. Klikamy na nazwę zapytania Tabela1 (3) i w pasku formuły zmieniamy nazwę Tabela1 na Tabela3. Wprowadzoną zmianę zatwierdzamy przyciskiem Enter. Otrzymamy trzecie zapytanie z wczytanymi danymi z tabeli numer 3 przedstawionymi na rysunku nr 7. Po raz kolejny usuwamy niepotrzebny krok Zmieniono typ z Zastosowanych kroków.

rys. nr 7 — Dane z trzech tabel

Kolejnym krokiem jest zmiana nazwy zapytań nr 2 i 3 we Właściwościach w panelu bocznym z Ustawieniami zapytania (oznaczone zieloną strzałką na rysunku nr 8).

rys. nr 8 — Zmiana nazwy zapytania

Kiedy już mamy zaczytane wszystkie trzy tabele, możemy je połączyć. Rozwijamy polecenie Połącz (punkt nr 2 na rysunku nr 9) z karty Narzędzia główne i wybieramy polecenie Dołącz zapytania (punkt nr 3 na rysunku nr 9).

rys. nr 9 — Dołącz zapytania

Pojawi nam się okno Dołączanie, gdzie zaznaczamy checkbox przy opcji Co najmniej trzy tabele (punkt nr 1 na rysunku nr 10). Następnie dodajemy Tabelę2 i Tabelę3 z pola Dostępne tabele do pola Tabele do dołączenia za pomocą przycisku Dodaj (punkt nr 2 na rysunku nr 10). Dołączone tabele zatwierdzamy przyciskiem OK.

rys. nr 10 — Okno Dołączania zapytań

Otrzymamy jedną tabelę ze skumulowanymi danymi ze wszystkich trzech tabel (rys. nr 11).

rys. nr 11 — Skumulowane dane

Mimo że dane zostały dołączone to nadal osoby które zapisały się na większą ilość kursów są wyświetlane osobno, a nam chodzi o to by takie osoby wyświetlane były w tabeli tylko raz. W tym celu musimy anulować przestawienie innych kolumn poza kolumną Mail. Klikamy prawym przyciskiem myszy na tytuł kolumny Mail i z podręcznego menu wybieramy polecenie Anuluj przestawienie innych kolumn (rys. nr 12).

rys. nr 12 — Anuluj przestawienie innych kolumn

Otrzymamy dane przedstawione na rysunku nr 13, gdzie stworzą nam się nowe kolumny. Kolumna Atrybuty będzie zawierała nazwy kolumn, natomiast kolumna Wartość będzie zawierała wartości jakie były przypisane do odpowiednich adresów mailowych w tych kolumnach.

rys. nr 13 — Dane z nowymi kolumnami

Najważniejszy efekt działania tego polecenia to pozbycie się z danych wartości null. W kolejnym etapie musimy przekształcić jedną z tych kolumn (Atrybuty bądź Wartość), zrobimy operację odwrotną. Z karty Przekształć wybieramy polecenie Kolumna przestawna (punkt nr 2 na rysunku nr 14).

rys. nr 14 — Kolumna przestawna

Otworzy nam się okno Tabeli przestawnej, gdzie stworzymy nowe kolumny przy użyciu kolumny Atrybyt, czyli powstaną kolumny z nazwami VBA, Dashboardy i Excel (rys. nr 15). Odpowiednio w każdej z tych kolumn będzie wpisana wartość z kolumny Wartość (punkt nr 1 na rysunku nr 15). Ponadto musimy wybrać w Opcjach zaawansowanych wybieramy w jaki sposób będą agregowane nasze dane – Nie agreguj (punkt nr 2 na rysunku nr 15). Wybrane parametry zatwierdzamy przyciskiem OK.

rys. nr 15 — Okno kolumny przestawnej

Otrzymamy dane przedstawione na rysunku nr 16, gdzie mamy już skumulowane dane. Przykładowo właściciel maila Andrzej@gazeta.pl występuje tylko raz na liście mimo że zapisał się aż na trzy kursy.

rys. nr 16 — Skumulowane dane

Tak przygotowane dane możemy załadować do Excela za pomocą polecenia Zamknij i załaduj (punkt nr 2 na rysunku nr 17) z karty Narzędzia główne.

rys. nr 17 Zamknij i załaduj

Ponieważ jednocześnie pobieraliśmy dane z trzech tabel, to utworzyło nam się w Excelu tylko połączenie. Dane te będą widoczne w panelu bocznym Zapytania. Gdyby nie były widoczne należy wybrać polecenie Pokaż zapytania z karty Dane (rys. nr 18).

rys. nr 19 — Pokaż zapytania

Interesuje nas zapytanie Tabela1, bo to w tym zapytaniu połączyliśmy wszystkie dane (rys. nr 19).

rys. nr 19 — Zapytanie Tabela1

Klikamy prawym przyciskiem myszy na nazwę zapytania Tabela1 i z podręcznego menu wybieramy polecenie Załaduj do (rys. nr 20).

rys. nr 20 — Załaduj do

Otworzy nam się okno Ładowania do, gdzie wybieramy sposób przedstawienia załadowanych danych jako Tabela, a następnie lokalizację tych danych jako Istniejący arkusz i wskazujemy konkretną komórkę (rys. nr 21). Powyższe parametry zatwierdzamy przyciskiem Załaduj.

rys. nr 21 — Okno Ładowanie do

Otrzymamy dane załadowane do Excela przedstawione na rysunku nr 22.

rys. nr 22 — Dane w Excelu

Przy tworzeniu danych został zrobiony błąd, którego nie zauważyliśmy. W Excelu w oknie Zapytania dotyczące skoroszytu jest on wyszczególniony (rys. nr 23).

rys. nr 23 — Błąd w zapytaniu

Po kliknięciu na ten błąd, otworzy nam się edytor zapytań z wczytanym błędem w zapytaniu Tabela1 (rys. nr 24).

rys. nr 24 — Edytor zapytań z błędem

W panelu bocznym Ustawienia zapytania klikamy na Źródło w Zastosowanych krokach. Mamy podany numer wiersza w którym był błąd, więc możemy go łatwo znaleźć. Kiedy klikniemy na ten wiersz wyświetli nam się pod tabelą informacja o tym błędzie (rys. nr 25).

rys. nr 25 — Informacja o błędzie

Błąd ten dotyczy maila Sebastian@yahoo.com, który wystąpił w danych źródłowych dwa razy (dla tego samego kursu) co widać na rysunku nr 26.

rys. nr 26 — Miejsce błędu

Musimy poprawić te dane W Excelu i usunąć powtarzający się adres mailowy, następnie klikamy prawym przyciskiem myszy na dowolną komórkę w danych z zapytania i z podręcznego menu wybieramy polecenie Odśwież (rys. nr 27).

rys. nr 27 — Odśwież

Otrzymamy prawidłowe dane przedstawione na rysunku nr 28.

rys. nr 28 — Prawidłowe dane

Przedsprzedaż Mistrz Excela II wydanie

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 przedsprzedaży możesz mieć Mistrza Excela w obniżonej cenie:
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 przedsprzedaży możesz mieć całą książkę za jedyne 30 zł (łącznie z darmową wysyłką listem ekonomicznym), a jeśli zależy Ci na dedykacji wystarczy, że dołożysz 10 zł.

Książka Mistrz Excela reklama

Power Query #27 — Usuń wybrane kolumny VS usuń inne kolumny

W dzisiejszym poście omówimy różnice pomiędzy poleceniami Usuń inne kolumny a Usuń wybrane kolumny w Power Query. Tak naprawdę będą to różnice pomiędzy funkcjami, które kryją się pod tymi poleceniami, czyli odpowiednio Table.RemoveColumns oraz Table.SelectColumns. Zagadnienie to omówimy na podstawie przykładowych danych z rysunku nr 1.

rys. nr 1 — Przykładowe dane

Aby obrazowo wyjaśnić temat – chodzi o to, że mamy za dużo kolumn z danymi i po prostu część z nich chcemy usunąć lub chcemy usunąć wszystkie oprócz zaznaczonych. Mamy tabelę danych w Excelu, zaznaczamy w jej obszarze dowolną komórkę, a następnie wybieramy polecenie Z tabeli (punkt nr 2 na rysunku nr 2) z karty Dane.

rys. nr 2 — Z tabeli

Otworzy nam się edytor zapytań w Power Query z wczytaną tabelą tSprzedaż (rys. nr 3).

rys. nr 3 — Edytor zapytań

Przede wszystkim, aby dane dobrze się prezentowały musimy zmienić format danych z kolumny Data. Klikamy na ikonkę po lewej stronie nazwy kolumny Data a następnie z podręcznego menu wybieramy polecenie Data (rys. nr 4).

rys. nr 4 — Zmiana typu wyświetlania danych

Wyświetli się nam komunikat dotyczący zmiany typu kolumny, w którym musimy zatwierdzić zmianę klikając przycisk Zamień bieżącą (rys. nr 5).

rys. nr 5 — Komunikat o zmianie typu kolumny

Zakładamy, że w naszym przypadku interesują nas trzy kolumny: Data, Sprzedawca i Zysk. Zaznaczamy pozostałe kolumny (od kolumny Region do kolumny KWS) i klikamy prawym przyciskiem myszy na nagłówek dowolnej kolumny z tych zaznaczonych i z podręcznego menu wybieramy polecenie Usuń kolumny (rys. nr 6)

rys. nr 6 — Usuń kolumny

Otrzymamy interesujące nas kolumny (rys. nr 7). W pasku formuły pokaże się nam funkcja Table.RemoveColumns odpowiadająca poleceniu Usuń kolumny.

rys. nr 7 funkcja Table.RemoveColumns

Jeśli nie mamy widocznego paska formuły możemy to łatwo zmienić, klikając checkbox przy poleceniu Pasek formuły na karcie Widok (zaznaczone na rysunku nr 8).

rys. nr 8 — Pasek formuły

Na pasku formuły dla funkcji Table.RemoveColumns w formule mamy wypisane nazwy kolumn, które usunęliśmy (rys. nr 9).

rys. nr 9 — Argumenty funkcji Table.RemoveColumns

W Power Query w formułach funkcji są podawane nazwy kolumn. Wystarczy, że nazwa kolumny ulegnie zmianie i pojawiają się błędy.

Możemy usunąć ostatni krok z Zastosowanych kroków, aby wrócić do danych wejściowych. Na danych wejściowych pokażemy bowiem drugie polecenie, które moim zdaniem lepiej się sprawdza, czyli polecenie Usuń inne kolumny. Zaznaczamy kolumny, które nas interesują (które chcemy zostawić). Zaznaczamy w pierwszej kolejności kolumnę Sprzedawca, następnie przytrzymując klawisz Ctrl Kolumnę Data i Zysk. Klikamy na tytuł którejś z tych zaznaczonych kolumn i z podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 10).

rys. nr 10 — Usuń inne kolumny

Po użyciu tego polecenia zostały usunięte kolumny, które nie były zaznaczone (rys. nr 11).

rys. nr 11 — Dane po usunięciu kolumn

W pasku formuły pojawi się zapis funkcji Table.SelectColumns, w której mamy wypisane nazwy kolumn, które nas interesują (rys. nr 12). Przy tej funkcji nie interesuje nas, że ktoś dołoży kolejną kolumnę do danych wejściowych. Nowa kolumna nie zostanie tutaj uwzględniona i nasze końcowe dane się nie zmienią.

rys. nr 12 — Zapis funkcji Table.SelectColumns

Bez względu na zmianę w danych wejściowych ilość wyświetlanych kolumn się nie zmieni. Kolejnym plusem jest to, że kolumny są wyświetlane w takiej kolejności w jakiej je zaznaczyliśmy przed użyciem polecenia Usuń inne kolumny. Możemy również ręcznie zmienić kolejność wyświetlania kolumn w kodzie w pasku formuły.

W Power Query, jeśli znamy nazwę interesującej nas funkcji i wpiszemy ją w pasku formuły a następnie zatwierdzimy przyciskiem Enter, to wyświetlą nam się informacje o tej funkcji z oficjalnej strony Microsoftu (rys. nr 13).

Możemy się tutaj dowiedzieć, że funkcja ta posiada dodatkowy parametr missingField, który może przyjąć jedną z trzech wartości. Będzie się wyświetlał błąd jeśli podaliśmy złą nazwę kolumny lub zignoruje ten brak albo wypełni je wartościami null. Poniżej mamy także podane przykłady działania tej funkcji.

Zrobimy przykład, w którym dopiszemy do formuły MissingField.UseNull i zmienimy nazwy kolumn Data i Zysk na Data2 i Zysk2, czyli na takie które nie występują w danych. Power Query wypełni je wartościami null (rys. nr 14).

rys. nr 14 — Dane wypełnione wartościami null

Jeśli do zapisu formuły użyjemy MissingField.Ignore to zostanie nam tylko kolumna ze Sprzedawcami. Jeśli nie podamy tego parametru, to Power Query domyślnie wybierze wyświetlenie błędu (rys. nr 15).

rys. nr 15 — Domyślny komunikat jeśli nie podamy parametru MissingField

Ja wole funkcje Table.SelectColumns czyli usunięcie innych kolumn a pozostawienie tych, które są mi potrzebne. Przede wszystkim dlatego, że dodawanie innych kolumn nie wpłynie na wynik tej funkcji. Łatwiej jest korzystać z tych domyślnych opcji na pasku narzędzi niż z kodu w pasku formuły, ponieważ przy słabej znajomości języka M w Power Query możemy nie wiedzieć jak wpisać poszczególne parametry danej funkcji.

Wynik tej funkcji z rysunku nr 11 możemy sobie załadować do Excela za pomocą polecenia Zamknij i załaduj z karty Narzędzia główne (rys. nr 16).

rys. nr 16 — Zamknij i załaduj

Otrzymamy dane wczytane do Excela przedstawione na rysunku nr 17.

rys. nr 17 — Dane wczytane do Excela

Przedsprzedaż Mistrz Excela II wydanie

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 przedsprzedaży możesz mieć Mistrza Excela w obniżonej cenie:
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 przedsprzedaży możesz mieć całą książkę za jedyne 30 zł (łącznie z darmową wysyłką listem ekonomicznym), a jeśli zależy Ci na dedykacji wystarczy, że dołożysz 10 zł.

Książka Mistrz Excela reklama

Power Query #26 — Zamień wszystkie x w tabeli na nagłówki kolumn

W dzisiejszym poście nauczymy się jak zamienić wszystkie x w tabeli na nagłówki kolumn. Jest to zadanie zaczerpnięte od Oza du Soleila https://www.youtube.com/watch?v=abQrUE10Xqo , on rozwiązał to za pomocą kilku zapytań. Nasze zadanie ma na celu podstawienie w miejsce x w tabeli odpowiednich nagłówków. Przykładowe dane i cel do którego dążymy został przedstawiony na rysunku nr 1.

rys. nr 1 — Dane i cel do którego dążymy

Na przykład chcemy aby zamiast znaku x dla Scoobiego zostało wstawione Jedzenie (czyli nagłówek kolumny, w której został wstawiony ten znak x). Pokażemy jak to zrobić w Power Query za pomocą tylko jednego zapytania. Załóżmy, że mamy przykładową tabelkę z danymi przedstawioną na rysunku nr 2.

rys. nr 2 — Przykładowe dane

Z karty Dane wybieramy polecenie Z tabeli (punkt nr 2 na rysunku nr 3).

rys. nr 3 — Z tabeli

Otworzy nam się edytor zapytań tCechy z wczytaną tabela z Excela (rys. nr 4). W pierwszym etapie chcemy usunąć krok Zmieniono tym z Zastosowanych kroków, bo nie jest nam on potrzebny.

rys. nr 4 — Edytor zapytań

W naszych wczytanych danych jest jedna postać, która w każdej kolumnie ma Null, czyli nie ma w żadnej kolumnie znaku x. to był największy problem, żeby rozwiązać to zadanie za pomocą jednego zapytania. Klikamy na tytuł komuny Imię prawym przyciskiem myszy i z podręcznego menu wybieramy polecenie Anuluj przestawienie innych kolumn (rys. nr 5).

rys. nr 5 — Anuluj przestawienie innych kolumn

W efekcie otrzymamy tabelkę z tylko tymi osobami gdzie był znak x (rys. nr 6). Nie została uwzględniona tutaj postać Duch, u której nie było w ogóle tego znaku.

rys. nr 6 — tabela z danymi

Wynika z tego, że nie możemy użyć tego polecenia. Potrzebujemy sposobu na uwzględnienie osoby, która nie zaznaczyła żadnego znaku x (czyli np. nie wybrała żadnego kursu z naszej oferty). Usuwamy z Zastosowanych kroków ostatni etap, ponieważ zwracał niekompletne dane. Można to zrobić za pomocą Kolumny indeksu. Z karty Dodaj kolumnę wybieramy polecenie Kolumna indeksu (punkt nr 2 na rysunku na 7), a następnie od 0 lub 1 (nie ma to znaczenia, ponieważ jest to kolumna pomocnicza, dzięki której osoba Duch nie zniknie z danych, ponieważ będzie miała przypisaną jakąś wartość).

rys. nr 7 — Kolumna indeksu

Otrzymamy dane przedstawione na rysunku nr 8.

rys. nr 8 — Dane z nową kolumną

Na tym etapie po raz kolejny wybieramy polecenie z podręcznego menu Anuluj przestawienie innych kolumn (identycznie jak na rysunku nr 5 wyżej). Otrzymamy dane przedstawione na rysunku nr 9.

rys. nr 9 — Dane

Kolumna z Wartościami jest nam nie potrzebna, natomiast potrzebujemy drugiej kolumny z Atrybutami. Klikamy prawym przyciskiem myszy na nagłówek kolumny Atrybut i z podręcznego menu wybieramy polecenie Duplikuj kolumnę (rys. nr 10).

rys. nr 10 — Duplikuj kolumnę

W kolejnym etapie usuwamy kolumnę z wartościami. Klikamy na jej nagłówek prawym przyciskiem myszy i z podręcznego menu wybieramy polecenie Usuń (rys. nr 11).

rys. nr 11 — Usuń

Otrzymamy dane przedstawione na rysunku nr 12.

rys. nr 12

Mamy dwie kolumny z takimi samymi wartościami, część z tych danych ma posłużyć jako nagłówki kolumn a część jako wartości. Aby tak się stało musimy znaleźć na karcie Przekształć ikonkę Kolumny przestawnej (punkt nr 2 na rysunku nr 13). Ważne jest aby aktywna (zaznaczona) była kolumna Atrybut.

rys. nr 13 — Kolumna przestawna

Pojawi się okno Kolumny przestawnej (rys. nr 14). Polecenie to utworzy nowe kolumny przy użyciu danych z kolumny Atrybut. Jeśli jakaś nazwa się powtórzy to kolumna pozostanie pojedyncza, nie zostanie zduplikowana np. dla danej Jedzenie. W polu Kolumna wartości (punkt nr 1 na rysunku nr 14) musimy wybrać daną, jaka ma być przechowywana w tej nowej kolumnie. Tę wartość chcemy wziąć z kolumny Atrybut – kopia. Nasza kolumna z imionami natomiast zostanie na miejscu. Ważne jest tutaj użycie opcji zaawansowanych – w polu Agreguj funkcje wartości (punkt nr 2) wybieramy polecenie Nie agreguj, ponieważ nie chcemy tutaj wykonywać żadnych obliczeń. Tak ustawione parametry  kolumny przestawnej zatwierdzamy klikając przycisk OK.

rys. nr 14 — Okno kolumny przestawnej

Otrzymamy dane przedstawione na rysunku nr 15.

rys. nr 15 — Dane z kolumną przestawną

W kolejnym etapie musimy usunąć kolumnę indeks, która była kolumną pomocniczą. Klikamy prawym przyciskiem myszy na nagłówek kolumny Indeks i z podręcznego menu wybieramy polecenie Usuń. Otrzymamy wynik końcowy, o który nam chodziło (rys. nr 16).

Rys nr 16 — Ostateczny wynik

Następnie wybieramy polecenie Zamknij i załaduj do z karty Narzędzia główne (rys. nr 17).

rys. nr 17 — Zamknij i załaduj do

Otworzy nam się okno Ładowanie do, gdzie wybieramy rodzaj wyświetlania danych jako Tabela i miejsce ich wstawienia jako istniejący arkusz i konkretną komórkę. Powyższe parametry zatwierdzamy klikając przycisk Załaduj (rys. nr 18).

rys. nr 18 — Okno Ładowania do

Otrzymamy dane w Excelu przedstawione na rysunku nr 19.

rys. nr 19 — Dane wczytane do Excela

Jedyną wadą takiego rozwiązania jest to, że Power Query sortuje wszystkie dane, łącznie z nazwami kolumn. Możemy sobie sprawdzić działanie zapytania dodając dodatkowe dane do tabeli z dany mi bazowymi (dodatkowe wiersze i kolumnę). Następnie klikamy prawym przyciskiem myszy na komórkę z tabeli z Power Query i z podręcznego menu wybieramy polecenie Odśwież (rys. nr 20).

rys. nr 20 — Odśwież dane

Otrzymamy prawidłowo przeliczone dane przedstawione na rysunku nr 21.

rys. nr 21 — Przeliczone dane

Przedsprzedaż Mistrz Excela II wydanie

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 przedsprzedaży możesz mieć Mistrza Excela w obniżonej cenie:
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 przedsprzedaży możesz mieć całą książkę za jedyne 30 zł (łącznie z darmową wysyłką listem ekonomicznym), a jeśli zależy Ci na dedykacji wystarczy, że dołożysz 10 zł.

Książka Mistrz Excela reklama

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.


Przedsprzedaż Mistrz Excela II wydanie

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 przedsprzedaży możesz mieć Mistrza Excela w obniżonej cenie:
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 przedsprzedaży możesz mieć całą książkę za jedyne 30 zł (łącznie z darmową wysyłką listem ekonomicznym), a jeśli zależy Ci na dedykacji wystarczy, że dołożysz 10 zł.

Książka Mistrz Excela reklama