Spis treści
Jeśli jesteś choć trochę zaznajomiony z wyrażeniami regularnymi, nie musisz ich reklamować. Jeśli nie jesteś do końca w temacie, to wyrażenia regularne (Wyrażenia regularne = RegExp = „regexps” = „regular”) to język, w którym za pomocą znaków specjalnych i reguł wyszukiwane są niezbędne podciągi w tekście, są one wyodrębniane lub zastąpiony innym tekstem . To bardzo potężne i piękne narzędzie, o rząd wielkości przewyższające wszystkie inne sposoby pracy z tekstem.
Opisałem już szczegółowo i z kilkoma przykładami z życia, w jaki sposób można dodać obsługę wyrażeń regularnych do Excela za pomocą prostych makr – jeśli nie czytałeś tego artykułu, bardzo polecam go przeczytać przed kontynuowaniem. Odkryjesz wiele nowych rzeczy, gwarantuję 🙂
Pytanie jednak pozostaje otwarte – jak dodać możliwość używania wyrażeń regularnych w Power Query? Oczywiście dodatek Power Query sam w sobie jest dobry i może wiele zrobić z tekstem (wycinanie, klejenie, czyszczenie itp.), ale gdybyś mógł go skrzyżować z potęgą wyrażeń regularnych, byłaby to tylko bomba.
Niestety nie ma wbudowanych funkcji do pracy z RegExps w Power Query, a oficjalna pomoc i wsparcie techniczne firmy Microsoft odpowiadają na to pytanie przecząco. Istnieje jednak sposób na obejście tego ograniczenia 🙂
Istota metody
Główna idea jest prosta do zhańbienia.
Na liście wbudowanych funkcji Power Query znajduje się funkcja Strona internetowa. Opis tej funkcji na oficjalnej stronie pomocy Microsoft jest niezwykle zwięzły:
W tłumaczeniu byłoby to: „Zwraca zawartość dokumentu HTML z podziałem na jego struktury składowe, a także reprezentację całego dokumentu i jego treści po usunięciu znaczników”. Tak sobie opis, szczerze mówiąc.
Zwykle ta funkcja jest używana podczas importowania danych z sieci i jest automatycznie zastępowana, na przykład, gdy wybieramy na karcie Dane Command Z Internetu (Dane — z internetu). Jako argument podajemy funkcję stronę WWW, która zwraca nam swoją zawartość w postaci tabel, po uprzednim wyczyszczeniu wszystkich tagów.
Pomoc NIE mówi, że oprócz języka znaczników HTML funkcjonować Strona internetowa obsługuje skrypty JavaScript, który jest obecnie wszechobecny na stronach internetowych. Z kolei JavaScript zawsze mógł pracować z wyrażeniami regularnymi i ma wbudowane funkcje RegExps! Aby więc zaimplementować wyrażenia regularne w dodatku Power Query, będziemy musieli podać funkcje Web.Page jako argument do małego programu JavaScript, który wykona całą pracę dla dodatku Power Query.
Jak to wygląda w czystym JavaScript
Istnieje wiele szczegółowych samouczków dotyczących pracy z wyrażeniami regularnymi w JavaScript w Internecie (na przykład jeden, dwa).
W skrócie i uproszczony kod JavaScript będzie wyglądał tak:
Tutaj:
- var str = 'Zapłać rachunki 123 i 789 za kiełbasę'; – utwórz zmienną str i przypisz mu tekst źródłowy, który będziemy analizować.
- var wzorzec = /d+/gi; – utwórz wyrażenie regularne i umieść je w zmiennej wzorzec.
Wyrażenie zaczyna się od ukośnika (/).
Samo wyrażenie tutaj, na przykład, to d+ oznacza dowolny ciąg cyfr.
Poprzez ułamek po wyrażeniu znajdują się dodatkowe parametry wyszukiwania (modyfikatory) – można je określić w dowolnej kolejności:
- g – oznacza wyszukiwanie globalne, tzn. po znalezieniu dopasowania nie należy przerywać, ale kontynuować wyszukiwanie do końca tekstu. Jeśli ten modyfikator nie jest ustawiony, nasz skrypt zwróci tylko pierwsze dopasowanie (123)
- i – wyszukiwanie bez względu na wielkość liter
- m – wyszukiwanie wielowierszowe (stosowane, gdy tekst źródłowy jest podzielony na kilka wierszy)
- var wynik = str.match(wzór).join(';'); – przeprowadzić wyszukiwanie w tekście źródłowym (str) przez podane wyrażenie regularne (wzorzec) i umieść wyniki w zmiennej dalsze, łącząc je ze średnikiem za pomocą polecenia przystąpić
- dokument.zapis(wynik); – wyświetl zawartość zmiennej wynikowej
Zwróć też uwagę, że ciągi tekstowe (z wyjątkiem wyrażeń regularnych) w JavaScript są ujęte w apostrofy, a nie w cudzysłowy, jak w Power Query lub VBA.
Na wyjściu ten skrypt poda nam w rezultacie wszystkie liczby znalezione w tekście źródłowym:
123, 789
Krótki kurs JavaScript się skończył, dziękuję wszystkim. Mam nadzieję, że rozumiesz logikę 🙂
Pozostaje przenieść tę konstrukcję do dodatku Power Query.
Funkcja wyszukiwania i wyodrębniania tekstu według wyrażenia regularnego w Power Query
Wykonujemy następujące czynności:
1. Otwórz Excel i utwórz nowe puste Power Query na karcie Dane – Pobierz dane / Utwórz żądanie – Z innych źródeł – Puste żądanie (Dane — Pobierz dane / Nowe zapytanie — Z innych źródeł — Puste zapytanie). Jeśli masz starą wersję programu Excel 2010-2013 i Power Query, której nie masz wbudowanego, ale został on zainstalowany jako osobny dodatek, wszystko to będzie na karcie Zapytanie o mocI nie Dane.
2. W pustym oknie edytora zapytań, które się otworzy, w prawym panelu od razu wpisz nazwę naszej przyszłej funkcji (np. Wyciąg fxRegExp)
3. Przejdźmy do zakładki Widok – Edytor zaawansowany (Widok — Edytor zaawansowany), kasujemy cały kod M pustego żądania i wklejamy tam kod naszej superfunkcji:
Uważaj na swoje ręce:
W pierwszym wierszu mówimy, że nasza funkcja będzie miała trzy argumenty tekstowe: txt – analizowany tekst oryginalny, regex – wzorzec wyrażenia regularnego, rozgraniczać — znak ogranicznika do wyświetlania wyników.
Następnie wywołujemy funkcję Strona internetowa, tworząc w swoim argumencie kod JavaScript opisany powyżej. Wklejamy i podstawiamy nasze zmienne argumenty do kodu.
Fragment:
[Dane]{0}[Dzieci]{0}[Dzieci]{1}[Tekst]{0}
… jest potrzebne, aby „wpaść” do tabeli z wynikami, których potrzebujemy. Chodzi o to, że funkcja Strona internetowa w rezultacie tworzy kilka zagnieżdżonych tabel, które powtarzają strukturę strony internetowej. Bez tego fragmentu kodu M nasza funkcja wypisze to:
… i musielibyśmy klikać w słowo kilka razy Stół, sukcesywnie „wpadając” do podrzędnych tabel zagnieżdżonych w kolumnach Dzieci:
Zamiast całego tego cytatu od razu wskazujemy w kodzie naszej funkcji, która zagnieżdżona tabela i kolumna (Tekst) potrzebujemy.
Oto w rzeczywistości wszystkie sekrety. Pozostaje nacisnąć przycisk koniec w oknie zaawansowany edytor, gdzie wstawiliśmy nasz kod, i możesz przejść do najsmaczniejszego – wypróbuj naszą funkcję w pracy.
Oto kilka przykładów nasion.
Przykład 1. Pobranie numeru rachunku i daty z opisu płatności
Posiadamy wyciąg bankowy z opisem (celem) płatności, w którym do osobnych kolumn należy wysunąć numery i daty zapłaconych faktur:
Tabelę ładujemy do Power Query w standardowy sposób Dane — z tabeli/zakresu (Dane — z Tzdolny/Ranioł).
Następnie dodajemy obliczoną kolumnę z naszą funkcją via Dodaj kolumnę – Wywołaj funkcję niestandardową (Dodaj kolumnę — Wywołaj funkcję niestandardową) i wprowadź jego argumenty:
Jako wyrażenie regularne (argument regex) szablon, którego używamy:
(d{3,5}|d{2}.d{2}.d{4})
… przetłumaczone na ludzki język znaczenie:
numery od 3 do 5 cyfr (numery rachunków)
or
fragmenty postaci „liczba 2-bitowa – punkt – liczba 2-bitowa – punkt – liczba 4-bitowa”, czyli daty w formacie DD.MM.RRRR.
Jako znak rozdzielający (argument rozgraniczać) wpisz średnik.
Po kliknięciu OK nasza magiczna funkcja analizuje wszystkie dane początkowe zgodnie z naszym wyrażeniem regularnym i tworzy dla nas kolumnę ze znalezionymi numerami i datami faktur:
Pozostaje oddzielić go średnikiem za pomocą polecenia Strona główna — Podziel kolumnę — Według ogranicznika (Strona główna — Podziel kolumnę — Według separatora) i dostajemy to, czego chcieliśmy:
Piękno!
Przykład 2: Wyodrębnij adresy e-mail z tekstu
Załóżmy, że mamy poniższą tabelę jako dane początkowe:
… skąd musimy wyciągnąć znalezione tam adresy e-mail (dla jasności zaznaczyłem je w tekście na czerwono).
Podobnie jak w poprzednim przykładzie, ładujemy tabelę do Power Query w standardowy sposób przez Dane — z tabeli/zakresu (Dane — z Tzdolny/Ranioł).
Następnie dodajemy obliczoną kolumnę z naszą funkcją via Dodaj kolumnę – Wywołaj funkcję niestandardową (Dodaj kolumnę — Wywołaj funkcję niestandardową) i wprowadź jego argumenty:
Parsowanie adresów e-mail jest trudniejszym zadaniem i istnieje kilka wyrażeń regularnych o różnym stopniu koszmaru, które można rozwiązać. Użyłem jednej z prostych opcji – nie idealnej, ale w większości przypadków całkiem sprawnej:
[w|.|-]*@w*.[w|.]*
jako separator (rozgraniczać) możesz wpisać średnik i spację.
Kliknij na OK i otrzymujemy kolumnę z adresami e-mail wyodrębnionymi z oryginalnego tekstu „owsianka”:
Magia!
PS
Jak mówi przysłowie: „Nie ma takiej dobrej rzeczy, której nie dałoby się jeszcze ulepszyć”. Dodatek Power Query sam w sobie jest fajny, a w połączeniu z wyrażeniami regularnymi daje nam całkowicie nierealistyczną moc i elastyczność w przetwarzaniu dowolnych danych tekstowych. Mam nadzieję, że Microsoft doda kiedyś obsługę RegExp w aktualizacjach Power Query i Power BI, a wszystkie powyższe tańce z tamburynem staną się przeszłością. Cóż, na razie tak.
Dodam też, że wygodnie jest bawić się wyrażeniami regularnymi na stronie https://regexr.com/ – bezpośrednio w edytorze online. Tam w dziale Wzorce społeczności Istnieje ogromna liczba gotowych sezonów regularnych na każdą okazję. Eksperyment — cała moc wyrażeń regularnych jest teraz do Twojej dyspozycji w dodatku Power Query!
- Czym są wyrażenia regularne (RegExp) i jak ich używać w Excelu
- Wyszukiwanie rozmyte w Power Query
- Składanie tabel z różnych plików za pomocą dodatku Power Query