W dzisiejszym poście nauczymy się jak wyciągnąć wszystkie cyfry z tekstu w Power Query. W poprzednim odcinku na temat Power Query pokazywaliśmy jak wyciągnąć cyfry znajdujące się na początku tekstu bądź na jego końcu. W przykładowych danych mamy tabelę, gdzie tekst został pomieszany z cyframi (rys. nr 1).
Rys. nr 1 – przykładowe dane
W celu zaczytania danych do Power Query wybieramy polecenie Z tabeli/zakresu z karty Dane (rys. nr 2).
Rys. nr 2 – polecenie Z tabeli/zakresu
Otworzy nam się edytor zapytań Power Query z wczytaną tabelą z przykładowymi danymi przedstawiony na rys. nr 3.
Rys. nr 3 – edytor zapytań Power Query z wczytanymi danymi
Teraz zajmiemy się odpowiednim przekształcaniem tych danych. W pierwszym kroku musimy skopiować naszą kolumnę. W tym celu wybieramy polecenie Duplikuj kolumnę z karty Dodaj kolumnę (rys. nr 4).
Rys. nr 4 – polecenie Duplikuj kolumnę
Otrzymamy dane ze zduplikowaną kolumną przedstawione na rys. nr 5.
Rys. nr 5 – dane ze zduplikowaną kolumną
Teraz
zmienimy sobie nazwę nowej kolumny w pasku formuły, aby nie dodawać nowego
kroku. Zmieniamy tekst zaznaczony na rys. nr 6 na „Cyfry”. Zapis ze zmienioną
nazwą kolumny powinien wyglądać następująco:
Rys. nr 6 – nazwa nowej kolumny, którą chcemy zmienić w pasku formuły
Jeśli nie pokazuje Ci się pasek formuły, możesz go uruchomić zaznaczając checkbox przy poleceniu Pasek formuły na karcie Widok (rys. nr 7).
Rys. nr 7 – uruchamianie paska formuły
Kiedy mamy już zduplikowaną kolumnę, zaznaczamy ją i rozwijamy polecenie Podziel kolumny (punkt 2 na rys. nr 8) z karty Strona główna (punkt 1), następnie wybieramy polecenie Według liczby znaków (punkt 3).
Rys. nr 8 – ścieżka dostępu do polecenia Według liczby znaków
Otworzy nam się okno Dzielenia kolumny według liczby znaków, gdzie w polu Liczba znaków wpisujemy wartość 1, następnie wybieramy w polu Podziel checkbox przy opcji Powtarzalne. Ważne jest abyśmy rozwinęli opcje zaawansowane, gdzie w polu Podziel na wybieramy dzielenie na Wiersze. Tak ustawione parametry dzielenia kolumny zatwierdzamy przyciskiem OK (rys. nr 9).
Rys. nr 9 – parametry dzielenia kolumny według liczby znaków
Otrzymamy podzielone dane, których fragment został przedstawiony na rys. nr 10.
Rys. nr 10 – fragment danych podzielonych według liczby znaków
W kolumnie Tekst mamy zduplikowane nasze oryginalne wartości, a w kolumnie Cyfry mamy podział na wszystkie pojedyncze znaki. Nasza kolumna Cyfry zaimportowała się domyślnie jako dane tekstowe, musimy zmienić typ danych. W tym celu klikamy na ikonkę ABC przy nazwie kolumny i z listy rozwijanej wybieramy typ Liczna całkowita (rys. nr 11).
Rys. nr 11 – zmiana typu danych w kolumnie Cyfry
Pojawi nam się okienko, w którym musimy potwierdzić decyzję o zmianie typu danych za pomocą przycisku Zamień bieżącą (rys. nr 12).
Rys. nr 12 – okno Zmień typ kolumny
Otrzymamy zmienione dane, gdzie w miejscach znaków tekstowych otrzymamy "Error", ponieważ Power Query nie rozpozna cyfry. Natomiast w miejscach, gdzie mieliśmy do czynienia ze spacją otrzymamy wartość "null" (rys. nr 13).
Rys. nr 13 – dane po zmianie typu danych z tekstu na liczby całkowite
Dzięki temu, że otrzymaliśmy błędy (error), w miejscach znaków tekstowych, możemy je łatwo usunąć. Rozwijamy polecenie Usuń wiersze (punkt 2 na rys. nr 14) z karty Strona główna (punkt 1), następnie wybieramy polecenie Usuń błędy (punkt 3).
Rys. nr 14 – ścieżka dostępu do polecenia Usuń błędy
Otrzymamy dane po usunięciu wierszy z błędami przedstawione na rys. nr 15 .
Rys. nr 15 – dane po usunięciu wierszy z błędami (error)
W kolejnym kroku chcielibyśmy pozbyć się wierszy z wartościami null, które powstały w miejsce spacji. W tym celu rozwijamy filtry (ikona trójkąta obok nazwy kolumny) i z podręcznego menu wybieramy polecenie Usuń puste (rys. nr 16).
Rys. nr 16 – polecenie Usuń puste
Otrzymamy dane bez pustych wierszy przedstawione na rys. nr 17.
Rys. nr 17 – dane po usunięciu pustych wierszy
Możemy zauważyć, że przy konkretnym tekście mamy tylko te wiersze, gdzie znalazły się cyfry, bez względu czy były one na początku, na końcu czy w środku tekstu. Otrzymaliśmy wyciągnięte wszystkie cyfry z tekstu, lecz nie są one jeszcze odpowiednio zgrupowane. Zaznaczamy kolumnę Tekst i wybieramy polecenie Grupowanie według z karty Strona główna (rys. nr 18).
Rys. nr 18 – polecenie Grupowanie według
Otworzy nam się okno Grupowania według, gdzie wybieramy typ grupowanie Podstawowy. W polu Nazwa nowej kolumny zmieniamy domyślną nazwę Liczność na Cyfry, następnie w polu Operacja wybieramy z listy rozwijanej Wszystkie wiersze. Tak ustawione parametry grupowania według zatwierdzamy przyciskiem OK (rys. nr 19).
Rys. nr 19 – parametry Grupowania według
Otrzymamy pogrupowane dane przedstawione na rys. nr 20.
Rys. nr 20 – pogrupowane dane
Pod skrótem
Table ukryta jest tabelka zarówno z danymi z kolumny Tekst jak i z kolumny
Cyfry. Nam zależy tylko na danych z kolumny cyfry, więc zrobimy małe
przekształcenie w pasku formuły. Zamiast znaku podkreślenia, który oznacza cały
wiersz wpiszemy nazwę kolumny. Zapis formuły powinien wyglądać następująco:
=Table.Group
(#"Przefiltrowano wiersze", {"Tekst"}, {{"Cyfry"
each [Cyfry], type table [Tekst=anynonnull, Cyfry=numer]}})
Otrzymamy dane
tylko z kolumny Cyfry ukryte pod słowem List w naszej tabeli przedstawione na
rys. nr 21.
Rys. nr 21 – dane pogrupowane jako lista
W pasku
formuły nadal mamy typ danych table oraz dodatkowe domyślne dane. Musimy
wprowadzić dodatkowe zmiany, między innymi zamienić table na list. Zapis
formuły powinien wyglądać następująco:
=Table.Group
(#"Przefiltrowano wiersze", {"Tekst"}, {{"Cyfry"
each [Cyfry], type list}})
Dzięki
takiej zmianie oprócz słowa list mamy zaznaczony odpowiednią ikonką typ danych
w kolumnie cyfry, co widać na rys. nr 22.
Rys. nr 22 – dane po zmianie typu na listopad
Dodatkowo dzięki takiej zmianie możemy bez problemu rozwinąć takie dane. W tym celu rozwijamy ikonkę ze strzałkami obok nazwy kolumny Cyfry i wybieramy polecenie Wyodrębnij wartości (rys. nr 23).
Rys. nr 23 – polecenie Wyodrębnij wartości
Otworzy nam się okno Wyodrębniania wartości z listy, gdzie z listy rozwijanej musimy wybrać Ogranicznik, jakiego Power Query ma użyć do połączenia danych. W naszym przykładzie, ze względu na to że mamy do czynienia z cyframi wybieramy Brak ogranicznika. Tak ustawione parametry wyodrębniania wartości zatwierdzamy przyciskiem OK (rys. nr 24).
Rys. nr 24 – parametry Wyodrębniania wartości z listy
Otrzymamy wyodrębnione cyfry w drugiej kolumnie przedstawione na rys. nr 25.
Rys. nr 25 – dane wyodrębnione z listy
Co istotne
mimo że wyciągnęliśmy z danych cyfry mają one przypisany typ danych Tekst.
Gdyby zależało nam żeby zmienić typ danych na liczby to stracilibyśmy zera
wiodące, czyli nie otrzymalibyśmy wszystkich cyfr z tekstu.
Tak
przygotowane dane możemy załadować do Excela za pomocą polecenia Zamknij i
załaduj do z karty Strona główna (rys. nr 26).
Rys. nr 26 – polecenie Zamknij i załaduj do
W Excelu otworzy nam się okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako Tabela oraz wskazujemy miejsce wstawienia danych, czyli Istniejący arkusz oraz wskazujemy konkretną komórkę. Tak ustawione parametry importowania danych zatwierdzamy przyciskiem OK (rys. nr 27).
Rys. nr 27 – okno Importowania danych
Otrzymamy dane wstawione do Excela przedstawione na rys. nr 28.
Rys. nr 28 – dane wstawione do Excela
Jak widać Power Query bez problemu poradził sobie z wyciągnięciem wszystkich cyfr z tekstu bez względu, gdzie się one znajdowały, czy były na końcu tekstu, czy na początku, czy też w środku.
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.
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.
W tym poście
zajmiemy się automatycznym wybieraniem kolumn z tabeli z listy.
W Power
Query odcinku 48 pokazywaliśmy jak ręcznie z dużej ilości danych wybrać
konkretne kolumny ( https://www.youtube.com/watch?v=sy0ZAca-0qw ). Pod tym postem otrzymaliśmy
pytanie od widza, jak to zrobić bazując na liście, czyli zakładamy że mamy
listę kolumn, które chcemy wyciągnąć z większej tabeli z danymi.
Zadanie to
wykonamy na przykładowych danych z rysunku nr 1.
rys. nr 1 — Przykładowe dane
Okazuje się to bardzo proste. Wystarczy że mamy listę kolumn, których potrzebujemy. Ustawiamy aktywną komórkę w obszarze tej listy i zaczytujemy dane do Power Query za pomocą polecenia Z tabeli/ zakresu z karty Dane (rys. nr 2).
rys. nr 2 — Z tabeli/zakresu
Otworzy nam
się Edytor zapytań Power Query z wczytaną tabelą tListaKolumn, która zawiera
wartości null w pustych komórkach. Usunięciem tych wartości zajmiemy się w
późniejszym etapie (rys. nr 3).
rys. nr 3 — Edytor zapytań Power Query z wczytaną tabelą tListaKolumn
Co istotne
nasze zapytanie zostało wczytane jako tabela, co widać w Liście zapytań
(zaznaczone czerwoną strzałką na rys. nr 4).
rys. nr 4 — dane wczytane do Power Query jako Tabela
Do dalszych działań potrzebujemy listy a nie tabeli. W tym celu wybieramy ikonkę polecenia Konwertuj na listę z karty Przekształć (rys. nr 5).
rys. nr 5 — polecenie Konwertuj na listę
Otrzymamy
przekonwertowane dane na listę przedstawione na rys. nr 6.
rys. nr 6 — Przekonwertowane dane na listę
Na tym etapie pracy wykonaliśmy wszystko, co było potrzebne z naszymi danymi. Tak przygotowane dane musimy załadować do Excela za pomocą polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 7).
rys. nr 7 — polecenie Zamknij i załaduj do
Otworzy się
nam okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako
Połączenie, czyli zaznaczamy checkbox przy opcji Utwórz tylko połączenie. Tak
ustawione opcje importowania danych zatwierdzamy przyciskiem OK (rys. nr 8).
rys. nr 8 — okno Importowania danych
W kolejnym etapie pobieramy właściwe połączenie, które w naszym przykładzie jest plikiem CSV. Wybieramy polecenie Z pliku tekstowego/CSV z karty Dane (rys. nr 9).
rys. nr 9 — polecenie Z pliku tekstowego /CSV
Otworzy się
nam okno Importowania danych, gdzie musimy znaleźć interesujący nas plik,
zaznaczyć go i zatwierdzić nasz wybór przyciskiem OK (rys. nr 10).
rys. nr 10 — Okno Importowania danych
W pliku CSV
mamy dużą ilość danych, z których chcemy wyciągnąć tylko kolumny z naszej
listy. Otworzy nam się okno z tymi danymi, gdzie klikamy przycisk Edytuj, aby
przejść do edycji tego zapytania (rys. nr 11).
rys. nr 11 — polecenie Edytuj zapytanie
Otworzy nam
się edytor zapytań Power Query z wczytanymi wszystkimi danymi przedstawiony na
rys. nr 12.
rys. nr 12 — Edytor zapytań z wczytanymi danymi z pliku CSV
Przykładowo możemy usunąć niepotrzebne kolumny, czyli zaznaczyć interesujące nas kolumny, następnie kliknąć prawym przyciskiem myszy na dowolną z nich i z podręcznego menu wybrać polecenie Usuń inne kolumny (rys. nr 13).
rys. nr 13 — polecenie Usuń inne kolumny
Nie ma znaczenia, które kolumny usuniemy, o ile Power Query użyje funkcji Table.SelectColumns, która ma w zapisie formuły listę kolumn, które chcemy zachować (rys. nr 14).
rys. nr 14 — argumenty funkcji Table.SelectColumns
W Power Query możemy się odwołać do innego zapytania, czyli w zapisie funkcji zastąpić listę kolumn, które zostały wpisane automatycznie, zapytaniem z listą, które przygotowaliśmy sobie na początku pracy (zapytanie tListaKolumn). Zapis funkcji powinien wyglądać następująco: (rys. nr 15)
rys. nr 15 — Zapis funkcji Table.SelectColumns z odwołaniem do zapytania z listą
Po
zatwierdzeniu formuły otrzymamy błąd, wynikający z wartości null na liście
(rys. nr 16). Power Query nie może przekonwertować wartości null.
rys. nr 16 — Błąd funkcji wynikający z wartości null na liście
Aby usunąć błąd przechodzimy do zapytania tListaKolumn, następnie co istotne w kroku poprzedzającym krok Lista wybór (krok Zmieniono typ), nakładamy na dane filtr. Rozwijamy ikonkę trójkąta w nagłówku kolumny z następnie wybieramy polecenie Usuń puste (rys. nr 17).
rys. nr 17 — Filtrowanie listy za pomocą polecenia Usuń puste
Pojawi nam się
komunikat Wstawianie kroku, w którym musimy zatwierdzić nasze działanie
przyciskiem Wstaw (rys. nr 18).
rys. nr 18 — Komunikat o wstawieniu nowego kroku
Dzięki temu
krokowi wartości null zostały usunięte z listy. Otrzymamy listę przedstawioną
na rys. nr 19.
rys. nr 19 — Odfiltrowana lista kolumn
Nasza lista
potrzebnych kolumn nie zawiera już wartości null i dzięki temu jak przejdziemy
do naszego zapytania FoodMart Klienci, błędu nie będzie. Wpisana wcześniej
formuła z odwołaniem do zapytania tListaKolumn zadziała prawidłowo i otrzymamy
dane zawierające kolumny z listy (rys. nr 20).
rys. nr 20 — prawidłowe działanie funkcji Table.SelectColumns
Tak przygotowane dane możemy załadować do Excela za pomocą polecenie Zamknij i załaduj do z karty Narzędzia główne (analogicznie jak na rys. nr 7).
Otworzy nam
się okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako
Tabela, następnie wskazujemy miejsce wstawienia danych – Istniejący arkusz oraz
wskazujemy konkretną komórkę. Tak ustawione parametry wstawienia danych do
Excela zatwierdzamy przyciskiem OK (rys. nr 21).
rys. nr 21 — Okno importowania danych
Otrzymamy
dane wczytane do Excela zawierające tylko kolumny z listy, przedstawione na
rys. nr 22.
rys. nr 22 — Dane zaimportowane do Excela
Tabela wczytana z Power Query działa dynamicznie. Przykładowo możemy zmienić nasze dane w liście kolumn (zmieniamy kolumnę Nazwisko na Adres i dokładamy nową kolumnę Liczba dzieci), następnie odświeżyć dane z Power Query klikając prawym przyciskiem myszy na dowolną komórkę z obszaru tabeli i wybierając polecenie Odśwież (rys. nr 23).
rys. nr 23 — polecenie Odśwież
Po
odświeżeniu danych otrzymamy zmienione dane, w których kolumna Nazwisko została
zastąpiona kolumną Adres i została dołożona nowa kolumna Liczba dzieci (rys. nr 24).
rys. nr 24 — automatycznie zaktualizowane dane
Podsumowując
stworzyliśmy tabelę z kolumnami z listy, która działa dynamicznie i zawiera
kolumny, które aktualnie są na liście.
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.
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.
