Sadržaj
Ako ste barem malo upoznati s regularnim izrazima, onda ih ne trebate reklamirati. Ako niste baš u temi, onda su regularni izrazi (Regular Expressions = RegExp = “regexps” = “regulars”) jezik u kojem se pomoću posebnih znakova i pravila traže potrebni podnizovi u tekstu, izvlače se ili zamijenjen drugim tekstom. Ovo je vrlo moćan i lijep alat, red veličine superiorniji od svih drugih načina rada s tekstom.
Već sam detaljno i s hrpom primjera iz života opisao kako Excelu pomoću jednostavnih makronaredbi možete dodati podršku za regularne izraze – ako niste pročitali ovaj članak, toplo vam preporučujem da ga pročitate prije nastavka. Otkrit ćete puno novih stvari, garantiram 🙂
Međutim, ostaje otvoreno pitanje – kako dodati mogućnost korištenja regularnih izraza u Power Query? Power Query je, naravno, dobar sam po sebi i može puno s tekstom (rezati, lijepiti, čistiti itd.), ali kada biste ga mogli ukrstiti sa snagom regularnih izraza, bila bi to prava bomba.
Nažalost, nema ugrađenih funkcija za rad s RegExps-om u Power Queryju, a službena Microsoftova pomoć i tehnička podrška negativno odgovaraju na ovo pitanje. Međutim, postoji način da se ovo ograničenje zaobiđe 🙂
Bit metode
Glavna ideja je jednostavna za osramotiti.
Na popisu ugrađenih mogućnosti Power Queryja nalazi se funkcija Web.stranica. Opis ove funkcije na službenoj Microsoftovoj stranici za pomoć vrlo je koncizan:
Prevedeno, ovo bi bilo: "Vraća sadržaj HTML dokumenta rastavljen na strukture njegovih komponenti, kao i prikaz cijelog dokumenta i njegovog tijela nakon što su oznake uklonjene." Kako-taki opis, iskreno.
Obično se ova funkcija koristi pri uvozu podataka s weba i automatski se zamjenjuje, na primjer, kada odaberemo na kartici Datum naredba S interneta (Podaci — s weba). Funkciji dajemo web stranicu kao argument, a ona nam vraća njezin sadržaj u obliku tablica, prethodno počistivši sve oznake.
Ono što pomoć NE kaže je da uz označni jezik HTML funkcija Web.stranica podržava JavaScript skripte, koji je sada sveprisutan na web stranicama na internetu. A JavaScript je zauzvrat uvijek mogao raditi s regularnim izrazima i ima ugrađene funkcije za RegExps! Dakle, da bismo implementirali regularne izraze u Power Query, morat ćemo dati funkcije Web.Page kao argument malom JavaScript programu koji će obaviti sav posao za Power Query.
Kako to izgleda u čistom JavaScriptu
Na internetu postoji mnogo detaljnih tutorijala o radu s regularnim izrazima u JavaScriptu (na primjer, jedan, dva).
Ukratko i pojednostavljeno, JavaScript kod će izgledati ovako:
Ovdje:
- var str = 'Plati račune 123 i 789 za kobasicu'; – stvoriti varijablu str te mu dodijeliti izvorni tekst koji ćemo analizirati.
- var uzorak = /d+/gi; – stvoriti regularni izraz i staviti ga u varijablu uzorak.
Izraz počinje kosom crtom (/).
Sam izraz ovdje je, na primjer d+ označava bilo koji niz znamenki.
Kroz razlomak iza izraza nalaze se dodatni parametri pretraživanja (modifikatori) – mogu se navesti bilo kojim redoslijedom:
- g – znači globalno pretraživanje, tj. nakon pronalaska podudaranja ne treba stati, već nastaviti pretraživanje do kraja teksta. Ako ovaj modifikator nije postavljen, tada će naša skripta vratiti samo prvo podudaranje (123)
- i – pretraživanje bez obzira na velika i mala slova
- m – pretraživanje u više redaka (koristi se kada je izvorni tekst podijeljen u nekoliko redaka)
- var rezultat = str.match(pattern).join(';'); – pretraži izvorni tekst (str) zadanim regularnim izrazom (uzorak) i stavite rezultate u varijablu rezultirati, spajajući ih točkom i zarezom pomoću naredbe pridruži
- document.write(rezultat); – prikazati sadržaj varijable rezultata
Također imajte na umu da su tekstualni nizovi (isključujući regularne izraze) u JavaScriptu zatvoreni apostrofima, a ne navodnicima kao što je to slučaj u Power Queryju ili VBA.
Na izlazu, ova skripta će nam kao rezultat dati sve brojeve pronađene u izvornom tekstu:
123, 789
Kratki tečaj JavaScripta je gotov, hvala svima. Nadam se da shvaćate logiku 🙂
Ostaje prenijeti ovu konstrukciju u Power Query.
Funkcija pretraživanja i izdvajanja teksta pomoću regularnog izraza u Power Queryju
Radimo sljedeće:
1. Otvorite Excel i kreirajte novi prazan Power Query na kartici Podaci – Dohvati podatke / Kreiraj zahtjev – Iz drugih izvora – Prazan zahtjev (Podaci — Dobivanje podataka / Novi upit — Iz drugih izvora — Prazan upit). Ako imate staru verziju Excela 2010-2013 i Power Query nemate ugrađen, već je instaliran kao zaseban dodatak, onda će sve ovo biti na kartici Upit snageA nije Datum.
2. U praznom prozoru uređivača upita koji se otvori, na desnoj ploči, odmah unesite naziv naše buduće funkcije (npr. fxRegExpExtract)
3. Idemo na karticu Pogled – napredni uređivač (Prikaz — napredni uređivač), brišemo cijeli M-kod praznog zahtjeva i tamo zalijepimo kod naše superfunkcije:
Pazi na ruke:
U prvom redu kažemo da će naša funkcija imati tri tekstualna argumenta: txt – izvorni tekst koji se analizira, Regularni izraz – uzorak regularnog izraza, razgraničiti — znak razgraničenja za prikaz rezultata.
Zatim pozivamo funkciju Web.stranica, formirajući JavaScript kod opisan gore u svom argumentu. Zalijepimo i zamijenimo naše varijable argumente u kodu.
Fragment:
[Podaci]{0}[Djeca]{0}[Djeca]{1}[Tekst]{0}
… potrebno je da „upadnemo“ u tablicu s rezultatima koji su nam potrebni. Stvar je u tome da funkcija Web.stranica kao rezultat, proizvodi nekoliko ugniježđenih tablica koje ponavljaju strukturu web stranice. Bez ovog dijela M-koda, naša funkcija bi ispisala ovo:
... i morali bismo pritisnuti riječ nekoliko puta Stol, sukcesivno "propadajući" u podređene ugniježđene tablice u stupcima Djeca:
Umjesto svih ovih citata, u kodu naše funkcije odmah označavamo koja je ugniježđena tablica i stupac (Tekst) trebamo.
Ovdje su, zapravo, sve tajne. Ostaje pritisnuti tipku završiti u prozoru napredni urednik, gdje smo umetnuli naš kod, a vi možete prijeći na najukusnije – isprobajte našu funkciju na poslu.
Evo nekoliko primjera sjemena.
Primjer 1. Dohvaćanje broja računa i datuma iz opisa plaćanja
Imamo bankovni izvadak s opisom (svrhom) plaćanja, gdje je potrebno izvući brojeve i datume plaćenih računa u posebne stupce:
Učitavamo tablicu u Power Query na standardni način Podaci – iz tablice/raspona (Podaci - od Tsposoban/Ranđeo).
Zatim dodajemo izračunati stupac s našom funkcijom via Dodaj stupac – Pozovi prilagođenu funkciju (Dodaj stupac — pozovi prilagođenu funkciju) i unesite njegove argumente:
Kao regularni izraz (argument Regularni izraz) predložak koji koristimo:
(d{3,5}|d{2}.d{2}.d{4})
… prevedeno na ljudski jezik znači:
brojevi od 3 do 5 znamenki (brojevi računa)
or
fragmenti oblika "2-bitni broj - točka - 2-bitni broj - točka - 4-bitni broj", odnosno datume oblika DD.MM.GGGG.
Kao znak za razdvajanje (argument razgraničiti) unesite točku i zarez.
Nakon što kliknete OK naša čarobna funkcija analizira sve početne podatke prema našem regularnom izrazu i formira stupac za nas s pronađenim brojevima i datumima faktura:
Ostaje ga odvojiti točkom i zarezom pomoću naredbe Početna — Podijeli stupac — Po razdjelniku (Početna — Podijeli stupac — Prema razdjelniku) i dobili smo što smo htjeli:
Ljepota!
Primjer 2: izdvajanje adresa e-pošte iz teksta
Pretpostavimo da imamo sljedeću tablicu kao početne podatke:
… odakle trebamo izvući adrese e-pošte koje se tamo nalaze (radi jasnoće, označio sam ih crvenom bojom u tekstu).
Kao i u prethodnom primjeru, učitavamo tablicu u Power Query na standardni način putem Podaci – iz tablice/raspona (Podaci - od Tsposoban/Ranđeo).
Zatim dodajemo izračunati stupac s našom funkcijom via Dodaj stupac – Pozovi prilagođenu funkciju (Dodaj stupac — pozovi prilagođenu funkciju) i unesite njegove argumente:
Raščlanjivanje adresa e-pošte je teži zadatak i postoji hrpa regularnih izraza različitih stupnjeva noćne more za njegovo rješavanje. Koristio sam jednu od jednostavnih opcija - nije idealna, ali prilično funkcionira u većini slučajeva:
[w|.|-]*@w*.[w|.]*
Kao separator (razgraničiti) možete unijeti točku-zarez i razmak.
Kliknite na OK i dobivamo stupac s e-mail adresama izdvojenim iz originalnog teksta “porridge”:
Magija!
PS
Kako se kaže: “Nema tako dobre stvari koja se ne bi mogla učiniti još boljom.” Power Query je cool sam po sebi, a u kombinaciji s regularnim izrazima daje nam potpuno nestvarnu snagu i fleksibilnost u obradi bilo kakvih tekstualnih podataka. Nadam se da će Microsoft jednog dana dodati RegExp podršku u Power Query i Power BI ažuriranja i da će svi gore navedeni plesovi uz tamburicu postati prošlost. Pa, za sada, da.
Također želim dodati da je zgodno igrati se s regularnim izrazima na web mjestu https://regexr.com/ – upravo u mrežnom uređivaču. Tamo u odjeljku Obrasci zajednice Postoji ogroman broj gotovih običnih sezona za sve prilike. Eksperimentirajte – sva moć regularnih izraza sada vam je na usluzi u Power Queryju!
- Što su regularni izrazi (RegExp) i kako ih koristiti u Excelu
- Pretraživanje neizrazitog teksta u Power Queryju
- Sastavljanje tablica iz različitih datoteka pomoću Power Queryja