Power Query 36 — Dynamiczna ilość kolumn przy podziale ogranicznikiem — Interfejs użytkownika

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.

rys. nr 1 — Rozwiązanie zadania

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.

rys. nr 2 — Przykładowe dane

Wybieramy polecenie Z tabeli/zakresu z karty Dane (rys. nr 3).

rys. nr 3 — Z tabeli

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

rys. nr 4 — Usuń inne kolumny

Otrzymamy dane przedstawione na rysunku nr 5.

rys. nr 5 — Dane po usunięciu innych kolumn

Do prawidłowego działania zapytania przy użyciu interfejsu musimy dodać Kolumnę indeksu z karty Dodaj kolumnę (rys. nr 6).

rys. nr 6 — kolumna indeksu

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.

rys. nr 7 — Dane z kolumną indeksu

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

rys. nr 8 — Podziel kolumny

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

rys. nr 9 — Dzielenie kolumny według ogranicznika

Otrzymamy dane przedstawione na rysunku nr 10.

rys. nr 10 — Podzielone dane

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

rys. 11 — Grupowanie według

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

rys. 12 — Parametry grupowania danych

Otrzymamy dane przedstawione na rysunku nr 13.

rys. 13 — pogrupowane dane

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

rys. nr 14 — Maksimum

Power Query wyciągnie nam, jako wynik, maksymalną wartość i otrzymamy daną przedstawioną na rysunku nr 15.

rys. 15 — Maksymalny wynik

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

rys. 16 — Pasek formuły

Zmianę tą zatwierdzamy przyciskiem Enter i otrzymamy dane przedstawione na rysunku nr 17.

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

rys. 18 — Podziel kolumny

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

rys.19 — Parametry dzielenia według ogranicznika

Otrzymamy dane przedstawione na rysunku nr 20. Usuwamy krok Zmieniono typ z zastosowanych kroków.

rys. 20 — Podzielone dane

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

rys.21 Ilość nazw kolumn wyznaczona na pasku formuły

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.

rys. 22 — Table.SplitColumn

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.

rys. nr 23 — Pasek formuły

Zatwierdzamy zmiany w formule przyciskiem Enter i otrzymujemy dane przedstawione na rysunku nr 24.

rys. 24 — Dane

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

rys. 25 — Zmiany w pasku formuły

Po zatwierdzeniu formuły klawiszem Enter, otrzymujemy dane przedstawione na rysunku nr 26.

rys. 26 — Końcowe dane

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

rys. 27 — Zamknij i załaduj do

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)

rys. 28 — Okno importowania danych

Otrzymamy dane przedstawione na rysunku nr 29.

rys. 29 Dane zaimportowane do Excela

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


Przedsprzedaż Mistrz Excela II wydanie

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 przedsprzedaży możesz mieć Mistrza Excela w obniżonej cenie:
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 przedsprzedaży możesz mieć całą książkę za jedyne 30 zł (łącznie z darmową wysyłką listem ekonomicznym), a jeśli zależy Ci na dedykacji wystarczy, że dołożysz 10 zł.

Książka Mistrz Excela reklama