W dzisiejszym poście nauczymy się jak w Power Query stworzyć dynamiczną ilość kolumn przy podziale kolumny według ogranicznika. Rozwiązanie tego problemu zostało przedstawione na rysunku nr 1.
W Power Query często pojawia się problem, kiedy dzielimy dane na kolumny według ogranicznika, to w zależności ile tych produktów było rozdzielonych tym ogranicznikiem, Power Query mógł się nie odświeżyć poprawnie. Power Query zapisuje konkretną ilość kolumn powstałych w kroku Podziel kolumnę według ogranicznika i kiedy dopiszemy więcej danych, nie pojawią się nowe kolumny. Rozwiązanie, które przedstawimy wykorzystuje interfejs użytkownika. W pliku do pobrania możecie znaleźć trzy rozwiązania. Pierwsze, którego nie będę omawiał, bo przy większej ilości danych (powyżej 10) Power Query się gubił. Drugie rozwiązanie podpowiedziane przez Billa Szysza, z wykorzystaniem interfejsu użytkownika oraz trzecie dla bardziej zaawansowanych użytkowników, wykorzystujące język M.
Temat ten omówimy na podstawie przykładowych danych z rysunku nr 2.
Wybieramy polecenie Z tabeli/zakresu z karty Dane (rys. nr 3).
Otworzy nam się Edytor zapytań Power Query z wczytaną tabelą danych. Usuwamy krok Zmieniono typ z Zastosowanych kroków. Interesuje nas tylko kolumna Produkt, więc klikamy na jej nagłówek prawym przyciskiem myszy i z podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 4).
Otrzymamy dane przedstawione na rysunku nr 5.
Do prawidłowego działania zapytania przy użyciu interfejsu musimy dodać Kolumnę indeksu z karty Dodaj kolumnę (rys. nr 6).
Bez znaczenia czy jest indeksowana od wartości 0 czy 1, ważne aby w każdym wierszu były inne liczby. Otrzymamy dane przedstawione na rysunku nr 7.
W kolejnym etapie dzielimy naszą kolumnę Produkt po ograniczniku. Rozwijamy polecenie Podziel kolumny (punkt nr 2 na rysunku nr 8) z karty Przekształć i z listy wybieramy polecenie Według ogranicznika (punkt nr 3 na rysunku nr 8).
Otworzy nam się okno Dzielenia kolumny według ogranicznika, gdzie musimy określić parametry podziału. Wybieramy rodzaj ogranicznika — przecinek, następnie, kiedy dzielimy dane, czyli w naszym przykładzie przy każdym wystąpieniu ogranicznika. Następnie w opcjach zaawansowanych zaznaczamy podział na wiersze. Podział na kolumny ma ustawianą ilość kolumn na 11 ponieważ tyle najwięcej kolumny powstanie dla naszych danych, więc gdy dodamy większą ilość produktów Power Query ich nie pokaże, bo zapisze sobie w pamięci podział na 11 kolumn. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 9).
Otrzymamy dane przedstawione na rysunku nr 10.
Kolumna indeks odpowiednio się zduplikowała. Mamy w niej informacje, z którego wiersza pochodzą poszczególne dane. W kolejnym etapie możemy zrobić grupowanie po indeksie, żeby policzyć, ile było elementów w poszczególnych pozycjach w danych wejściowych. Wybieramy polecenie Grupowanie według z karty Przekształć (rys. nr 11).
Otworzy nam się okno Grupowania według, w którym musimy określić jego parametry. W polu Grupowania według wybieramy kolumnę Indeks, Wpisujemy nazwę nowej kolumny (Liczność) i wybieramy operację jaka ma zostać wykonana, czyli w naszym przykładzie Zlicz wiersze. Zatwierdzamy przyjęte założenia przyciskiem OK (rys. nr 12).
Otrzymamy dane przedstawione na rysunku nr 13.
W powyższych wynikach interesuje nas maksymalna wartość z kolumny Liczność. Zaznaczamy kolumnę Liczność i rozwijamy polecenie Statystyka (punkt nr 2 na rysunku nr 14) z karty Przekształć, a następnie wybieramy polecenie Maksimum (punkt nr 3 na rysunku nr 14).
Power Query wyciągnie nam, jako wynik, maksymalną wartość i otrzymamy daną przedstawioną na rysunku nr 15.
Przechodzimy do kroku Usunięto inne kolumny w Zastosowanych krokach, czyli do danych przedstawionych na rysunku nr 5 wyżej, następnie kopiujemy po wciśnięciu klawisza F2 nazwę tego kroku za pomocą skrótu klawiszowego Ctrl+C. Dołożymy teraz nowy krok w działaniu – zmienimy formułę w pasku formuły, a dokładniej zastąpimy nazwę kroku Obliczona wartość maksymalna na tą, którą skopiowaliśmy wcześniej, czyli Usunięto inne kolumny (rys. nr 16).
Zmianę tą zatwierdzamy przyciskiem Enter i otrzymamy dane przedstawione na rysunku nr 17.
W kroku Niestandardowe1 mamy kopię kroku Usunięto inne kolumny. Na tym etapie rozwijamy polecenie Podziel kolumny z karty Narzędzia główne, a następnie wybieramy polecenie Według ogranicznika (rys. nr 18).
Otworzy nam się okno Dzielenia kolumny według ogranicznika. W polu wybierz lub wprowadź ogranicznik wybieramy opcję Niestandardowe, a następnie wpisujemy ogranicznik przecinek i spacja (, ). Na liście Podziel przy wybieramy opcję Każde wystąpienie ogranicznika. Następnie w opcjach zaawansowanych wybieramy podział na kolumny i wpisujemy ilość kolumn, na która zostanie podzielona kolumna jako wartość 2. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 19).
Otrzymamy dane przedstawione na rysunku nr 20. Usuwamy krok Zmieniono typ z zastosowanych kroków.
Gdybyśmy mieli wpisaną wartość domyślną 11, jako liczbę kolumn, na która podzielić kolumny na rysunku nr 19, to Power Query w pasku formuły wypisałby 11 nazw (rys. nr 21).
Użyliśmy tutaj funkcji Table.SplitColumn, więc kiedy podejrzymy sobie jej działanie tej funkcji otrzymamy między innymi informacje przedstawione na rysunku nr 22.
Interesuje nas opcja ColumnNamesOrNumbers, czyli liczba kolumn bądź ich nazwy. W pasku formuły zamiast nazw kolumn (podświetlone na rysunku nr 23) wpisujemy wartość 11.
Zatwierdzamy zmiany w formule przyciskiem Enter i otrzymujemy dane przedstawione na rysunku nr 24.
Nie chcemy, aby wartość 11 była wpisana na stałe. Chcemy aby Power Query pobierał nam tą wartość z poprzedniego kroku, czyli z kroku Obliczona wartość maksymalna. W tym celu kopiujemy (po wciśnięciu klawisza F2) nazwę tego kroku za pomocą skrótu klawiszowego Ctrl+C. Teraz zamiast wartości 11 w pasku formuły wklejamy nazwę skopiowanego kroku poprzedzoną znakiem # (hash) i w cudzysłowie (rys. nr 25).
Po zatwierdzeniu formuły klawiszem Enter, otrzymujemy dane przedstawione na rysunku nr 26.
Tak przygotowane dane możemy zaimportować do Excela. W tym celu wybieramy polecenie Zamknij i załaduj do z karty Narzędzia główne (rys. nr 27).
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 28)
Otrzymamy dane przedstawione na rysunku nr 29.
Jeśli w danych wejściowych w wierszu nr 5, który rozpatrujemy, dopiszemy sobie dodatkowy produkt, a następnie odświeżymy nasze dane z Power Query, otrzymamy powiększoną tabelę o ten dopisany produkt. Tabela wczytana z Power Query jest tabelą dynamiczną, która reaguje po odświeżeniu na wprowadzane zmiany w danych bazowych. To rozwiązanie wykonaliśmy za pomocą interfejsu, natomiast w kolejnym filmie skorzystamy z kodu M (rozwiązanie podpowiedziane przez Billa Szysza).
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
