Efnisyfirlit
Ef þú ert að minnsta kosti aðeins kunnugur reglulegum orðasamböndum, þá þarftu ekki að auglýsa þær. Ef þú ert ekki alveg í efninu, þá er regluleg tjáning (Regular Expressions = RegExp = “regexps” = “regulars”) tungumál þar sem, með því að nota sérstafi og reglur, er leitað að nauðsynlegum undirstrengjum í textanum, þeir eru teknir út eða skipt út fyrir annan texta. Þetta er mjög öflugt og fallegt tól, stærðargráðu ofar öllum öðrum leiðum til að vinna með texta.
Ég hef þegar lýst í smáatriðum og með fullt af dæmum úr lífinu hvernig þú getur bætt við reglulegri tjáningu við Excel með einföldum fjölvi - ef þú hefur ekki lesið þessa grein mæli ég eindregið með því að þú lesir hana áður en þú heldur áfram. Þú munt uppgötva fullt af nýjum hlutum, ég ábyrgist 🙂
Hins vegar er spurningin enn opin - hvernig á að bæta við getu til að nota reglulegar segðir í Power Query? Power Query er auðvitað góð ein og sér og getur gert mikið með texta (klippa, líma, þrífa o.s.frv.), en ef þú gætir krossað það með krafti reglulegra tjáninga væri það bara sprengja.
Því miður eru engar innbyggðar aðgerðir til að vinna með RegExps í Power Query og opinber hjálp frá Microsoft og tækniaðstoð svara þessari spurningu neitandi. Hins vegar er leið framhjá þessari takmörkun 🙂
Kjarni aðferðarinnar
Meginhugmyndin er einföld til skammar.
Á listanum yfir innbyggða Power Query getu er aðgerð Vefsíða. Lýsingin á þessari aðgerð á opinberu Microsoft hjálparsíðunni er afar hnitmiðuð:
Þýtt myndi þetta vera: "Skilar innihaldi HTML skjalsins sundurliðað í hlutabyggingu þess, sem og framsetningu á öllu skjalinu og meginmáli þess eftir að merkin hafa verið fjarlægð." Svo-svo lýsing, satt að segja.
Venjulega er þessi aðgerð notuð þegar gögn eru flutt inn af vefnum og kemur sjálfkrafa í staðinn, til dæmis þegar við veljum á flipanum Gögn Skipun Af netinu (Gögn — af vefnum). Við gefum fallinu vefsíðu sem rök og hún skilar innihaldi hennar til okkar í formi töflur, eftir að hafa hreinsað öll merki áður.
Það sem hjálpin segir EKKI er að til viðbótar við HTML markup language virka Vefsíða styður JavaScript forskriftir, sem er nú alls staðar nálægur á vefsíðum á netinu. Og JavaScript hefur aftur á móti alltaf getað unnið með reglulegum tjáningum og hefur innbyggðar aðgerðir fyrir RegExps! Svo til að innleiða reglulegar tjáningar í Power Query, þurfum við að fæða Web.Page virka sem rök fyrir lítið JavaScript forrit sem mun vinna alla vinnu fyrir Power Query.
Hvernig það lítur út í hreinu JavaScript
Það er mikið af ítarlegum námskeiðum um að vinna með reglubundnar tjáningar í JavaScript á Netinu (til dæmis einn, tveir).
Í stuttu máli og einföldu mun JavaScript kóðinn líta svona út:
hér:
- var str = 'Borgaðu reikninga 123 og 789 fyrir pylsur'; - búa til breytu str og úthluta honum frumtextanum sem við munum greina.
- var mynstur = /d+/gi; – búa til reglulega tjáningu og setja hana í breytu mynstur.
Tjáningin byrjar á skástrik (/).
Tjáningin sjálf hér er til dæmis d+ stendur fyrir hvaða röð talna sem er.
Í gegnum brotið á eftir segðinni eru fleiri leitarfæribreytur (breytur) - þær má tilgreina í hvaða röð sem er:
- g – þýðir alþjóðleg leit, þ.e. eftir að hafa fundið samsvörun, ættirðu ekki að hætta, heldur halda leitinni áfram til loka textans. Ef þessi breytibúnaður er ekki stilltur mun handritið okkar aðeins skila fyrstu samsvörun (123)
- i – leit án tillits til hástafa
- m - margra lína leit (notað þegar frumtextanum er skipt í nokkrar línur)
- var niðurstaða = str.match(pattern).join(';'); - framkvæma leit í frumtextanum (str) með tiltekinni reglulegri tjáningu (mynstur) og settu niðurstöðurnar í breytu leitt, tengja þau saman með semíkommu með því að nota skipunina taka þátt
- document.write(niðurstaða); - birta innihald niðurstöðubreytunnar
Athugaðu líka að textastrengir (að undanskildum reglulegum orðasamböndum) í JavaScript eru innifalin í gæsalappir, ekki gæsalappir eins og þeir eru í Power Query eða VBA.
Við úttakið mun þetta handrit gefa okkur þar af leiðandi allar tölurnar sem finnast í frumtextanum:
123, 789
JavaScript stuttnámskeiði er lokið, takk allir. Vona að þú skiljir rökfræðina 🙂
Eftir er að flytja þessa byggingu til Power Query.
Leitaðu og dragðu út textaaðgerð með reglulegri tjáningu í Power Query
Við gerum eftirfarandi:
1. Opnaðu Excel og búðu til nýja tóma Power Query í flipanum Gögn – Fáðu gögn / Búðu til beiðni – Frá öðrum aðilum – Tóm beiðni (Gögn - Fáðu gögn / Ný fyrirspurn - Frá öðrum aðilum - Auð fyrirspurn). Ef þú ert með gamla útgáfu af Excel 2010-2013 og Power Query sem þú ert ekki með innbyggða, en var sett upp sem sérstök viðbót, þá verður þetta allt á flipanum OrkufyrirspurnOg ekki Gögn.
2. Í tómum glugga fyrirspurnaritlinum sem opnast, í hægra spjaldinu, sláðu strax inn nafn framtíðaraðgerðarinnar okkar (til dæmis, fxRegExpExtract)
3. Við skulum fara í flipann Skoða - Ítarleg ritstjóri (Skoða — Ítarleg ritstjóri), við eyðum út öllum M-kóða tómu beiðninnar og límum kóðann fyrir ofurvirkni okkar þar:
Gættu að höndum þínum:
Í fyrstu línunni segjum við að fallið okkar muni hafa þrjár textarök: txt - upprunalega textinn sem verið er að greina, regex - reglubundið tjáningarmynstur, afmörkun — afmörkunarstaf til að sýna niðurstöður.
Næst köllum við fallið Vefsíða, myndar JavaScript kóðann sem lýst er hér að ofan í röksemdafærslu sinni. Við límum og setjum breytubreytur okkar í kóðann.
Brot:
[Gögn]{0}[Börn]{0}[Börn]{1}[Texti]{0}
… þarf til að „falla“ inn í töfluna með þeim niðurstöðum sem við þurfum. Aðalatriðið er að aðgerðin Vefsíða þar af leiðandi framleiðir það nokkrar hreiðrar töflur sem endurtaka uppbyggingu vefsíðu. Án þessa stykki af M-kóða myndi aðgerðin okkar gefa þetta út:
… og við þyrftum að smella nokkrum sinnum á orðið Tafla, „falla í gegnum“ í röð inn í undirlagðar töflur í dálkum Börn:
Í stað allrar þessarar tilvitnunar tilgreinum við strax í kóða fallsins okkar hvaða hreiður töflu og dálk (Texti) við þurfum.
Hér eru reyndar öll leyndarmálin. Það er eftir að ýta á hnappinn Ljúka í glugganum háþróaður ritstjóri, þar sem við settum inn kóðann okkar, og þú getur haldið áfram í það ljúffengasta - prófaðu aðgerðina okkar í vinnunni.
Hér eru nokkur fræ dæmi.
Dæmi 1. Að sækja reikningsnúmer og dagsetningu úr greiðslulýsingu
Við erum með bankayfirlit með lýsingu (tilgangi) greiðslna, þar sem þú þarft að draga út númer og dagsetningar greiddra reikninga í sérstaka dálka:
Við hleðum töflunni inn í Power Query á hefðbundinn hátt í gegnum Gögn – Frá töflu/sviði (Gögn - Frá Tfær/Rengill).
Síðan bætum við við reiknuðum dálki með fallinu okkar via Bæta við dálki - Hringdu í sérsniðna aðgerð (Bæta við dálki - kalla fram sérsniðna aðgerð) og sláðu inn rök þess:
Sem venjuleg tjáning (rök regex) sniðmát sem við notum:
(d{3,5}|d{2}.d{2}.d{4})
… þýtt á mannamál þýðir:
tölur frá 3 til 5 tölustöfum (reikningsnúmer)
or
brot af forminu „2-bita tala – punktur – 2-bita tala – punktur – 4-bita tala“, það er dagsetningar á forminu DD.MM.ÁÁÁÁ.
Sem afmörkunarstaf (rök afmörkun) sláðu inn semíkommu.
Eftir að smella á OK töfrafallið okkar greinir öll upphafsgögn í samræmi við reglubundna tjáningu okkar og myndar dálk fyrir okkur með fundnum tölum og dagsetningum reikninganna:
Það er eftir að aðskilja það með semíkommu með því að nota skipunina Heima — Skipt dálki — Eftir afmörkun (Heima — Skiptur dálki — Eftir afmörkun) og við fáum það sem við vildum:
Fegurð!
Dæmi 2: Dragðu netföng úr texta
Segjum að við höfum eftirfarandi töflu sem upphafsgögn:
… þaðan sem við þurfum að draga út netföngin sem finnast þar (til glöggvunar auðkenndi ég þau með rauðu í textanum).
Eins og í fyrra dæmi, hleðum við töflunni inn í Power Query á hefðbundinn hátt í gegnum Gögn – Frá töflu/sviði (Gögn - Frá Tfær/Rengill).
Síðan bætum við við reiknuðum dálki með fallinu okkar via Bæta við dálki - Hringdu í sérsniðna aðgerð (Bæta við dálki - kalla fram sérsniðna aðgerð) og sláðu inn rök þess:
Að flokka netföng er erfiðara verkefni og það er fullt af reglulegum tjáningum af mismiklum martröð til að leysa það. Ég notaði einn af einföldu valkostunum - ekki tilvalið, en virkar alveg í flestum tilfellum:
[w|.|-]*@w*.[w|.]*
Sem skiljur (afmörkun) þú getur slegið inn semíkommu og bil.
Smelltu á OK og við fáum dálk með netföngum dregin út úr upprunalega textanum „grautur“:
Galdur!
PS
Eins og orðatiltækið segir: "Það er ekkert svo gott sem ekki væri hægt að gera enn betra." Power Query er flott ein og sér og þegar hún er sameinuð reglulegum tjáningum gefur hún okkur algjörlega óraunhæfan kraft og sveigjanleika við vinnslu textagagna. Ég vona að Microsoft muni einhvern tíma bæta við RegExp stuðningi í Power Query og Power BI uppfærslum og allir ofangreindir dansar með tambúrínu muni heyra fortíðinni til. Jæja, í bili, já.
Ég vil líka bæta því við að það er þægilegt að leika sér með venjuleg orðasambönd á síðunni https://regexr.com/ – beint í netritlinum. Þar í kaflanum Samfélagsmynstur Það er gríðarlegur fjöldi tilbúinna venjulegra tímabila fyrir öll tilefni. Gerðu tilraunir - allur kraftur reglulegra tjáninga er nú til þjónustu þinnar í Power Query!
- Hvað eru regluleg tjáning (RegExp) og hvernig á að nota þau í Excel
- Óljós textaleit í Power Query
- Að setja saman töflur úr mismunandi skrám með Power Query