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.

rys. nr 1 — Przykładowe dane

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).

rys. nr 2 — pokaż zapytania

Następnie klikamy dwa razy myszką na nazwę naszego zapytania w oknie Zapytań dotyczących skoroszytu, aby przejść do Power Query (rys. nr 3).

rys. nr 3 — Lista zapytań dotyczących skoroszytu

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).

rys. nr 4 — Scal zapytania jako nowe

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).

rys. nr 5 — Parametry scalania

Otrzymamy scalone dane przedstawione na rysunku nr 6.

rys. nr 6 — Scalone dane

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).

rys. nr 7 — Dane ukryte pod Table

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).

rys. nr 8 — Kolumna niestandardowa

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).

rys. nr 9 — Parametry kolumny niestandardowej

Otrzymamy dane przedstawione na rysunku nr 10.

rys. nr 10 — Dane

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.

rys. nr 11 — Error

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).

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.

rys.nr 13 — dane

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.

rys. nr 14 — Wynik prawidłowy

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.

rys. nr 15 pojedyncze wyniki dla każdego wiersza

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).

rys. nr 16 — Odfiltrowanie danych

Otrzymamy dane przedstawione na rysunku nr 17.

rys. nr 17 — Odfiltrowane dane

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])

rys. nr 17 — Aktualne dane

Klikamy prawym przyciskiem myszy na nazwę kolumny tCeny i za pomocą polecenia Usuń z podręcznego menu usuwamy niepotrzebną kolumnę (rys. nr 19).

rys. nr 19 — Usuń kolumnę

Możemy jeszcze w zapisie formuły zmienić nazwę kolumny REC na Cena. Otrzymamy dane przedstawione na rysunku nr 20.

rys. nr 20 — Dane

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).

rys. nr 21 — Dane ukryte pod Table

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).

rys. nr 22 — Zamknij i załaduj do

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).

rys. nr 23 — Okno ładowanie do

Otrzymamy dane wczytane do Excela przedstawione na rysunku nr 24.

rys. nr 24 — Dane wczytane do Excela

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.


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.