vsebina
Če ste vsaj malo seznanjeni z regularnimi izrazi, vam jih ni treba oglaševati. Če niste povsem v temi, potem so regularni izrazi (Regular Expressions = RegExp = “regexps” = “regulars”) jezik, kjer se s posebnimi znaki in pravili iščejo potrebni podnizi v besedilu, se ekstrahirajo ali zamenjati z drugim besedilom. To je zelo zmogljivo in lepo orodje, ki je za red velikosti boljše od vseh drugih načinov dela z besedilom.
Podrobno in s kopico primerov iz življenja sem že opisal, kako lahko Excelu dodate podporo za regularne izraze s preprostimi makri – če še niste prebrali tega članka, toplo priporočam, da ga preberete, preden nadaljujete. Odkrili boste veliko novega, garantiram 🙂
Odprto pa ostaja vprašanje – kako v Power Query dodati možnost uporabe regularnih izrazov? Power Query je seveda dober sam po sebi in zmore marsikaj z besedilom (rezanje, lepljenje, čiščenje itd.), a če bi ga križali z močjo regularnih izrazov, bi bila prava bomba.
Na žalost v Power Query ni vgrajenih funkcij za delo z RegExps, uradna Microsoftova pomoč in tehnična podpora pa na to vprašanje odgovarjata negativno. Vendar pa obstaja način, kako zaobiti to omejitev 🙂
Bistvo metode
Glavna ideja je preprosto osramotiti.
Na seznamu vgrajenih zmogljivosti Power Query je funkcija Spletna stran. Opis te funkcije na uradni Microsoftovi strani za pomoč je izjemno jedrnat:
Prevedeno bi to bilo: "Vrne vsebino dokumenta HTML, razčlenjeno na njegove sestavne strukture, kot tudi predstavitev celotnega dokumenta in njegovega telesa po odstranitvi oznak." Tako-tako opis, odkrito.
Običajno se ta funkcija uporablja pri uvozu podatkov iz spleta in se samodejno nadomesti, na primer, ko izberemo na zavihku datum Ukaz Iz interneta (Podatki — iz spleta). Funkciji damo kot argument spletno stran, ona pa nam njeno vsebino vrne v obliki tabel, pri čemer je predhodno počistila vse oznake.
V pomoči NE piše, da poleg označevalnega jezika HTML funkcija Spletna stran podpira JavaScript skripte, ki je zdaj vseprisoten na spletnih mestih v internetu. JavaScript pa je vedno lahko deloval z regularnimi izrazi in ima vgrajene funkcije za RegExps! Če želimo implementirati regularne izraze v Power Query, bomo morali vnesti funkcije Web.Page kot argument majhnemu programu JavaScript, ki bo opravil vse delo za Power Query.
Kako je videti v čistem JavaScriptu
Na internetu je veliko podrobnih vadnic o delu z regularnimi izrazi v JavaScriptu (na primer ena, dve).
Na kratko in poenostavljeno bo koda JavaScript videti takole:
Tukaj:
- var str = 'Plačaj račune 123 in 789 za klobaso'; – ustvarite spremenljivko Str in mu dodeli izvorno besedilo, ki ga bomo analizirali.
- var vzorec = /d+/gi; – ustvarite regularni izraz in ga postavite v spremenljivko vzorec.
Izraz se začne s poševnico (/).
Sam izraz tukaj je na primer d+ pomeni poljubno zaporedje števk.
Skozi ulomek za izrazom so dodatni iskalni parametri (modifikatorji) – navedete jih lahko v poljubnem vrstnem redu:
- g – pomeni globalno iskanje, torej po najdenem ujemanju se ne smete ustaviti, ampak iskanje nadaljevati do konca besedila. Če ta modifikator ni nastavljen, bo naš skript vrnil samo prvo ujemanje (123)
- i – iskanje brez upoštevanja velikih črk
- m – večvrstično iskanje (uporablja se, ko je izvorno besedilo razdeljeno na več vrstic)
- var rezultat = str.match(pattern).join(';'); – izvede iskanje v izvornem besedilu (Str) z danim regularnim izrazom (vzorec) in rezultate vstavite v spremenljivko povzroči, ki jih poveže s podpičjem z ukazom pridružite
- document.write(rezultat); – prikaz vsebine spremenljivke rezultata
Upoštevajte tudi, da so besedilni nizi (razen regularnih izrazov) v JavaScriptu obdani z apostrofi in ne z narekovaji, kot so v Power Query ali VBA.
Na izhodu nam bo ta skript kot rezultat dal vsa števila, ki jih najdemo v izvornem besedilu:
123, 789
Kratek tečaj JavaScript je končan, hvala vsem. Upam, da razumete logiko 🙂
Ostaja še prenos te konstrukcije v Power Query.
Funkcija išči in ekstrahiraj besedilo z regularnim izrazom v Power Query
Delamo naslednje:
1. Odprite Excel in na zavihku ustvarite nov prazen Power Query Podatki – Pridobi podatke / Ustvari zahtevo – Iz drugih virov – Prazna zahteva (Podatki — Pridobi podatke / Nova poizvedba — Iz drugih virov — Prazna poizvedba). Če imate staro različico Excela 2010-2013 in Power Query nimate vgrajenega, ampak je bil nameščen kot ločen dodatek, potem bo vse to na zavihku poizvedba o močiIn ne datum.
2. V prazno okno urejevalnika poizvedb, ki se odpre, na desni plošči takoj vnesite ime naše bodoče funkcije (npr. fxRegExpExtract)
3. Pojdimo na zavihek Pogled – napredni urejevalnik (Pogled — napredni urejevalnik), izbrišemo celotno M-kodo prazne zahteve in tja prilepimo kodo naše superfunkcije:
Pazi na roke:
V prvi vrstici povemo, da bo imela naša funkcija tri besedilne argumente: txt – izvirno besedilo, ki se analizira, regularni izraz – vzorec regularnega izraza, razmejiti — ločilni znak za prikaz rezultatov.
Nato pokličemo funkcijo Spletna stran, ki tvori kodo JavaScript, opisano zgoraj v svojem argumentu. V kodo prilepimo in nadomestimo naše spremenljive argumente.
Drobec:
[Podatki]{0}[Otroci]{0}[Otroci]{1}[Besedilo]{0}
… je potrebno, da »pademo skozi« v tabelo z rezultati, ki jih potrebujemo. Bistvo je, da funkcija Spletna stran posledično izdela več ugnezdenih tabel, ki ponavljajo strukturo spletne strani. Brez tega dela M-kode bi naša funkcija izpisala tole:
... in besedo bi morali klikniti večkrat Tabela, ki zaporedoma »padajo skozi« v podrejene ugnezdene tabele v stolpcih Otroci:
Namesto vsega tega citata takoj navedemo v kodi naše funkcije, katera ugnezdena tabela in stolpec (Besedilo) potrebujemo.
Tukaj so pravzaprav vse skrivnosti. Ostaja še pritisk na gumb Konec v oknu napredni urejevalnik, kamor smo vstavili našo kodo, in lahko nadaljujete z najbolj okusnim – preizkusite našo funkcijo pri delu.
Tukaj je nekaj primerov semen.
Primer 1. Pridobivanje številke računa in datuma iz opisa plačila
Imamo bančni izpisek z opisom (namenom) plačil, kjer morate v ločene stolpce izvleči številke in datume plačanih računov:
Tabelo naložimo v Power Query na standardni način Podatki – iz tabele/razpona (Podatki - od Tsposoben/Rangel).
Nato dodamo izračunani stolpec z našo funkcijo via Dodaj stolpec – pokliči funkcijo po meri (Dodaj stolpec — Prikliči funkcijo po meri) in vnesite njene argumente:
Kot regularni izraz (argument regularni izraz) uporabljamo predlogo:
(d{3,5}|d{2}.d{2}.d{4})
… prevedeno v človeški jezik pomeni:
številke od 3 do 5 mest (številke računov)
or
fragmenti oblike "2-bitno število - točka - 2-bitno število - točka - 4-bitno število", torej datumi v obliki DD.MM.LLLL.
Kot ločilni znak (argument razmejiti) vnesite podpičje.
Po kliku OK naša čarobna funkcija analizira vse začetne podatke glede na naš regularni izraz in nam oblikuje stolpec z najdenimi številkami in datumi računov:
Še vedno ga ločite s podpičjem z ukazom Domov — Razdeli stolpec — Z ločilom (Domov — Razdeli stolpec — Po ločilu) in dobimo, kar smo želeli:
Lepota!
2. primer: izvlecite e-poštne naslove iz besedila
Recimo, da imamo naslednjo tabelo kot začetne podatke:
… od koder moramo izvleči tam najdene e-poštne naslove (zaradi jasnosti sem jih v besedilu označil z rdečo).
Kot v prejšnjem primeru tabelo naložimo v Power Query na standardni način prek Podatki – iz tabele/razpona (Podatki - od Tsposoben/Rangel).
Nato dodamo izračunani stolpec z našo funkcijo via Dodaj stolpec – pokliči funkcijo po meri (Dodaj stolpec — Prikliči funkcijo po meri) in vnesite njene argumente:
Razčlenjevanje e-poštnih naslovov je težja naloga in obstaja kup regularnih izrazov različnih stopenj nočne more, ki jo lahko rešijo. Uporabil sem eno od preprostih možnosti - ni idealna, a v večini primerov precej deluje:
[w|.|-]*@w*.[w|.]*
Kot ločilo (razmejiti) lahko vnesete podpičje in presledek.
Kliknite na OK in dobimo stolpec z e-poštnimi naslovi, izvlečenimi iz izvirnega besedila “porridge”:
Čarovnija!
PS
Kot pravi pregovor: "Ni je tako dobre stvari, ki je ne bi bilo mogoče narediti še boljšo." Power Query je sam po sebi kul, v kombinaciji z regularnimi izrazi pa nam daje popolnoma nerealistično moč in prilagodljivost pri obdelavi kakršnih koli besedilnih podatkov. Upam, da bo Microsoft nekoč dodal podporo za RegExp v posodobitve Power Query in Power BI in bodo vsi zgoraj omenjeni plesi s tamburino postali preteklost. No, zaenkrat ja.
Prav tako želim dodati, da je priročno igrati z regularnimi izrazi na spletnem mestu https://regexr.com/ – kar v spletnem urejevalniku. Tam v oddelku Skupnostni vzorci Obstaja ogromno število že pripravljenih rednih sezon za vse priložnosti. Eksperimentirajte – vsa moč regularnih izrazov vam je zdaj na voljo v Power Query!
- Kaj so regularni izrazi (RegExp) in kako jih uporabljati v Excelu
- Iskanje mehkega besedila v Power Query
- Sestavljanje tabel iz različnih datotek s pomočjo Power Query