W dzisiejszym poście zrobimy wstęp do kolejnego odcinka, w którym w Power Query będziemy uwzględniać sytuacje, w jakich dany sprzedawca nie sprzedawał konkretnego produktu (ciasteczek). Chodzi nam o sprzedawcę Roberta, który nie pokazywał się w rozwiązaniu z odcinka o Power Query nr 43 (rys. nr 1).

rys. nr 1 - rozwiązanie odcinka Power Query 43
rys. nr 1 — rozwiązanie odcinka Power Query 43

W tym wideo zaczynamy od iloczynu kartezjańskiego, czyli sparowania każdego elementu z każdym (rys. nr 2).

rys. nr 2 - sparowanie danych - iloczyn kartezjański
rys. nr 2 — sparowanie danych — iloczyn kartezjański

Mamy dwie listy rozwijane, jedna zawiera nazwy sprzedawców, a druga produkty. Naszym zadaniem jest sparowanie, przypisanie do każdego ze sprzedawców z pierwszej listy, wszystkich produktów z drugiej listy (rys. nr 3).

rys. nr 3 - zasada iloczyn kazrtezjańskiego
rys. nr 3 — zasada iloczyn kazrtezjańskiego

Ręcznie możemy to zrobić kopiując sprzedawcę Jan za pomocą skrótu klawiszowego Ctrl+C do komórki E1, następnie kopiujemy wszystkie produkty z kolumny Produkt do komórki F1. Następnie kopiujemy nazwę sprzedawcy na odpowiednią ilość wierszy w dół, aby każdy produkt miał przypisanego sprzedawcę (rys. nr 4).

rys. nr 4 - pary dla jednego sprzedawcy
rys. nr 4 — pary dla jednego sprzedawcy

Analogicznie postępujemy dla wszystkich sprzedawców, otrzymamy wtedy listę wszystkich sprzedawców i produktów (rys. nr 5).

rys. nr 5 - pary dla wszystkich sprzedawców
rys. nr 5 — pary dla wszystkich sprzedawców

Taki sposób tworzenia tych list jest czasochłonne. Szczególnie w sytuacji gdybyśmy mieli większą ilość danych. Naszym zadaniem jest zautomatyzowanie tego procesu – żeby zrobił to za nas Power Query.

Pierwszym krokiem jest zaczytanie obu list (Sprzedawca i Produkt) do Power Query. W tym celu ustawiamy aktywną komórkę na liście Produkt i wybieramy polecenie Z tabeli/zakresu z karty Dane (rys. nr 6).

rys. nr 6 - Z tabeli/zakresu
rys. nr 6 — Z tabeli/zakresu

Otworzy nam się Edytor zapytań Power Query z wczytaną tabelą Produkt. Power Query pozostawił domyślny typ danych, a nam zależy, żeby traktował nasze dane jako tekst, więc rozwijamy ikonkę ABC123 przy tytule kolumny i wybieramy typ danych Tekst (rys. nr 7).

rys. nr 7 - zmiana typu danych
rys. nr 7 — zmiana typu danych

Tak przygotowane dane chcemy zaczytać do Excela jako połączenie. Wybieramy polecenie Zamknij i załaduj do z karty Narzędzia główne (rys. nr 8).

rys. nr 8 - zamknij i załaduj do
rys. nr 8 — zamknij i załaduj do

W Excelu otworzy nam się okno importowania danych, gdzie wybieramy sposób wyświetlania danych jako Utwórz tylko połączenie, następnie ustawienie to zatwierdzamy przyciskiem OK (rys. nr 9).

rys. nr 9 - Importowanie danych
rys. nr 9 — Importowanie danych

Następnie ustawiamy aktywną komórkę na liście Sprzedawca i ponownie wybieramy polecenie Z tabeli/zakresu z karty Dane (jak na rys. nr 6).

Otworzy nam się Edytor zapytań Power Query z wczytaną tabelą Sprzedawca (rys. nr 10). Co istotne musimy zwrócić uwagę na nazwy naszych zapytań, czyli tProdukty oraz tSprzedawcy.

rys. nr 10 - nazwy zapytań z danymi
rys. nr 10 — nazwy zapytań z danymi

Głównym zapytaniem jest tSprzedawcy, ponieważ zapytanie tProdukty zaczytaliśmy tylko jako połączenie.

Naszym celem jest stworzenie iloczynu kartezjańskiego, czyli otrzymanie tabeli, gdzie każdemu sprzedawcy będzie przyporządkowany każdy produkt.

Musimy skopiować sobie nazwę zapytania tProdukty, klikamy na nazwę zapytania i za pomocą klawisza F2, przychodzimy w tryb edycji. Następnie za pomocą skrótu klawiszowego Ctrl+C kopiujemy nazwę zapytania (rys. nr 11).

rys. nr 11 - Tryb edycji nazwy zapytania
rys. nr 11 — Tryb edycji nazwy zapytania

Wracamy do zapytania tSprzedawcy i wybieramy polecenie Kolumna niestandardowa z karty Dodaj kolumnę (rys. nr 12).

rys. nr 12 - kolumna niestandardowa
rys. nr 12 — kolumna niestandardowa

Otworzy nam się okno Kolumny niestandardowej, gdzie zmieniamy nazwę nowej kolumny na Produkty (punkt 1 na rys. nr 13). W polu Formuła kolumny niestandardowej wklejamy skopiowaną nazwę zapytania tProdukty (punkt 2). Tak przygotowane parametry kolumny niestandardowej zatwierdzamy przyciskiem OK.

rys. nr 13 - okno kolumny niestandardowej
rys. nr 13 — okno kolumny niestandardowej

Otrzymamy dane przedstawione na rys. nr 14.

rys. nr 14 - dane z nową kolumną
rys. nr 14 — dane z nową kolumną

W każdej komórce ze skrótem Table w kolumnie Produkty ukryta jest tabelka z danymi (rys. nr 15).

rys. nr 15 - dane ukryte pod nazwą table
rys. nr 15 — dane ukryte pod nazwą table

Klikamy ikonkę ze strzałkami po prawej stronie nazwy kolumny Produkty, a następnie wybieramy polecenie Rozwiń oraz odznaczamy checkbox przy poleceniu Użyj oryginalnej nazwy kolumny jako prefiksu. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 16).

rys. nr 16 - polecenie rozwiń
rys. nr 16 — polecenie rozwiń

Otrzymamy dane przedstawione na rys. nr 17.

rys. nr 17 - rozwinięte dane
rys. nr 17 — rozwinięte dane

W danych tych każdy sprzedawca został powtórzony tyle razy, ile jest produktów. Tak przygotowane dane możemy załadować do Excela. W tym celu wybieramy polecenie Zamknij i załaduj do z karty Narzędzia główne (jak na rys. nr 8).

Otworzy nam się okno Importowania danych, gdzie ustawiamy Sposób wyświetlania danych jako Tabela, a następnie wybieramy miejsce wstawienia tych danych – Istniejący arkusz i wskazujemy konkretną komórkę – w naszym przykładzie komórka H1. Tak przygotowane parametry zatwierdzamy przyciskiem OK (rys. nr 18).

rys. nr 18 - importowanie danych
rys. nr 18 — importowanie danych

Otrzymamy dane zaczytane do Excela przedstawione na rys. nr 19

rys. nr 19 - dane wstawione do Excela
rys. nr 19 — dane wstawione do Excela

Poprawność działania formuły możemy sprawdzić, kiedy dołożymy innego sprzedawcę np. Michał w tabeli Sprzedawca oraz produkt Mleko w tabeli Produkt, następnie klikniemy prawym przyciskiem myszy na dowolną komórkę w danych otrzymanych z Power Query i wybierzemy polecenie Odśwież (rys. nr 20).

rys. nr 20 - polecenie Odśwież dane
rys. nr 20 — polecenie Odśwież dane

Otrzymamy zaktualizowane dane przedstawione na rys. nr 21.

rys. nr 21 - zaktualizowane dane
rys. nr 21 — zaktualizowane dane

Zmieniła nam się ilość sprzedawców (5 osób) oraz ilość produktów (6 szt.), więc w wyniku powinniśmy otrzymać 30 par (rys. nr 22).

rys. nr 22 - ilość wyników z Power Query
rys. nr 22 — ilość wyników z Power Query

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