Inhalter
Wann Dir op d'mannst e bëssen mat reguläre Ausdréck vertraut sidd, da musst Dir se net annoncéieren. Wann Dir net ganz am Thema sidd, da sinn regulär Ausdréck (Regular Expressions = RegExp = "regexps" = "regulars") eng Sprooch, wou mat spezielle Charakteren a Reegelen déi néideg Ënnersträicher am Text gesicht ginn, se extrahéiert ginn oder duerch aneren Text ersat. Dëst ass e ganz mächtegt a schéint Tool, eng Uerdnung vun der Gréisst superieur wéi all aner Weeër fir mam Text ze schaffen.
Ech hu schonn am Detail a mat enger Rëtsch Beispiller aus dem Liewen beschriwwen, wéi Dir regelméisseg Ausdrock-Ënnerstëtzung fir Excel mat einfache Makroen addéiere kënnt - wann Dir dësen Artikel net gelies hutt, ech recommandéieren Iech et ze liesen ier Dir weiderfuert. Dir wäert vill nei Saachen entdecken, ech garantéieren 🙂
Wéi och ëmmer, d'Fro bleift oppen - wéi d'Fäegkeet derbäi ze ginn fir regulär Ausdréck an der Power Query ze benotzen? Power Query ass selbstverständlech gutt eleng a ka vill mat Text maachen (Ausschneiden, Kleeblatt, Botzen, etc.), awer wann Dir et mat der Kraaft vu reguläre Ausdréck iwwerschreift, wier et just eng Bomm.
Leider ginn et keng agebaute Funktiounen fir mat RegExps an Power Query ze schaffen, an offiziell Microsoft Hëllef an technesch Ënnerstëtzung beäntweren dës Fro negativ. Wéi och ëmmer, et gëtt e Wee ronderëm dës Begrenzung 🙂
D'Essenz vun der Method
D'Haaptidee ass einfach ze schueden.
An der Lëscht vun agebaute Power Query Fäegkeeten gëtt et eng Funktioun Internet Säit. D'Beschreiwung vun dëser Funktioun op der offizieller Microsoft Help-Site ass extrem präzis:
Iwwersat wier dëst: "Gëtt den Inhalt vum HTML Dokument zréck a seng Komponentstrukturen zréck, wéi och eng Duerstellung vum komplette Dokument a sengem Kierper nodeems d'Tags ewechgeholl goufen." Sou-sou Beschreiwung, éierlech gesot.
Normalerweis gëtt dës Funktioun benotzt wann Dir Daten vum Internet importéiert a gëtt automatesch ersat, zum Beispill wa mir op der Tab auswielen Daten Kommando Vum Internet (Daten - Vum Web). Mir ginn der Funktioun eng Websäit als Argument, an et gëtt säin Inhalt un eis zréck a Form vun Dëscher, virdrun all Tags geläscht.
Wat d'Hëllef NET seet ass dat nieft der HTML Markup Sprooch Funktioun Internet Säit ënnerstëtzt JavaScript Scripten, déi elo iwwerall op Websäiten um Internet ass. An JavaScript, ofwiesselnd, konnt ëmmer mat reguläre Ausdréck schaffen an huet agebaute Funktiounen fir RegExps! Also fir reegelméisseg Ausdréck an der Power Query ëmzesetzen, musse mir de Web fidderen.Page funktionéiert als Argument fir e klenge JavaScript Programm deen all d'Aarbecht fir Power Query mécht.
Wéi et ausgesäit a pure JavaScript
Et gi vill detailléiert Tutorials fir mat reguläre Ausdréck am JavaScript um Internet ze schaffen (zum Beispill een, zwee).
Kuerz a vereinfacht, gesäit de JavaScript Code esou aus:
Hei:
- var str = 'Bezuelt Rechnungen 123 an 789 fir Wurst'; - eng Variabel erstellen Str a gitt et de Quelltext deen mir analyséieren.
- var Muster = /d+/gi; - e reegelméissegen Ausdrock erstellen an et an eng Variabel setzen Muster.
Den Ausdrock fänkt mat engem Slash (/) un.
Den Ausdrock selwer hei, zum Beispill, ass d+ steet fir all Sequenz vun Zifferen.
Duerch d'Fraktioun nom Ausdrock ginn et zousätzlech Sichparameter (Modifiers) - si kënnen an all Uerdnung spezifizéiert ginn:
- g – heescht global Sich, also nodeems Dir e Match fonnt hutt, sollt Dir net ophalen, mee d'Sich weiderfuere bis zum Enn vum Text. Wann dëse Modifier net agestallt ass, da gëtt eise Skript nëmmen den éischte Match zréck (123)
- i - Sich ouni Hibléck op Fall vu Bréiwer
- m - Multi-Line Sich (benotzt wann de Quelltext an e puer Zeilen opgedeelt ass)
- var result = str.match(pattern).join(';'); - Maacht eng Sich am Quelltext (Str) vum gegebene regulären Ausdrock (Muster) a setzt d'Resultater an eng Variabel Resultat, koncatenéieren se mat engem Semikolon mam Kommando anzeschreiwen
- document.write(Resultat); - weist den Inhalt vun der Resultatvariabel
Notéiert och datt Textstringen (ausser regulär Ausdréck) a JavaScript an Apostrophen zougemaach sinn, net Zitater wéi se an Power Query oder VBA sinn.
Beim Ausgang gëtt dëst Skript eis als Resultat all d'Zuelen, déi am Quelltext fonnt goufen:
123, 789
JavaScript Kuerzkurs ass eriwwer, merci all. Hoffen Dir kritt d'Logik 🙂
Et bleift fir dës Konstruktioun op Power Query ze transferéieren.
Sich an Extrait Text Funktioun duerch Regular Expression an Power Query
Mir maachen déi folgend:
1. Open Excel a erstellt eng nei eidel Power Query an der Tab Daten - Kritt Daten / Erstellt Ufro - Vun anere Quellen - Eidel Ufro (Daten - Kritt Daten / Nei Ufro - Vun anere Quellen - Blank Ufro). Wann Dir eng al Versioun vun Excel 2010-2013 a Power Query hutt, hutt Dir net agebaut, awer als separat Add-in installéiert, da wäert dat alles op der Tab sinn Muecht UfroAn net Daten.
2. An der eidel Fënster vum Ufro-Editor, deen opmaacht, am rietse Panel, gitt direkt den Numm vun eiser zukünfteger Funktioun (zum Beispill, fxRegExpExtract)
3. Loosst eis op d'Tab goen View - Advanced Editor (View - Advanced Editor), mir leschen de ganze M-Code vun der eidelen Ufro a pechen de Code vun eiser Superfunktioun do:
Passt op Är Hänn:
An der éischter Zeil soen mir datt eis Funktioun dräi Textargumenter huet: txt - den ursprénglechen Text deen analyséiert gëtt, regex - regelméisseg Ausdrock Muster, ofgrenzen - Delimiter Charakter fir Resultater ze weisen.
Als nächst ruffe mir d'Funktioun Internet Säit, de JavaScript-Code ze bilden, deen uewen a sengem Argument beschriwwe gëtt. Mir pechen an ersetzen eis variabel Argumenter an de Code.
Fragment:
[Daten]{0}[Kanner]{0}[Kanner]{1}[Text]{0}
... ass néideg fir an d'Tabell "duerch ze falen" mat de Resultater déi mir brauchen. De Punkt ass, datt d'Funktioun Internet Säit als Resultat produzéiert et e puer nestéiert Dëscher déi d'Struktur vun enger Websäit widderhuelen. Ouni dëst Stéck M-Code géif eis Funktioun dëst erausginn:
... a mir missten e puer Mol op d'Wuert klickt Dësch, successiv "falen duerch" an Kanner nestet Dëscher a Kolonnen Kanner:
Amplaz vun all dëser Zitat, weisen mir direkt am Code vun eiser Funktioun un déi nestéiert Tabell a Kolonn (Text) mir brauchen.
Hei sinn tatsächlech all d'Geheimnisser. Et bleift de Knäppchen ze drécken Freck an der Fënster fortgeschratt Redakter, wou mir eise Code agefouert hunn, an Dir kënnt op déi leckerste weidergoen - probéiert eis Funktioun op der Aarbecht.
Hei sinn e puer Som Beispiller.
Beispill 1. Recuperéieren der Kont Zuel an Datum vun der Bezuelen Beschreiwung
Mir hunn e Bankerklärung mat enger Beschreiwung (Zweck) vun de Bezuelungen, wou Dir d'Zuelen an d'Datume vun de bezuelte Rechnungen an getrennte Kolonnen erauszitt:
Mir lueden den Dësch an Power Query am Standard Wee duerch Donnéeën - Vun Table / Range (Daten - Vun Tkann/REngel).
Dann addéiere mer eng berechent Kolonn mat eiser Funktioun via Spalt derbäi - Call Benotzerdefinéiert Funktioun (Kolonn derbäisetzen - Benotzerdefinéiert Funktioun opruffen) a gitt seng Argumenter:
Als regelméisseg Ausdrock (Argument regex) Schabloun déi mir benotzen:
(d {3,5}|d{2}.d{2}.d{4})
... iwwersat an mënschlech Sprooch Bedeitung:
Zuelen vun 3 bis 5 Zifferen (Kontonummeren)
or
Fragmenter vun der Form "2-Bit Zuel - Punkt - 2-Bit Zuel - Punkt - 4-Bit Zuel", dat heescht Datumer vun der Form DD.MM.JJJJ.
Als Delimiter Charakter (Argument ofgrenzen) gitt e Semikolon.
Nodeems Dir op OK eis magesch Funktioun analyséiert all initial Donnéeën no eisem regulären Ausdrock a bildt eng Kolonn fir eis mat den fonnten Zuelen an Datumer vun de Rechnungen:
Et bleiwt et duerch Semikolon ze trennen mam Kommando Home - Spalt Kolonn - Duerch Ofgrenzer (Home - Split Kolonn - Duerch Ofgrenzer) a mir kréien wat mir wollten:
Schéinheet!
Beispill 2: Extrait E-Mail Adressen aus Text
Ugeholl mir hunn déi folgend Tabell als initial Daten:
... vu wou mir d'E-Mail-Adressen, déi do fonnt goufen, erauszéien (fir Kloerheet hunn ech se rout am Text markéiert).
Wéi am virege Beispill luede mir den Dësch an Power Query op de Standard Manéier iwwer Donnéeën - Vun Table / Range (Daten - Vun Tkann/REngel).
Dann addéiere mer eng berechent Kolonn mat eiser Funktioun via Spalt derbäi - Call Benotzerdefinéiert Funktioun (Kolonn derbäisetzen - Benotzerdefinéiert Funktioun opruffen) a gitt seng Argumenter:
E-Mail Adressen parséieren ass eng méi schwéier Aufgab an et ginn eng Rëtsch reegelméisseg Ausdréck vu variabelen Grad vun Albtraum fir et ze léisen. Ech hunn eng vun den einfachen Optiounen benotzt - net ideal, awer an de meeschte Fäll ganz funktionnéiert:
[w|.|-]*@w*.[w|.]*
Als Separator (ofgrenzen) kënnt Dir e Semikolon an e Raum aginn.
klickt op OK a mir kréien eng Kolonn mat E-Mail Adressen extrahéiert aus dem Originaltext "Kaffi":
Magesch!
PS
Wéi de Spréchwuert seet: "Et gëtt keng sou gutt Saach, déi net nach besser gemaach ka ginn." Power Query ass cool op sech selwer, a wann se mat reguläre Ausdréck kombinéiert ginn, gëtt et eis komplett onrealistesch Kraaft a Flexibilitéit bei der Veraarbechtung vun Textdaten. Ech hoffen Microsoft wäert iergendwann RegExp Support an Power Query a Power BI Updates addéieren an all déi uewe genannte Danz mat enger Tambourin wäert eng Saach vun der Vergaangenheet ginn. Gutt, fir elo, jo.
Ech wëll och addéieren datt et bequem ass mat reguläre Ausdréck op der Säit https://regexr.com/ ze spillen - direkt am Online Editor. Do an der Rubrik Gemeinschaftsmuster Et ginn eng riesech Unzuel vu fäerdege reguläre Saisons fir all Occasioun. Experimentéiert - all d'Kraaft vu reegelméissegen Ausdrock ass elo zu Ärem Service an der Power Query!
- Wat sinn regulär Ausdréck (RegExp) a wéi se se an Excel benotzen
- Fuzzy Text Sich an Power Query
- Assemblée Dëscher aus verschiddene Fichier mat Power Query