Contents [show]
As jy ten minste effens vertroud is met gereelde uitdrukkings, dan hoef jy dit nie te adverteer nie. As jy nie heeltemal in die onderwerp is nie, dan is gereelde uitdrukkings (Regular Expressions = RegExp = "regexps" = "regulars") 'n taal waar, met behulp van spesiale karakters en reëls, na die nodige substringe in die teks gesoek word, hulle word onttrek of vervang met ander teks. Dit is 'n baie kragtige en pragtige hulpmiddel, 'n orde van grootte wat beter is as alle ander maniere om met teks te werk.
Ek het reeds in detail en met 'n klomp voorbeelde uit die lewe beskryf hoe jy gereelde uitdrukking-ondersteuning by Excel kan voeg deur eenvoudige makro's te gebruik - as jy nie hierdie artikel gelees het nie, beveel ek sterk aan dat jy dit lees voordat jy verder gaan. Jy sal baie nuwe dinge ontdek, ek waarborg 🙂
Die vraag bly egter oop – hoe om die vermoë by te voeg om gereelde uitdrukkings in Power Query te gebruik? Power Query is natuurlik goed op sy eie en kan baie met teks doen (sny, plak, skoonmaak, ens.), maar as jy dit met die krag van gereelde uitdrukkings kon kruis, sou dit net 'n bom wees.
Ongelukkig is daar geen ingeboude funksies om met RegExps in Power Query te werk nie, en amptelike Microsoft-hulp en tegniese ondersteuning beantwoord hierdie vraag ontkennend. Daar is egter 'n manier om hierdie beperking 🙂
Die essensie van die metode
Die hoofgedagte is eenvoudig om te skande.
In die lys van ingeboude Power Query-vermoëns is daar 'n funksie Webblad. Die beskrywing van hierdie funksie op die amptelike Microsoft-hulpwerf is uiters bondig:
Vertaal, sou dit wees: "Gee die inhoud van die HTML-dokument terug in sy komponentstrukture, sowel as 'n voorstelling van die volledige dokument en sy liggaam nadat die etikette verwyder is." So-so beskrywing, eerlik.
Gewoonlik word hierdie funksie gebruik wanneer data vanaf die web invoer en word outomaties vervang, byvoorbeeld wanneer ons op die blad kies data Command Van die internet af (Data - Van die web). Ons gee die funksie 'n webbladsy as 'n argument, en dit gee die inhoud daarvan aan ons terug in die vorm van tabelle, nadat al die etikette voorheen skoongemaak is.
Wat die hulp NIE sê nie, is dit bykomend tot die HTML-opmerktaal funksie Webblad ondersteun JavaScript-skrifte, wat nou alomteenwoordig op webwerwe op die internet is. En JavaScript kon op sy beurt nog altyd met gewone uitdrukkings werk en het ingeboude funksies vir RegExps! So om gereelde uitdrukkings in Power Query te implementeer, sal ons die Web.Page-funksies moet voer as 'n argument aan 'n klein JavaScript-program wat al die werk vir Power Query sal doen.
Hoe dit lyk in suiwer JavaScript
Daar is baie gedetailleerde tutoriale oor die werk met gereelde uitdrukkings in JavaScript op die internet (byvoorbeeld een, twee).
In kort en vereenvoudig, sal die JavaScript-kode soos volg lyk:
hier:
- var str = 'Betaal rekeninge 123 en 789 vir wors'; – skep 'n veranderlike str en gee dit die bronteks toe wat ons sal ontleed.
- var patroon = /d+/gi; – skep 'n gereelde uitdrukking en plaas dit in 'n veranderlike patroon .
Die uitdrukking begin met 'n skuinsstreep (/).
Die uitdrukking self hier is byvoorbeeld d+ staan vir enige volgorde van syfers.
Deur die breuk na die uitdrukking is daar addisionele soekparameters (wysigers) – hulle kan in enige volgorde gespesifiseer word:
- g – beteken globale soektog, maw nadat jy 'n pasmaat gevind het, moet jy nie stop nie, maar voortgaan met die soektog tot aan die einde van die teks. As hierdie wysiger nie gestel is nie, sal ons skrif slegs die eerste passing gee (123)
- i – soek sonder inagneming van hoofletters
- m - multi-reël soektog (gebruik wanneer die bronteks in verskeie reëls verdeel is)
- var resultaat = str.match(pattern).join(';'); - voer 'n soektog in die bronteks uit (str) deur die gegewe gereelde uitdrukking (patroon ) en plaas die resultate in 'n veranderlike lei, koppel hulle met 'n kommapunt met behulp van die opdrag sluit
- dokument.skryf(resultaat); – vertoon die inhoud van die resultaatveranderlike
Let ook daarop dat teksstringe (uitsluitend gereelde uitdrukkings) in JavaScript in apostrofe ingesluit word, nie aanhalings soos dit in Power Query of VBA is nie.
By die afvoer sal hierdie skrif ons as gevolg daarvan al die nommers gee wat in die bronteks gevind word:
123, 789
JavaScript-kortkursus is verby, dankie almal. Hoop jy verstaan die logika 🙂
Dit bly om hierdie konstruksie na Power Query oor te dra.
Soek en onttrek teksfunksie deur gereelde uitdrukking in Power Query
Ons doen die volgende:
1. Maak Excel oop en skep 'n nuwe leë Power Query in die oortjie Data – Kry data / Skep versoek – Van ander bronne – Leë versoek (Data - Kry data / Nuwe navraag - Van ander bronne - Leë navraag). As jy 'n ou weergawe van Excel 2010-2013 en Power Query het wat jy nie ingebou het nie, maar as 'n aparte byvoeging geïnstalleer is, sal dit alles op die blad wees KragnavraagEn nie data.
2. In die leë venster van die navraagredigeerder wat oopmaak, in die regterpaneel, voer onmiddellik die naam van ons toekomstige funksie in (byvoorbeeld, fxRegExpExtract)
3. Kom ons gaan na die blad Kyk - Gevorderde redakteur (Bekyk - Gevorderde Redigeerder), vee ons die hele M-kode van die leë versoek uit en plak die kode van ons superfunksie daar:
Hou jou hande dop:
In die eerste reël sê ons dat ons funksie drie teksargumente sal hê: txt – die oorspronklike teks wat ontleed word, regeks - gereelde uitdrukkingspatroon, afgrens - skeidingsteken vir die vertoon van resultate.
Vervolgens noem ons die funksie Webblad, wat die JavaScript-kode vorm wat hierbo in sy argument beskryf word. Ons plak en vervang ons veranderlike argumente in die kode.
Fragment:
[Data]{0}[Kinders]{0}[Kinders]{1}[Teks]{0}
… is nodig om in die tabel te “deurval” met die resultate wat ons nodig het. Die punt is dat die funksie Webblad gevolglik produseer dit verskeie geneste tabelle wat die struktuur van 'n webblad herhaal. Sonder hierdie stukkie M-kode sal ons funksie dit uitstuur:
… en ons sal verskeie kere op die woord moet klik Tabel, wat agtereenvolgens "deurval" in kind geneste tabelle in kolomme kinders:
In plaas van al hierdie aanhaling, dui ons dadelik in die kode van ons funksie aan watter geneste tabel en kolom (Teks) ons benodig.
Hier is eintlik al die geheime. Dit bly om die knoppie te druk Voltooi in die venster gevorderde redakteur, waar ons ons kode ingevoeg het, en jy kan voortgaan na die lekkerste – probeer ons funksie by die werk.
Hier is 'n paar saadvoorbeelde.
Voorbeeld 1. Haal die rekeningnommer en datum uit die betalingbeskrywing
Ons het 'n bankstaat met 'n beskrywing (doel) van betalings, waar jy die nommers en datums van betaalde fakture in aparte kolomme moet uittrek:
Ons laai die tabel in Power Query op die standaard manier deur Data – Van tabel/reeks (Data - Van Tbekwaam/Rengel).
Dan voeg ons 'n berekende kolom by met ons funksie via Voeg kolom by – Bel pasgemaakte funksie (Voeg kolom by – Roep pasgemaakte funksie aan) en voer sy argumente in:
As 'n gereelde uitdrukking (argument regeks) sjabloon wat ons gebruik:
(d{3,5}|d{2}.d{2}.d{4})
… in mensetaal vertaal beteken:
nommers van 3 tot 5 syfers (rekeningnommers)
or
fragmente van die vorm “2-bis getal – punt – 2-bis getal – punt – 4-bis getal”, dit wil sê datums van die vorm DD.MM.JJJJ.
As 'n skeidingsteken (argument afgrens) voer 'n kommapunt in.
Na kliek op OK ons towerfunksie ontleed al die aanvanklike data volgens ons gereelde uitdrukking en vorm 'n kolom vir ons met die gevind nommers en datums van die fakture:
Dit bly om dit te skei deur die kommapunt met behulp van die opdrag Tuis — Verdeel kolom — Deur skeiding (Tuis — Verdeel kolom — Deur skeidingsteken) en ons kry wat ons wou hê:
Beauty!
Voorbeeld 2: Onttrek e-posadresse uit teks
Gestel ons het die volgende tabel as aanvanklike data:
… van waar ons die e-posadresse moet uithaal wat daar gevind is (vir duidelikheid het ek hulle in rooi in die teks uitgelig).
Soos in die vorige voorbeeld, laai ons die tabel in Power Query op die standaard manier via Data – Van tabel/reeks (Data - Van Tbekwaam/Rengel).
Dan voeg ons 'n berekende kolom by met ons funksie via Voeg kolom by – Bel pasgemaakte funksie (Voeg kolom by – Roep pasgemaakte funksie aan) en voer sy argumente in:
Om e-posadresse te ontleed is 'n moeiliker taak en daar is 'n klomp gereelde uitdrukkings van verskillende grade van nagmerrie om dit op te los. Ek het een van die eenvoudige opsies gebruik – nie ideaal nie, maar werk in die meeste gevalle redelik:
[w|.|-]*@w*.[w|.]*
As skeier (afgrens) kan jy 'n kommapunt en 'n spasie invoer.
Klik op OK en ons kry 'n kolom met e-posadresse wat uit die oorspronklike teks "pap" onttrek is:
Towerkuns!
PS
Soos die spreekwoord sê: "Daar is nie so 'n goeie ding wat nie nog beter gemaak kan word nie." Power Query is cool op sy eie, en wanneer dit gekombineer word met gereelde uitdrukkings, gee dit ons heeltemal onrealistiese krag en buigsaamheid in die verwerking van enige teksdata. Ek hoop Microsoft sal eendag RegExp-ondersteuning by Power Query en Power BI-opdaterings byvoeg en al die bogenoemde danse met 'n tamboeryn sal iets van die verlede word. Wel, vir eers, ja.
Ek wil ook byvoeg dat dit gerieflik is om met gewone uitdrukkings op die webwerf https://regexr.com/ te speel – direk in die aanlynredigeerder. Daar in die afdeling Gemeenskapspatrone Daar is 'n groot aantal klaargemaakte gereelde seisoene vir alle geleenthede. Eksperimenteer – al die krag van gereelde uitdrukkings is nou tot jou diens in Power Query!
- Wat is gereelde uitdrukkings (RegExp) en hoe om dit in Excel te gebruik
- Fuzzy tekssoektog in Power Query
- Stel tabelle saam van verskillende lêers met behulp van Power Query