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, oni se izdvajaju 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 uz gomilu primjera iz života opisao kako možete dodati podršku za regularne izraze u Excel pomoću jednostavnih makroa – ako niste pročitali ovaj članak, toplo preporučujem da ga pročitate prije nego što nastavite. Otkrićete puno novih stvari, garantujem 🙂
Međutim, ostaje otvoreno pitanje – kako dodati mogućnost korištenja regularnih izraza u Power Queryju? Power Query je, naravno, dobar sam po sebi i može dosta toga da uradi sa tekstom (rezanje, lepljenje, čišćenje, itd.), ali kada biste ga mogli ukrstiti snagom regularnih izraza, to bi bila samo bomba.
Nažalost, u Power Queryju ne postoje ugrađene funkcije za rad sa RegExps-ima, a službena Microsoftova pomoć i tehnička podrška odgovaraju na ovo pitanje negativno. Međutim, postoji način zaobići ovo ograničenje 🙂
Suština metode
Glavna ideja je jednostavna za sramotu.
Na listi ugrađenih Power Query mogućnosti nalazi se funkcija Web.Page. Opis ove funkcije na službenoj Microsoft stranici za pomoć je izuzetno sažet:
Prevedeno, ovo bi bilo: „Vraća sadržaj HTML dokumenta raščlanjen na njegove sastavne strukture, kao i prikaz cijelog dokumenta i njegovog tijela nakon što su oznake uklonjene.“ Tako-tako opis, iskreno.
Obično se ova funkcija koristi prilikom uvoza podataka s weba i automatski se zamjenjuje, na primjer, kada odaberemo na kartici podaci naredba Sa interneta (Podaci — sa weba). Dajemo funkciji web stranicu kao argument, a ona nam vraća njen sadržaj u obliku tabela, nakon što je prethodno obrisala sve oznake.
Ono što pomoć NE kaže je da pored HTML jezika za označavanje Funkcija Web.Page 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, moraćemo da unesemo 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 sa regularnim izrazima u JavaScript-u (na primjer, jedan, dva).
Ukratko i pojednostavljeno, JavaScript kod će izgledati ovako:
Ovdje:
- var str = 'Plati račune 123 i 789 za kobasicu'; – kreirati varijablu str i dodijelite mu izvorni tekst koji ćemo analizirati.
- var obrazac = /d+/gi; – kreirajte regularni izraz i stavite ga u varijablu obrazac.
Izraz počinje kosom crtom (/).
Sam izraz ovdje je, na primjer d+ označava bilo koji niz cifara.
Kroz razlomak nakon izraza postoje dodatni parametri pretraživanja (modifikatori) – mogu se specificirati bilo kojim redoslijedom:
- g – znači globalna pretraga, odnosno nakon pronalaženja podudaranja ne treba stati, već nastaviti pretragu do kraja teksta. Ako ovaj modifikator nije postavljen, onda ć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 redova)
- var rezultat = str.match(pattern).join(';'); – izvrši pretragu u izvornom tekstu (str) prema datom regularnom izrazu (obrazac) i stavite rezultate u varijablu rezultat, spajajući ih sa tačkom i zarezom pomoću naredbe Pridruži se
- document.write(rezultat); – prikazati sadržaj varijable rezultata
Takođe imajte na umu da su tekstualni nizovi (isključujući regularne izraze) u JavaScript-u zatvoreni u apostrofe, a ne navodnike kao što su u Power Queryju ili VBA.
Na izlazu, ova skripta će nam kao rezultat dati sve brojeve pronađene u izvornom tekstu:
123, 789
Kratki kurs JavaScripta je završen, hvala svima. Nadam se da ste shvatili 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 – Dobijte podatke / Kreirajte zahtjev – Iz drugih izvora – Prazan zahtjev (Podaci — Dobijte podatke / Novi upit — Iz drugih izvora — Prazan upit). Ako imate staru verziju Excel 2010-2013 i Power Query nemate ugrađen, već je instaliran kao poseban dodatak, onda će sve to biti na kartici power queryI ne podaci.
2. U prazan prozor uređivača upita koji se otvori, u desnom panelu, 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 – originalni tekst koji se analizira, regularni izraz – obrazac regularnog izraza, delim — znak za razgraničenje za prikaz rezultata.
Zatim pozivamo funkciju Web.Page, formirajući JavaScript kod opisan gore u svom argumentu. Zalijepimo i zamjenjujemo naše promjenljive argumente u kod.
Fragment:
[Podaci]{0}[Djeca]{0}[Djeca]{1}[Tekst]{0}
… je potrebno da “upadnemo” u tabelu sa rezultatima koji su nam potrebni. Poenta je da funkcija Web.Page kao rezultat, proizvodi nekoliko ugniježđenih tabela koje ponavljaju strukturu web stranice. Bez ovog dijela M-koda, naša funkcija bi ispisala ovo:
… i morali bismo kliknuti na riječ nekoliko puta sto, sukcesivno "propadajući" u podređene ugniježđene tabele u kolonama djeca:
Umjesto svih ovih citata, u kodu naše funkcije odmah ukazujemo koja je ugniježđena tablica i stupac (tekst) trebamo.
Evo, zapravo, svih tajni. Ostaje pritisnuti dugme završiti u prozoru napredni editor, gdje smo ubacili naš kod, a vi možete nastaviti do najukusnijeg – isprobajte našu funkciju na poslu.
Evo nekoliko primjera sjemena.
Primjer 1. Preuzimanje broja računa i datuma iz opisa plaćanja
Imamo bankovni izvod sa opisom (svrha) plaćanja, gde je potrebno da izvučete brojeve i datume plaćenih faktura u posebne kolone:
Učitavamo tabelu u Power Query na standardni način Podaci – iz tabele/opseg (Podaci — od Tsposoban/Ranđeo).
Zatim dodajemo izračunatu kolonu sa našom funkcijom via Dodaj kolonu – Pozovite prilagođenu funkciju (Dodaj kolonu — Pozovite prilagođenu funkciju) i unesite njegove argumente:
Kao regularni izraz (argument regularni izraz) šablon koji koristimo:
(d{3,5}|d{2}.d{2}.d{4})
… prevedeno na ljudski jezik značenje:
brojevi od 3 do 5 cifara (brojevi računa)
or
fragmenti oblika “2-bitni broj – tačka – 2-bitni broj – tačka – 4-bitni broj”, odnosno datumi u obliku DD.MM.GGGG.
Kao znak za razdvajanje (argument delim) unesite tačku i zarez.
Posle klika na OK naša magična funkcija analizira sve početne podatke prema našem regularnom izrazu i formira kolonu za nas s pronađenim brojevima i datumima faktura:
Ostaje da ga odvojite tačkom i zarezom koristeći naredbu Početna — Podijeljena kolona — Po graničniku (Početna — Podijeljeni stupac — Po razdjelniku) i dobijamo šta smo želeli:
Ljepota!
Primjer 2: Izdvojite adrese e-pošte iz teksta
Pretpostavimo da imamo sljedeću tabelu 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 iu prethodnom primjeru, učitavamo tabelu u Power Query na standardni način putem Podaci – iz tabele/opseg (Podaci — od Tsposoban/Ranđeo).
Zatim dodajemo izračunatu kolonu sa našom funkcijom via Dodaj kolonu – Pozovite prilagođenu funkciju (Dodaj kolonu — Pozovite prilagođenu funkciju) i unesite njegove argumente:
Raščlanjivanje adresa e-pošte je teži zadatak i postoji gomila regularnih izraza različitog stepena noćne more koji ga mogu riješiti. Koristio sam jednu od jednostavnih opcija – nije idealna, ali u većini slučajeva prilično funkcionira:
[w|.|-]*@w*.[w|.]*
Kao separator (delim) možete unijeti tačku i razmak.
Kliknite na OK i dobijamo kolonu sa adresama e-pošte izvučene iz originalnog teksta "kaša":
Magija!
PS
Kako se kaže: "Nema tako dobrog što se ne može učiniti još boljim." Power Query je kul sam po sebi, a u kombinaciji s regularnim izrazima daje nam potpuno nerealnu 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 svi gore navedeni plesovi s tamburom će postati stvar prošlosti. Pa, za sada, da.
Takođe želim da dodam da je zgodno igrati se regularnim izrazima na sajtu https://regexr.com/ – direktno u onlajn uređivaču. Tamo u sekciji Obrasci zajednice Postoji ogroman broj gotovih regularnih sezona za sve prilike. Eksperimentirajte – sva moć regularnih izraza sada vam je na usluzi u Power Queryju!
- Šta su regularni izrazi (RegExp) i kako ih koristiti u Excelu
- Pretraživanje nejasnog teksta u Power Queryju
- Sastavljanje tabela iz različitih datoteka pomoću Power Queryja