W dzisiejszym poście zajmiemy się kontynuacją poprzedniego, a dokładnie drugim sposobem rozwiązania zadania polegającego na dynamicznym określeniu ilości kolumn przy podziale ogranicznikiem. Rozwiązanie to jest wykorzystywane przez Billa Szysza, jest ono przeznaczone dla bardziej zaawansowanych użytkowników, ponieważ wymaga większej znajomości funkcji i kodu M w Power Query. Zadanie to wykonamy po podstawie przykładowych danych przedstawionych na rysunku nr 1.
Pierwszym krokiem jest zaczytanie danych wejściowych do Power Query. W tym celu klikamy polecenie Z tabeli/zakresu z karty Dane (rys. nr 2).
Otworzy nam się edytor zapytań Power Query z wczytaną tabelą z danymi przedstawioną na rysunku nr 3.
Z zastosowanych kroków usuwamy niepotrzebny krok Zmieniono typ. Do dalszych działań potrzebujemy tylko kolumny z produktami więc klikamy prawym przyciskiem myszy na nagłówek kolumny Produkt i z podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 4).
Otrzymamy interesujące nas dane przedstawione na rysunku nr 5.
Do tego momentu kroki w działaniu były takie same jak w poprzednim poście https://exceliadam.pl/?p=9720 . Aby ułatwić zrozumienie tematu nie zaczniemy pisać od razu funkcji w pasku formuły tylko dodamy kolumnę niestandardową z karty Dodaj kolumnę (rys. nr 6). Dodamy tą kolumnę, żeby to w niej zacząć pisać funkcję.
Otworzy nam się okno Kolumny niestandardowej, gdzie w polu Formuły kolumny niestandardowej wpiszemy funkcję. Zapis powinien wyglądać następująco:
=Text.Split([Produkt], ", ")
W funkcji Text.Split argumentem jest kolumna Produkt. Następnie wpisujemy ogranicznik, czyli w naszym przypadku — przecinek spacja ", ". Po wpisaniu formuły zatwierdzamy naszą kolumnę niestandardową przyciskiem OK (rys. nr 7).
Otrzymamy dane przedstawione na rysunku nr 8.
Polecenie to dzieli nasze kolumny na Listy. Pod każdym słowem List jest ukryta lista produktów (przykład przedstawiony został na rysunku nr 9).
Kolejnym krokiem jest policzenie elementów każdej z tych list. Użyjemy do tego funkcji List.Count. klikamy na nazwę kroku Dodano kolumnę niestandardową, aby otworzyć znowu okno Kolumny niestandardowej i wprowadzić zmiany w formule tej kolumny (rys. nr 10). Zapis powinien wyglądać następująco:
=List.Count(Text.Split([Produkt], ", "))
Zatwierdzamy zmienioną formułę przyciskiem OK i otrzymujemy dane przedstawione na rysunku nr 11.
Podsumowując najpierw podzieliliśmy dane na Poszczególne listy za pomocą funkcji Text.Split a następnie policzyliśmy ilość elementów w każdej liście za pomocą funkcji List.Count. W pasku formuły możemy wprowadzić zmiany, aby uzyskać interesujący nas wynik. Zamieniamy funkcję Table.AddColumn na List.Transform oraz dodajemy argument Produkt. Formuła powinna wyglądać jak na rysunku nr 12.
=List.Transform(#"Usunięto inne kolumny" [Produkt], each List.Count(Text.Split(_,", ")))
Po zatwierdzeniu formuły otrzymamy dane przedstawione na rysunku nr 13.
Otrzymaliśmy listę wszystkich elementów na konkretnych listach. Za pomocą funkcji List.Transform wyciągnęliśmy z danych tylko jedną kolumnę. Powinniśmy pamiętać, że nie potrzebujemy listy wszystkich elementów tylko maksymalną wartość. Wystarczy wstawić w formułę na pasku formuł funkcję List.Max. Formuła powinna wyglądać jak na rysunku nr 14.
=List.Max(List.Transform(#"Usunięto inne kolumny" [Produkt], each List.Count(Text.Split(_,", "))))
Kiedy zatwierdzimy formułę klawiszem Enter otrzymamy wynik przedstawiony na rysunku nr 15.
Kolejny krok pokrywa się z krokiem z wcześniejszego filmu, czyli Power Query odcinek 36.
Kopiujemy nazwę kroku Usunięto inne kolumny po zaznaczeniu go i wciśnięciu klawisza F2, za pomocą skrótu klawiszowego Ctrl+C. Klikamy przycisk fx przy pasku formuły i wklejamy skopiowaną nazwę kroku za pomocą skrótu klawiszowego Ctrl+V. Otrzymamy dane przedstawione na rysunku nr 16.
Rozwijamy polecenie Podziel kolumny (punkt nr 2 na rysunku nr 17) z karty Narzędzia główne, a następnie wybieramy polecenie Według ogranicznika (punkt nr 3 na rysunku nr 17).
Otworzy nam się okno Dzielenia kolumny według ogranicznika, gdzie w polu wybierz lub wprowadź ogranicznik wybieramy opcję Niestandardowe (punkt nr 1 na rysunku nr 18). Następnie w polu, które się pojawi wpisujemy nasz ogranicznik, czyli przecinek i spacja. Zaznaczamy Podziel przy każdym wystąpieniu ogranicznika (punkt nr 2), Podziel na Kolumny (punkt nr 3) i wpisujemy liczbę kolumn 2 (wpisujemy mniejszy parametr bo będziemy go zmieniać). Tak ustawione parametry dzielenia kolumny zatwierdzamy przyciskiem OK.
Otrzymamy dane przedstawione na rysunku nr 19. Usuwamy niepotrzebny krok o nazwie Zmieniono typ.
W pasku formuły w tym zapytaniu zmieniamy nazwy poszczególnych kolumn (podświetlone na rysunku nr 20) na odwołanie do wcześniejszego kroku
Klikamy nazwę wcześniejszego kroku — Dodano kolumnę niestandardową, wciskamy klawisz F2, a następnie kopiujemy nazwę kroku za pomocą skrótu klawiszowego Ctrl+C. Następnie tworzymy odwołanie do tego kroku, czyli w formule w miejscu zaznaczonym na rysunku nr 20 wklejamy nazwę naszego kroku poprzedzoną znakiem # (hash) i w podwójnym cudzysłowie. Zapis powinien wyglądać jak na rysunku nr 21.
Po zatwierdzeniu formuły otrzymamy dane przedstawione na rysunku nr 22.
Otrzymaliśmy jedenaście kolumn dynamicznych, czyli zmieniających się pod wpływem zmian w danych wejściowych.
Jeśli chcemy mieć mniej kroków w działaniach możemy usunąć krok Niestandardowe1, do którego się odwołaliśmy w formule. Wystarczy, że w pasku formuły z rysunku nr 21 zastąpimy nazwę kroku Niestandardowe1 na Usunięto inne kolumny, ponieważ oba kroki zawierają takie same dane. Zapis formuły po zastąpieniu nazw powinien wyglądać jak na rysunku nr 23.
Następnie możemy usunąć już zbędny krok Niestandardowe1. Klikamy na nazwę kroku i wciskamy przycisk Delete. Pojawi nam się komunikat Usuwanie kroku, gdzie potwierdzamy usunięcie go za pomocą przycisku Usuń (rys. nr 24).
Tak przygotowane dane możemy załadować do Excela korzystając z polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 25).
Otworzy nam się okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako Tabela i wskazujemy miejsce ich wstawienia, czyli Istniejący arkusz oraz wskazujemy konkretną komórkę. Tak określone parametry zatwierdzamy przyciskiem OK (rys. nr 26)
Otrzymamy dane zaimportowane do Excela przedstawione na rysunku nr 27.
Możemy w danych źródłowych dopisać dodatkowy wiersz z danymi, aby sprawdzić działanie dynamicznych kolumn. Po odświeżeniu danych w tabeli z Power Query otrzymamy aktualne dane. Tabelę odświeżamy klikając prawym przyciskiem myszy na jej obszar a następnie korzystając z polecenia Odśwież z podręcznego menu.
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
