tartalom
Ha legalább egy kicsit ismeri a reguláris kifejezéseket, akkor nem kell reklámoznia őket. Ha nem vagy teljesen a tárgyban, akkor a reguláris kifejezések (Regular Expressions = RegExp = "regexps" = "regulars") egy olyan nyelv, ahol speciális karakterek és szabályok segítségével megkeresik a szövegben a szükséges részkarakterláncokat, és kivonják azokat. vagy más szöveggel helyettesítjük. Ez egy nagyon erős és gyönyörű eszköz, nagyságrenddel felülmúlja a szöveggel való munka minden más módját.
Már részletesen és egy csomó életből vett példával leírtam, hogyan adhat hozzá reguláris kifejezés támogatást az Excelhez egyszerű makrók segítségével – ha még nem olvasta ezt a cikket, akkor nagyon ajánlom, hogy olvassa el a folytatás előtt. Sok új dolgot fogsz felfedezni, garantálom 🙂
A kérdés azonban nyitva marad – hogyan adható hozzá a reguláris kifejezések használatának képessége a Power Queryben? A Power Query persze önmagában is jó, és sok mindent tud a szöveggel (kivágás, ragasztás, tisztítás stb.), de ha a reguláris kifejezések erejével keresztezné, az csak egy bomba lenne.
Sajnos a Power Queryben nincsenek beépített funkciók a RegExps-szel való munkához, és a hivatalos Microsoft súgó és technikai támogatás nemleges választ ad erre a kérdésre. Ezt a korlátozást azonban ki lehet kerülni 🙂
A módszer lényege
A fő gondolat egyszerűen megszégyeníthető.
A beépített Power Query képességek listájában van egy funkció Honlap. Ennek a funkciónak a leírása a Microsoft hivatalos súgóoldalán rendkívül tömör:
Lefordítva ez lenne: "A HTML-dokumentum tartalmát adja vissza összetevőstruktúráira lebontva, valamint a teljes dokumentum és annak törzsének ábrázolását a címkék eltávolítása után." So-so leírás, őszintén szólva.
Általában ezt a funkciót az internetről történő adatok importálásakor használják, és automatikusan helyettesítik, például amikor kiválasztjuk a lapon dátum parancs Az internetről (Adatok – a webről). A függvénynek egy weboldalt adunk argumentumként, és az előzőleg minden címkét törölve visszaadja nekünk a tartalmát táblázatok formájában.
Amit a súgó NEM mond, hogy a HTML jelölőnyelv mellett funkció Honlap támogatja a JavaScript szkripteket, amely ma már mindenütt megtalálható az internetes webhelyeken. A JavaScript pedig mindig is tudott működni reguláris kifejezésekkel, és beépített funkciókkal rendelkezik a RegExps-ekhez! Tehát ahhoz, hogy reguláris kifejezéseket implementálhassunk a Power Queryben, a Web.Page függvényeket argumentumként kell táplálnunk egy kis JavaScript programhoz, amely elvégzi a Power Query összes munkáját.
Hogy néz ki a tiszta JavaScriptben
Az interneten sok részletes oktatóanyag található a JavaScript reguláris kifejezéseivel való munkáról (például egy, kettő).
Röviden és leegyszerűsítve a JavaScript kód így fog kinézni:
Itt:
- var str = 'A 123-as és 789-es számlák befizetése a kolbászért'; – hozzon létre egy változót str és hozzárendeljük az elemezni kívánt forrásszöveget.
- var minta = /d+/gi; – hozzon létre egy reguláris kifejezést, és tegye változóba minta.
A kifejezés perjellel (/) kezdődik.
Maga a kifejezés itt például az d+ bármely számjegysorozatot jelöl.
A kifejezés utáni törten keresztül további keresési paraméterek (módosítók) találhatók – ezek tetszőleges sorrendben megadhatók:
- g – globális keresést jelent, azaz egyezés megtalálása után ne álljunk le, hanem folytassuk a keresést a szöveg végéig. Ha ez a módosító nincs beállítva, akkor a szkriptünk csak az első egyezést adja vissza (123)
- i – keresés a betűk nagybetűitől függetlenül
- m – többsoros keresés (amikor a forrásszöveg több sorra van osztva)
- var result = str.match(pattern).join(';'); – végezzen keresést a forrásszövegben (str) a megadott reguláris kifejezéssel (minta), és az eredményeket egy változóba helyezzük eredményez, pontosvesszővel összefűzve a paranccsal csatlakozik
- document.write(eredmény); – megjeleníti az eredményváltozó tartalmát
Vegye figyelembe azt is, hogy a szöveges karakterláncok (a reguláris kifejezések kivételével) a JavaScriptben aposztrófok közé vannak zárva, nem idézőjelek közé, ahogyan a Power Queryben vagy a VBA-ban.
A kimeneten ez a szkript megadja nekünk a forrásszövegben található összes számot:
123, 789
A JavaScript rövid tanfolyam véget ért, köszönöm mindenkinek. Remélem érted a logikát 🙂
Ezt a konstrukciót át kell vinni a Power Querybe.
Szövegfüggvény keresése és kibontása reguláris kifejezéssel a Power Queryben
A következőket tesszük:
1. Nyissa meg az Excelt, és hozzon létre egy új üres Power Query-t a lapon Adatok – Adatok lekérése / Kérelem létrehozása – Más forrásból – Üres kérés (Adatok — Adatok lekérése / Új lekérdezés — Más forrásokból — Üres lekérdezés). Ha az Excel 2010-2013 régi verziója és a Power Query nincs beépítve, de külön bővítményként telepítették, akkor mindez a lapon lesz. Teljesítmény lekérdezésÉs nem dátum.
2. A megnyíló lekérdezésszerkesztő üres ablakában, a jobb oldali panelen azonnal írjuk be jövőbeli függvényünk nevét (pl. fxRegExpExtract)
3. Menjünk a lapra Nézet – Speciális szerkesztő (Nézet – Speciális szerkesztő), töröljük az üres kérés teljes M-kódját, és oda illesszük be a szuperfunkciónk kódját:
Vigyázz a kezedre:
Az első sorban azt mondjuk, hogy a függvényünknek három szöveges argumentuma lesz: txt – az elemzett eredeti szöveget, regex - reguláris kifejezés minta, elválasztani — határoló karakter az eredmények megjelenítéséhez.
Ezután hívjuk a függvényt Honlap, létrehozva az argumentumában fent leírt JavaScript kódot. A változó argumentumainkat beillesztjük és behelyettesítjük a kódba.
Töredék:
[Adatok]{0}[Gyerekek]{0}[Gyerekek]{1}[Szöveg]{0}
… szükséges ahhoz, hogy „beleessünk” a táblázatba a szükséges eredményekkel. A lényeg az, hogy a függvény Honlap ennek eredményeként több egymásba ágyazott táblát hoz létre, amelyek megismétlik egy weboldal szerkezetét. Az M-kód ezen része nélkül a függvényünk ezt adná ki:
… és többször is rá kellene kattintanunk a szóra Táblázat, egymás után „átesik” a gyermek beágyazott táblázatokba oszlopokban Gyerekek:
Mindezen idézetek helyett azonnal jelezzük függvényünk kódjában, hogy melyik beágyazott tábla és oszlop (szöveg) szükségünk van.
Valójában itt van minden titok. Marad a gomb megnyomása befejez az ablakban haladó szerkesztő, ahová beszúrtuk a kódunkat, és már mehet is a legfinomabbra – próbálja ki funkciónkat a munkahelyén.
Íme néhány vetőmag példa.
Példa 1. Számlaszám és dátum lekérése a fizetési leírásból
Rendelkezünk a fizetések leírását (célját) tartalmazó bankszámlakivonattal, ahol külön oszlopokba kell kihúzni a kifizetett számlák számát és dátumát:
A táblát a szokásos módon töltjük be a Power Querybe Adatok – Táblázatból/Tartományból (Adatok – Tképes/Rangyal).
Ezután hozzáadunk egy számított oszlopot a via függvényünkkel Oszlop hozzáadása – Egyéni függvény hívása (Oszlop hozzáadása – Egyéni függvény meghívása) és írja be az argumentumait:
Reguláris kifejezésként (argumentum regex) sablont használunk:
(d{3,5}|d{2}.d{2}.d{4})
… emberi nyelvre lefordítva jelentése:
3-5 számjegyű számok (számlaszámok)
or
„2 bites szám – pont – 2 bites szám – pont – 4 bites szám” alakú töredékei, azaz DD.HH.ÉÉÉÉ formátumú dátumok.
Határoló karakterként (érv elválasztani) írjon be egy pontosvesszőt.
Miután rákattintott OK varázsfüggvényünk a reguláris kifejezésünk alapján elemzi az összes kiindulási adatot, és egy oszlopot képez számunkra a talált számlák számaival és dátumaival:
A parancs segítségével pontosvesszővel kell elválasztani Kezdőlap — Oszlop — Határolóval (Főoldal – Oszlop felosztása – Határoló szerint) és megkapjuk, amit akartunk:
Szépség!
2. példa: E-mail címek kibontása a szövegből
Tegyük fel, hogy a következő táblázatot használjuk kiindulási adatokként:
… ahonnan ki kell húznunk az ott talált e-mail címeket (az egyértelműség kedvéért pirossal kiemeltem a szövegben).
Az előző példához hasonlóan a táblát a szabványos módon töltjük be a Power Querybe Adatok – Táblázatból/Tartományból (Adatok – Tképes/Rangyal).
Ezután hozzáadunk egy számított oszlopot a via függvényünkkel Oszlop hozzáadása – Egyéni függvény hívása (Oszlop hozzáadása – Egyéni függvény meghívása) és írja be az argumentumait:
Az e-mail címek elemzése nehezebb feladat, és egy csomó különböző fokú reguláris kifejezés létezik a megoldására. Az egyszerű lehetőségek egyikét használtam – nem ideális, de a legtöbb esetben nagyon működőképes:
[w|.|-]*@w*.[w|.]*
Elválasztóként (elválasztani) pontosvesszőt és szóközt írhat be.
Kattints a OK és kapunk egy oszlopot az eredeti „porridge” szövegből kivont e-mail címekkel:
Varázslat!
PS
Ahogy a mondás tartja: "Nincs olyan jó dolog, amit ne lehetne még jobbá tenni." A Power Query önmagában is menő, és reguláris kifejezésekkel kombinálva teljesen irreális erőt és rugalmasságot biztosít a szöveges adatok feldolgozásához. Remélem, hogy a Microsoft egyszer hozzáadja a RegExp támogatást a Power Query-hez és a Power BI-frissítésekhez, és a fenti tamburával való tánc a múlté lesz. Nos, egyelőre igen.
Azt is szeretném hozzátenni, hogy kényelmes a reguláris kifejezésekkel játszani a https://regexr.com/ oldalon – közvetlenül az online szerkesztőben. Ott a szekcióban Közösségi minták Rengeteg kész alapszezon létezik minden alkalomra. Kísérlet – a reguláris kifejezések minden ereje most az Ön szolgálatában áll a Power Queryben!
- Mik azok a reguláris kifejezések (RegExp) és hogyan kell használni őket az Excelben
- Fuzzy szöveges keresés a Power Queryben
- Táblázatok összeállítása különböző fájlokból a Power Query segítségével