W dzisiejszym poście zajmiemy się tematem scalania tego samego zapytania w Power Query. Zagadnienie to omówimy na podstawie danych z poprzedniego postu przedstawionych na rysunku nr 1.
Poprzedni post dotyczył wyciągania pierwszych i ostatnich wierszy po unikatowych wartościach z kolumn Data i Firma. Stworzyliśmy dwa zapytania i scaliliśmy je ze sobą. Tym razem chcemy mieć jedno zapytanie. Zagadnienie to jest wstępem do języka zapytań M, czyli kulis zapytań Power Query. Pozwoli nam to w miarę naszego rozwoju nie korzystać tylko z gotowych rozwiązań tego dodatku do Excela.
Pierwszym krokiem jest wczytanie naszych danych z tabeli do Power Query. Musimy w tym celu użyć polecenia Z tabeli z karty Dane (rys. nr 2).
Tak jak w poprzednim poście dane zostały wczytane w innym formacie więc musimy dla kolumny Data i Czas zmienić typ kolumny na bieżącą – klikamy prawym przyciskiem myszy na tytuł kolumn Data i analogicznie Czas, następnie wybieramy polecenie Data lub dla drugiej kolumny Godzina i na komunikacie, który się wyświetli klikamy przycisk Zamień bieżącą (zaznaczono zieloną strzałką na rysunku nr 3). Celem tych działań jest to, żeby Power Query odpowiednio interpretował te dane.
Na karcie Widok musimy mieć włączony Pasek formuły (rys. nr 4). Będziemy robić na nim drobne zmiany w kodzie.
Wczytane dane przedstawione zostały na rysunku nr 5.
Kolejnym krokiem jest posortowanie danych. Klikamy prawym przyciskiem myszki na trójkącik przy tytule kolumny Data i wybieramy polecenie Sortuj rosnąco (rys. nr 6).
Te same działania musimy powtórzyć dla kolumny Czas. Klikamy prawym przyciskiem myszki na trójkącik przy tytule kolumny Czas i wybieramy polecenie Sortuj rosnąco. Z boku ekranu w Ustawieniach zapytania mamy wpisane kroki, które wykonaliśmy na naszych danych (rys. nr 7).
Możemy zmienić nazwę tego kroku, aby w każdym momencie działań widzieć co konkretnie zrobiliśmy. Naciskamy klawisz F2 i zmieniamy nazwę na Posortowano Asc (nie napisałem rosnąco, bo ze względu na język programowania staram się nie używać polskich znaków) – rys. nr 8.
Teraz tak jak w poprzednim poście musimy usunąć duplikaty po kolumnach Data i Firma. Zaznaczamy kolumny Data i Firma, a następnie wybieramy z karty Narzędzia główne polecenie Usuń wiersze (punkt nr 2 na rysunku nr 9), a potem Usuń duplikaty (punkt nr 3 na rysunku nr 9).
Otrzymamy wyciągnięte pierwsze wiersze po unikatowych wartościach. Zostały one przedstawione na rysunku nr 10.
Analogicznie jak dla poprzedniego etapu zmieniamy nazwę zastosowanego kroku w Ustawieniach zapytania za pomocą klawisza F2 z Usunięto duplikaty na Pierwsze.
Chcemy aby teraz kolejny krok jaki wykonamy nie był zależny od kroku Pierwsze tylko od wcześniejszego czyli Posortowano Asc. Klikamy na krok Posortowano Asc i kopiujemy formułę z paska formuły za pomocą skrótu klawiszowego Ctrl+C. Następnie przechodzimy do ostatniego naszego kroku, czyli Pierwsze i na pasku formuły kliknąć przycisk funkcyjny fx – dodać krok niestandardowy i wkleić (Ctrl+V lub kliknąć prawym przyciskiem myszy i z podręcznego menu wybrać polecenie Wklej) w pasek formuły skopiowaną wcześniej formułę z kroku Posortowano Asc.
Musimy zmienić część wklejonej formuły, bo chcemy aby kolumna Czas była posortowana malejąco. Najprościej wystarczyło by zmienić rodzaj sortowania klikając na znaczek sortowania przy nazwie kolumny Czas (rys. nr 12).
Dla bardziej zaawansowanych użytkowników jest inny sposób. Wystarczy zmienić w formule dla kolumny czas sortowanie z Ascending na Descending, co zostało pokazane na rysunku nr 13.
Oba sposoby zadziałają w ten sam sposób. Zmieniamy nazwę kroku Niestandardowe 1 za pomocą klawisza F2 na Posortowano Desc. Musimy pamiętać, że Power Query ma problem z tym, że dane nie zostały wczytane od nowa, pamięta wcześniejszy bufor więc jak w poprzednim poście musimy dodać kolumnę indeksu. Z karty Dodaj kolumnę wybieramy polecenie Kolumna indeksu (punkt nr 2 na rysunku nr 14) a następnie Od 0 (punkt nr 3 na rysunku nr 14). Nie ma znaczenia jaki typ kolumny indeksu wybierzemy bo jest to kolumna pomocnicza i w późniejszym etapie ją usuniemy.
Power Query dzięki dodaniu dodatkowej kolumny od nowa zaczyta dane i zapamięta kolejność z odwrotnym sortowaniem. Zaznaczamy kolumny Data i Firma i z karty Narzędzia główne wybieramy polecenie Usuń wiersze a następnie Usuń duplikaty (analogicznie jak na rysunku nr 9 dla pierwszych wierszy). Otrzymamy ostatnie wiersze dla unikatowych wartości przedstawione na rysunku nr 15.
Zaznaczamy kolumnę Czas i Indeks i usuwamy za pomocą polecenia Usuń kolumny z podręcznego menu (rys. nr 16).
W poprzednim poście te same wyniki otrzymaliśmy w dwóch osobnych zapytaniach. W tym poście otrzymaliśmy takie same wyniki w jednym zapytaniu tylko w innych krokach. Pierwsze wiersze są w kroku Pierwsze, a ostatnie wiersze są w kroku Usunięto kolumny. Zmienimy jeszcze nazwę ostatniego kroku za pomocą klawisza F2 na Ostatnie (rys. nr 17). Teraz wiemy, które kroki chcemy połączyć.
Z karty Narzędzia główne wybieramy polecenie Połącz (punkt nr 2 na rysunku nr 18), a następnie Scal zapytania (punkt nr 3 na rysunku nr 18).
Otworzy nam się okno Scalanie. Scalać będziemy to samo zapytanie, czyli wybierzemy tNotowania i w drugim okienku tNotowania (bieżący). Zaznaczamy w obu zapytaniach kolumny Data i Firma. One są na razie identyczne. W kolejnym kroku zrobimy pewną zmianę. Zatwierdzamy nasz wybór przyciskiem OK (rys. nr 19).
W tym kroku bardzo ważne jest, że widzimy pasek formuły, bo mamy tam podane, na którym kroku wykonujemy dane zadanie (rys. nr 20).
W naszej formule powinniśmy zmienić pierwsze wystąpienie słowa Ostanie na słowo Pierwsze (nazwy kroków zapytania). Aby otrzymać prawidłowe dane formuła na pasku formuły powinna wyglądać jak na rysunku nr 21.
W formule powyżej mamy informację, że chcemy połączyć dane z dwóch kroków w tym samym zapytaniu – krok Pierwsze i krok Ostatnie.
Z naszej tabeli musimy się pozbyć kolumny Czas za pomocą polecenie Usuń kolumnę z podręcznego menu. Otrzymamy dane przedstawione na rysunku nr 22.
Następnie rozwijamy kolumnę Ostatnie (strzałki w prawym roku nazwy kolumny), odznaczamy Datę i Firmę, a zostawiamy tylko Cenę. Nie chcemy używać oryginalnej nazwy kolumny jako prefiksu i na koniec zatwierdzamy nasze ustawienia przyciskiem OK (rys. nr 23).
W otrzymanych danych zmienimy nazwę dwóch ostatnich kolumn z Cenami na Pierwsze i Ostatnie. Otrzymamy końcowe dane dla pierwszych i ostatnich wierszy unikatowych wartości przedstawione na rysunku nr 24.
Są to dane uzyskane w jednym zapytaniu, włącznie ze scalaniem. Był to wstęp do zagłębiania się w język M, aby poznać lepiej formuły i możliwości jakie nam dają. Ostatnim krokiem jest załadowanie danych do Excela za pomocą polecenia Zamknij i załaduj z karty Narzędzia główne (rys. nr 25).
W Excelu otworzy nam się okno Ładowanie do, gdzie wybieramy sposób wyświetlania danych jako Tabela i miejsce załadowania danych jako Istniejący arkusz (konkretną komórkę). Nasz wybór zatwierdzamy przyciskiem Załaduj (rys. nr 26).
Dane wklejone do Excela prezentują się jak na rysunku nr 27.
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
