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).
W celu zaczytania danych do Power Query wybieramy polecenie Z tabeli/zakresu z karty Dane (rys. nr 2).
Otworzy nam się edytor zapytań Power Query z wczytaną tabelą z przykładowymi danymi przedstawiony na rys. nr 3.
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).
Otrzymamy dane ze zduplikowaną kolumną przedstawione na rys. nr 5.
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:
=Table.DuplikateColumn(#"Zmieniono typ", "Tekst", "Cyfry")
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).
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).
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).
Otrzymamy podzielone dane, których fragment został przedstawiony na rys. nr 10.
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).
Pojawi nam się okienko, w którym musimy potwierdzić decyzję o zmianie typu danych za pomocą przycisku Zamień bieżącą (rys. nr 12).
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).
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).
Otrzymamy dane po usunięciu wierszy z błędami przedstawione na rys. nr 15 .
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).
Otrzymamy dane bez pustych wierszy przedstawione na rys. nr 17.
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).
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).
Otrzymamy pogrupowane dane przedstawione na rys. nr 20.
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.
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.
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).
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).
Otrzymamy wyodrębnione cyfry w drugiej kolumnie przedstawione na rys. nr 25.
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).
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).
Otrzymamy dane wstawione do Excela przedstawione na rys. nr 28.
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.
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
