W dzisiejszym poście nauczymy się jak wyciągnąć wartości z ostatniego i pierwszego wiersza po unikatowych wierszach (wartościach) w Power Query. Jest to kontynuacja tematów z porad o numerach 307 i 308. Zagadnienie to omówimy na podstawie przykładowych danych z rysunku nr 1.
Wykorzystamy tutaj metodę, której użyliśmy w poradzie 307 w Excelu, ale tym razem w Power Query. Pierwszym krokiem jest wczytanie danych z Excela. Wybieramy polecenie Z tabeli (punkt nr 2 na rysunku nr 2) na karcie Dane (punkt 1).
Otworzy nam się Okno – Edytor zapytań, a w nim dane z tabeli tNotowania (rysunek nr 3).
Power Query niepotrzebnie do daty dodaje czas, więc klikamy na ikonkę kolumny Data, a następnie wybieramy polecenie Data (tylko data) z podręcznego menu (punkt nr 2 na rysunku nr 4).
Pojawi nam się komunikat dotyczący zmiany typu kolumny. Klikamy przycisk Zamień bieżącą (rys. nr 5).
Power Query nie rozpoznał poprawnie czasu, więc analogicznie klikamy na ikonkę na kolumnie Czas i wybieramy polecenie Godzina z podręcznego menu (rys. nr 6).
Pojawi się taki sam komunikat jak dla Czasu. Klikamy przycisk Zamień bieżącą. Otrzymamy wtedy odpowiednio przedstawione dane z rysunku nr 7.
Mając tak posortowane dane , chcemy teraz wyciągnąć pierwsze i ostatnie wiersze. Klikamy znak < zaznaczony na rysunku nr 7 zieloną strzałką. Nasze zapytanie nazywa się tNotowania. Potrzebujemy 2 razy tego zapytania, ponieważ raz chcemy wyciągnąć pierwsze wiersze, a za drugim razem ostatnie wiersze. Klikamy prawym przyciskiem myszy na nazwę zapytania tNotowania i z podręcznego menu wybieramy polecenie Duplikuj (rys. nr 8).
Klikamy na tNotowania i zaznaczamy sobie kolumny dla których chcemy wyciągnąć unikatowe wartości, czyli kolumny Data i Firma (rys. nr 9).
W pierwszym kroku tak jak w poradzie 307 musimy usunąć duplikujące się wartości. Z karty Narzędzia główne wybieramy polecenie Usuń wiersze (punkt nr 2 na rysunku nr 10), a następnie polecenie Usuń duplikaty (punkt nr 3).
W wyniku usunięcia duplikatów otrzymamy pierwsze wiersze przedstawione na rysunku nr 11.
Analogicznie zaczniemy działać dla zapytania tNotowania2. W pierwszej kolejności powinniśmy posortować dane w kolumnie Data. Użyjemy do tego polecenia Sortuj rosnąco pokazanego na rysunku nr 12.
A następnie musimy posortować kolumnę Czas w odwrotnej kolejności, czyli polecenie Sortuj malejąco. Posortowanie w ten sposób danych sprawiło, że na początku danych mamy ostatnie wiersze. Teraz wystarczy usunąć duplikaty. Zaznaczamy kolumny Data i Firma i z karty Narzędzia główne wybieramy polecenie Usuń wiersze, a następnie polecenie Usuń duplikaty (identycznie jak na rysunku nr 10). Otrzymamy ostatnie wiersze przedstawione na rysunku nr 13.
Od razu widać że są to takie same wartości jak dla pierwszych wierszy w zapytaniu tNotowania. Z Power Query jest taki problem, że nie odświeża sobie bufora danych i działa na pierwszym zaczytaniu. Nie uwzględnia sortowania. Możemy obejść ten błąd. Przed usunięciem duplikatów musimy wykonać operację która każe Powe Query ponownie zaczytać dane, stworzyć ich nowy układ. Rozwiązanie to podpowiedział mi Bill Szysz. Najprostszą operacją jest dodawanie kolumny indeksu. Z karty Dodaj kolumnę, wybieramy polecenie Kolumna indeksu (punkt 2 na rysunku nr 14), a następnie Od 1 (punkt 3).
Nie ma znaczenia czy dodamy Kolumnę indeksu od 0 czy od 1 bo chodzi tylko o ponowne wczytanie danych przez program Power Query. Zaznaczamy kolumny Data i Firma a następnie wybieramy polecenie Usuń duplikaty z karty Narzędzia główne (rys. nr 10 wyżej). Teraz otrzymamy dane przedstawione na rysunku nr 15.
Tym razem dane są poprawne, możemy to zauważyć między innymi po numerze kolumny indeksu (numery są nie po kolei). Kolumna Indeks i Czas są nam niepotrzebne. Zaznaczamy je, klikamy prawym przyciskiem myszki i z podręcznego menu wybieramy polecenie Usuń kolumny (rys. nr 16).
Otrzymamy dane dla zapytania tNotowania2 przedstawione na rysunku nr 17.
Analogicznie dla zapytania tNotowania usuwamy kolumnę Czas i otrzymujemy wyniki przedstawione na rysunku nr 18.
W kolejnym kroku musimy scalić te zapytania. Zaznaczamy zapytanie tNotowania i z karty Narzędzia główne wybieramy polecenie Połącz, potem Scal zapytania (polecenia oznaczone kolejno na rysunku nr 19).
Otworzy nam się okno Scalanie. Musimy pamiętać, aby dla zapytania tNotowania (punkt nr 1 na rysunku nr 20) przy użyciu przycisku Ctrl zaznaczyć kolumny Data i Firma, bo po tych kolumnach mamy wyznaczone niepowtarzalność wiersza. Analogicznie dla zapytania tNotowania2 (punkt nr 2 na rysunku nr 20) zaznaczamy kolumny Data i Firma. Następnie w punkcie oznaczony numerem 3 na rysunku poniżej wybrać Rodzaj sprzężenia, czyli Lewe zewnętrzne.
Zatwierdzamy opcje scalania przyciskiem OK i mamy dołożone do naszego zapytania tNotowania wyniki zapytania tNotowania2 (rys. nr 21).
Na razie wyniki drugiego zapytania są w formie tabeli. A wyniki poszczególnych wierszy przedstawione są poniżej (miejsce zaznaczone zieloną strzałką na rysunku nr 22)
Z tych wartości potrzebujemy wyciągnąć tylko cenę bo inne wartości już mamy. Klikamy ikonkę dwóch strzałek przy nazwie tNotowania2 i zaznaczamy opcję Rozwiń, następnie zaznaczamy tylko kolumnę Cena i odznaczamy checkbox przy poleceniu Użyj oryginalnej nazwy kolumny jako prefiksu (polecenia kolejno oznaczone numerami od do 3 na rysunku nr 23).
Zatwierdzamy nasz wybór przyciskiem OK i otrzymujemy dane przedstawione na rysunku nr 24, zawierające ceny z obu zapytań. Możemy pozmieniać nazwy kolumn na Pierwsza cena i Ostatnia cena.
Tak przygotowane dane możemy zamknąć i załadować do Excela. Polecenie Zamknij i załaduj do na karcie Narzędzia główne (rys. nr 25).
W Excelu wyświetli nam się okno Ładowanie do. Wybieramy sposób wyświetlania danych jako Utwórz tylko połączenie, a następnie lokalizację wstawienia danych jako Nowy arkusz i Zatwierdzamy nasz wybór klikając przycisk OK (rys. nr 26).
Rozwijamy po prawej stronie okienko Zapytania do. Klikamy prawym przyciskiem myszy na tNotowania i z podręcznego menu wybieramy polecenie Załaduj do (rys. nr 27).
Pokaże się nam okno ładowanie do, w którym wybieramy sposób wyświetlania danych jako Tabela i lokalizację ich wyświetlenia w Istniejącym arkuszu, następnie klikamy przycisk Załaduj (rys. nr 28).
Otrzymamy dane przedstawione na rysunku nr 29.
Power Query sprawdza się przy wyznaczaniu wartości z pierwszego i ostatniego wiersza, ale zdarzają się zgrzyty przy usuwaniu duplikatów. Wynika to z tego, że Power Query potrzebuje przeczyszczenia buforu danych, ponownego załadowania danych.
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
