W dzisiejszym poście omówimy sobie wyznaczenie ceny produktu na podstawie daty zamówienia. Zadanie to w Power Query rozwiązaliśmy już w poprzednim poście nr 33 https://exceliadam.pl/?p=9575 , za pomocą poleceń ze wstążki. W tym poście rozwiążemy ten problem przy użyciu funkcji Table.SelectRows. Temat ten omówimy na podstawie przykładowych danych przedstawionych na rysunku nr 1.
Zadanie polega na znalezieniu ceny danego produktu na podstawie daty zamówienia. Mamy podane również daty, kiedy cena ta ulegała zmianie.
Zakładamy, że dwie tabelki z rysunku nr 1 mamy zaczytane do Power Query z rozwiązania przedstawionego w poprzednim poście. Aby mieć podgląd zapytań wybieramy polecenie Pokaż zapytania z karty Dane (rys. nr 2).
Następnie klikamy dwa razy myszką na nazwę naszego zapytania w oknie Zapytań dotyczących skoroszytu, aby przejść do Power Query (rys. nr 3).
Otworzy nam się edytor zapytań z wczytaną tabelą tCeny oraz tZamówienia (oznaczone zielonym prostokątem na rysunku nr 4). Naszym zadaniem jest scalenie tych zapytań. Rozwijamy polecenie Połącz (punkt nr 2 na rysunku nr 4) z karty Narzędzia główne, następnie rozwijamy polecenie Scal zapytania (punkt nr 3) i wybieramy polecenie Scal zapytania jako nowe (punkt nr 4).
Otworzy nam się okno Scalanie, w którym musimy określić jego parametry. W obu zapytaniach zaznaczamy kolumnę Produkt, ponieważ po tym elemencie chcemy scalić zapytania. Wybieramy rodzaj sprzężenia jako Lewe zewnętrzne, czyli wszystkie elementy z pierwszego zapytania i pasujące z drugiego. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 5).
Otrzymamy scalone dane przedstawione na rysunku nr 6.
W kolejnym etapie zmieniamy nazwę kroku na Scalone2 we właściwościach w Ustawieniach zapytania. Pierwszy krok w Power Query jest identyczny z rozwiązaniem przedstawionym w poprzednim poście. W danych tych mamy już ukryte interesujące nas informacje, czyli pod słowem Table kryje się tabela z datami zmiany ceny produktu (rys. nr7).
Dane w tabelce poniżej musimy odfiltrować, czyli wyrzucić z niej te daty które nie pasują do daty zamówienia. Dla przypomnienia – nasze dane posortowaliśmy wcześniej po produkcie i dacie zmiany ceny, dzięki temu możemy teraz relatywnie prosto znaleźć rozwiązanie. Z karty Dodaj kolumnę wybieramy polecenie Kolumna niestandardowa (rys. nr 8).
Otworzy nam się okno kolumny niestandardowej, gdzie zmieniamy nazwę nowej kolumny na REC (jak Bill Szysz w swoim rozwiązaniu). Następnie w polu Formuła kolumny niestandardowej wpisujemy formułę funkcji Table.SelectRows. Zapis jej powinien wyglądać następująco:
=Table.SelectRows([tCeny] each [Data zmiany ceny] <= #date (2018,2,1))
Funkcja ta pozwoli nam wyciągnąć konkretne wiersze z kolumny tCeny pod nałożonym warunkiem, czyli dla każdego wiersza gdzie Data zmiany ceny jest mniejsza bądź równa dacie zamówienia. Wpiszemy w formule zamiast Daty zamówienia znak # ponieważ na razie nie możemy sięgnąć po datę z tej kolumny. Tak ustawione parametry zatwierdzamy klikając przycisk OK (rys. nr 9).
Otrzymamy dane przedstawione na rysunku nr 10.
Formuła ta ogranicza nam wyniki lecz nie we właściwy sposób. My chcemy odwoływać się nie do konkretnej podanej daty a do innej daty dla każdego wiersza, czyli do daty zamówienia każdego produktu. Nie możemy ręcznie wpisać w formule odwołania do kolumny Data zamówienia ponieważ funkcja zwróci nam błąd (nie może znaleźć pola). Rys. nr 11.
Kiedy wpiszemy nazwę funkcji w pasku formuły Power Query wyświetli nam informacje o niej. Możemy wpisywać w niej albo wartości stałe albo wartości z wierszy z tej tabeli. Nie możemy użyć kolumny Data zamówienia ponieważ nie zawiera się w tabelach ukrytych pod Table w kolumnie tCena (rys. nr 12).
Bill Szysz podpowiedział mi rozwiązanie tego problemu. Możemy sobie wkleić funkcję (x) w funkcję Table.SelectRows. Funkcja ta to tak naprawdę cały wiersz naszych danych. Zapis powinien wyglądać następująco:
=Table.AddColumn (Źródło, "REC", (x) => Table.SelectRows (x[tCeny], each [Data zmiany ceny] <= x[Data zamówienia]))
Otrzymamy dane przedstawione na rysunku nr 13, gdzie nadal czasem w wynikach mamy dwa wiersze.
Aby uzyskać pojedyncze rozwiązanie musimy wstawić kolejną funkcję. Pamiętamy, że nasze dane posortowaliśmy według Produktu i Daty zmiany ceny więc wynikiem będzie ostatni wiersz.
Zapis funkcji będzie wyglądał następująco:
= Table.AddColumn (Źródło, "REC", (x) => Table.Last( Table.SelectRows (x[tCeny], each [Data zmiany ceny] <= x[Data zamówienia])))
Otrzymamy tym razem pojedyncze wyniki dla każdego wiersza przedstawione na rysunku nr 15.
Aby wyciągnąć dane, które nas interesują, czyli cenę, klikamy na ikonkę ze strzałkami w nazwie kolumny REC i zaznaczamy checkbox przy wartości Cena i odznaczamy checkbox przy poleceniu Użyj oryginalnej nazwy kolumny jako prefiksu. Tak ustawione parametry zatwierdzamy klikając przycisk OK (rys. nr 16).
Otrzymamy dane przedstawione na rysunku nr 17.
Na tym etapie możemy już usunąć kolumnę tCeny.
Aby uczyć się formuł w Power Query możemy usunąć krok Rozwinięty element REC z Zastosowanych kroków i wprowadzić kolejny element do formuły naszej funkcji. Poprzednia formuła zwróciła nam Rekord, czyli jeden cały wiersz. Kiedy dopiszemy w formule [Cena], otrzymamy już interesujący nas wynik (rys. nr 18). Zapis formuły powinien wyglądać następująco:
= Table.AddColumn (Źródło, "REC", (x) => Table.Last( Table.SelectRows (x[tCeny], each [Data zmiany ceny] <= x[Data zamówienia])) [Cena])
Klikamy prawym przyciskiem myszy na nazwę kolumny tCeny i za pomocą polecenia Usuń z podręcznego menu usuwamy niepotrzebną kolumnę (rys. nr 19).
Możemy jeszcze w zapisie formuły zmienić nazwę kolumny REC na Cena. Otrzymamy dane przedstawione na rysunku nr 20.
Podsumowując dziś omówiony sposób może wydawać się trudniejszy, ale gdy się nauczymy funkcji i formuł Power Query rozwiązywanie tego typu problemów zajmie nam chwilę. Skomplikowane w tym rozwiązaniu jest to, że łączymy pracę na tabelach wewnętrznych (ukrytych pod wartościami Table) oraz na tabelach danych z zapytań (rys. nr 21).
Tak przygotowane dane możemy zaczytać do Excela za pomocą polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 22).
Otworzy nam się okno Ładowania do, gdzie ustawiamy sposób wyświetlania danych jako Tabela oraz miejsce wstawienia danych – istniejący arkusz i wskazujemy konkretną komórkę (rys. nr 23).
Otrzymamy dane wczytane do Excela przedstawione na rysunku nr 24.
Pierwsze rozwiązanie przedstawione w poprzednim poście jest czasochłonne i zawiera wiele kroków, ale wszystkie kroki możemy wykonać z interfejsu użytkownika. Drugie rozwiązanie jest szybsze ale wymaga dobrej znajomości języka M i funkcji 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
