Nilalaman
Kung medyo pamilyar ka sa mga regular na expression, hindi mo kailangang i-advertise ang mga ito. Kung wala ka pa sa paksa, kung gayon ang mga regular na expression (Regular Expressions = RegExp = "regexps" = "regulars") ay isang wika kung saan, gamit ang mga espesyal na character at panuntunan, ang mga kinakailangang substring ay hinahanap sa teksto, sila ay nakuha. o pinalitan ng ibang teksto. Ito ay isang napakalakas at magandang tool, isang pagkakasunud-sunod ng magnitude na nakahihigit sa lahat ng iba pang paraan ng pagtatrabaho sa text.
Inilarawan ko na nang detalyado at kasama ang isang grupo ng mga halimbawa mula sa buhay kung paano ka magdagdag ng regular na suporta sa pagpapahayag sa Excel gamit ang mga simpleng macro – kung hindi mo pa nabasa ang artikulong ito, lubos kong inirerekumenda na basahin mo ito bago magpatuloy. Marami kang matutuklasan na mga bagong bagay, ginagarantiya ko 🙂
Gayunpaman, nananatiling bukas ang tanong – paano magdagdag ng kakayahang gumamit ng mga regular na expression sa Power Query? Ang Power Query, siyempre, ay mahusay sa sarili nitong at maraming magagawa gamit ang teksto (paggupit, pagdikit, paglilinis, atbp.), ngunit kung maaari mong i-cross ito sa kapangyarihan ng mga regular na expression, ito ay magiging isang bomba lamang.
Sa kasamaang palad, walang mga built-in na function para sa pagtatrabaho sa RegExps sa Power Query, at ang opisyal na tulong ng Microsoft at teknikal na suporta ay sinasagot ang tanong na ito sa negatibo. Gayunpaman, mayroong isang paraan sa paligid ng limitasyong ito 🙂
Ang kakanyahan ng pamamaraan
Ang pangunahing ideya ay simple sa kahihiyan.
Sa listahan ng mga built-in na kakayahan ng Power Query, mayroong isang function Pahina ng web. Ang paglalarawan ng function na ito sa opisyal na site ng tulong ng Microsoft ay lubos na maigsi:
Kung isinalin, ito ay magiging: "Ibinabalik ang mga nilalaman ng HTML na dokumento na pinaghiwa-hiwalay sa mga bahaging istruktura nito, pati na rin ang representasyon ng kumpletong dokumento at katawan nito pagkatapos maalis ang mga tag." So-so description, sa totoo lang.
Karaniwan ang function na ito ay ginagamit kapag nag-i-import ng data mula sa web at awtomatikong pinapalitan, halimbawa, kapag pumili kami sa tab data Utos Mula sa internet (Data — Mula sa web). Binibigyan namin ang function ng isang web page bilang isang argumento, at ibinabalik nito sa amin ang mga nilalaman nito sa anyo ng mga talahanayan, na dati nang na-clear ang lahat ng mga tag.
Ang HINDI sinasabi ng tulong ay bilang karagdagan sa HTML markup language tungkulin Pahina ng web sumusuporta sa mga script ng JavaScript, na ngayon ay nasa lahat ng dako sa mga website sa Internet. At ang JavaScript, sa turn, ay palaging gumagana sa mga regular na expression at may mga built-in na function para sa RegExps! Kaya para ipatupad ang mga regular na expression sa Power Query, kakailanganin naming i-feed ang mga function ng Web.Page bilang argumento sa isang maliit na JavaScript program na gagawa ng lahat ng gawain para sa Power Query.
Ano ang hitsura nito sa purong JavaScript
Mayroong maraming mga detalyadong tutorial sa pagtatrabaho sa mga regular na expression sa JavaScript sa Internet (halimbawa, isa, dalawa).
Sa maikli at pinasimple, ang JavaScript code ay magiging ganito:
Dito:
- var str = 'Magbayad ng mga bill 123 at 789 para sa sausage'; – lumikha ng variable STR at italaga dito ang source text na ating susuriin.
- var pattern = /d+/gi; – lumikha ng isang regular na expression at ilagay ito sa isang variable huwaran.
Ang expression ay nagsisimula sa isang slash (/).
Ang expression mismo dito, halimbawa, ay d+ ay kumakatawan sa anumang pagkakasunud-sunod ng mga digit.
Sa pamamagitan ng fraction pagkatapos ng expression, may mga karagdagang parameter ng paghahanap (mga modifier) - maaari silang tukuyin sa anumang pagkakasunud-sunod:
- g – nangangahulugan ng pandaigdigang paghahanap, ibig sabihin, pagkatapos makahanap ng tugma, hindi ka dapat huminto, ngunit ipagpatuloy ang paghahanap hanggang sa katapusan ng teksto. Kung hindi nakatakda ang modifier na ito, ibabalik lang ng aming script ang unang tugma (123)
- i – maghanap nang walang pagsasaalang-alang sa kaso ng mga titik
- m – multi-line na paghahanap (ginagamit kapag ang source text ay nahahati sa ilang linya)
- var result = str.match(pattern).join(';'); – magsagawa ng paghahanap sa pinagmulang teksto (STR) sa pamamagitan ng ibinigay na regular na expression (huwaran) at ilagay ang mga resulta sa isang variable resulta, pinagsasama ang mga ito ng isang semicolon gamit ang command sumali
- document.write(resulta); – ipakita ang mga nilalaman ng variable ng resulta
Tandaan din na ang mga string ng teksto (hindi kasama ang mga regular na expression) sa JavaScript ay nakapaloob sa mga kudlit, hindi mga quote dahil ang mga ito ay nasa Power Query o VBA.
Sa output, ibibigay sa amin ng script na ito bilang resulta ang lahat ng mga numerong makikita sa source text:
123, 789
Ang maikling kurso ng JavaScript ay tapos na, salamat sa lahat. Sana makuha mo ang logic 🙂
Nananatili itong ilipat ang construction na ito sa Power Query.
Search at Extract Text Function sa pamamagitan ng Regular Expression sa Power Query
Ginagawa namin ang sumusunod:
1. Buksan ang Excel at gumawa ng bagong walang laman na Power Query sa tab Data - Kumuha ng data / Lumikha ng kahilingan - Mula sa iba pang mga mapagkukunan - Walang laman na kahilingan (Data — Kumuha ng data / Bagong query — Mula sa iba pang mapagkukunan — Blangkong query). Kung mayroon kang lumang bersyon ng Excel 2010-2013 at Power Query na wala kang built-in, ngunit na-install bilang isang hiwalay na add-in, ang lahat ng ito ay nasa tab kapangyarihan queryAt hindi data.
2. Sa walang laman na window ng editor ng query na bubukas, sa kanang panel, ipasok kaagad ang pangalan ng aming function sa hinaharap (halimbawa, fxRegExpExtract)
3. Pumunta tayo sa tab Tingnan – Advanced na Editor (Tingnan — Advanced na Editor), binubura namin ang buong M-code ng walang laman na kahilingan at i-paste ang code ng aming superfunction doon:
Panoorin ang iyong mga kamay:
Sa unang linya, sinasabi namin na ang aming function ay magkakaroon ng tatlong tekstong argumento: txt – sinusuri ang orihinal na teksto, regex - pattern ng regular na expression, delim — karakter ng delimiter para sa pagpapakita ng mga resulta.
Susunod na tinatawag namin ang function Pahina ng web, na bumubuo ng JavaScript code na inilarawan sa itaas sa argumento nito. I-paste at pinapalitan namin ang aming mga variable na argumento sa code.
Fragment:
[Data]{0}[Mga Bata]{0}[Mga Bata]{1}[Text]{0}
… ay kailangan upang "mahulog" sa talahanayan kasama ang mga resulta na kailangan namin. Ang punto ay ang pag-andar Pahina ng web bilang resulta, gumagawa ito ng ilang nested table na inuulit ang istruktura ng isang web page. Kung wala itong piraso ng M-code, ilalabas ito ng aming function:
… at kailangan naming i-click ang salita nang maraming beses mesa, sunud-sunod na "nahuhulog" sa mga child nested na talahanayan sa mga column Mga bata:
Sa halip na lahat ng sipi na ito, agad naming ipinapahiwatig sa code ng aming function kung saan naka-nest ang talahanayan at column (teksto) kailangan namin.
Narito, sa katunayan, ang lahat ng mga lihim. Ito ay nananatiling pindutin ang pindutan Tapusin sa bintana advanced na editor, kung saan namin ipinasok ang aming code, at maaari kang magpatuloy sa pinaka masarap – subukan ang aming function sa trabaho.
Narito ang ilang mga halimbawa ng binhi.
Halimbawa 1. Pagkuha ng account number at petsa mula sa paglalarawan ng pagbabayad
Mayroon kaming bank statement na may paglalarawan (layunin) ng mga pagbabayad, kung saan kailangan mong ilabas ang mga numero at petsa ng mga bayad na invoice sa magkakahiwalay na column:
Nilo-load namin ang talahanayan sa Power Query sa karaniwang paraan Data – Mula sa Talahanayan/Saklaw (Data — Mula sa Tkaya/Ranghel).
Pagkatapos ay nagdaragdag kami ng isang kinakalkula na hanay sa aming function sa pamamagitan ng Magdagdag ng Column – Tumawag sa Custom na Function (Magdagdag ng Column — I-invoke ang Custom na Function) at ilagay ang mga argumento nito:
Bilang isang regular na expression (argumento regex) template na ginagamit namin:
(d{3,5}|d{2}.d{2}.d{4})
… isinalin sa kahulugan ng wika ng tao:
mga numero mula 3 hanggang 5 digit (mga numero ng account)
or
mga fragment ng form na "2-bit number - point - 2-bit number - point - 4-bit number", iyon ay, mga petsa ng form na DD.MM.YYYY.
Bilang karakter ng delimiter (argumento delim) maglagay ng semicolon.
Pagkatapos ng pag-click sa OK sinusuri ng aming magic function ang lahat ng paunang data ayon sa aming regular na expression at bumubuo ng column para sa amin na may mga nahanap na numero at petsa ng mga invoice:
Ito ay nananatiling paghiwalayin ito sa pamamagitan ng semicolon gamit ang command Home — Split Column — By Delimiter (Home — Hatiin ang column — Ayon sa delimiter) at makuha namin ang gusto namin:
Kagandahan!
Halimbawa 2: I-extract ang mga email address mula sa text
Ipagpalagay na mayroon kaming sumusunod na talahanayan bilang paunang data:
... mula sa kung saan kailangan nating bunutin ang mga email address na matatagpuan doon (para sa kalinawan, na-highlight ko ang mga ito sa pula sa teksto).
Tulad ng sa nakaraang halimbawa, nilo-load namin ang talahanayan sa Power Query sa karaniwang paraan sa pamamagitan ng Data – Mula sa Talahanayan/Saklaw (Data — Mula sa Tkaya/Ranghel).
Pagkatapos ay nagdaragdag kami ng isang kinakalkula na hanay sa aming function sa pamamagitan ng Magdagdag ng Column – Tumawag sa Custom na Function (Magdagdag ng Column — I-invoke ang Custom na Function) at ilagay ang mga argumento nito:
Ang pag-parse ng mga email address ay isang mas mahirap na gawain at mayroong isang grupo ng mga regular na expression ng iba't ibang antas ng bangungot upang malutas ito. Ginamit ko ang isa sa mga simpleng opsyon - hindi perpekto, ngunit medyo gumagana sa karamihan ng mga kaso:
[w|.|-]*@w*.[w|.]*
Bilang separator (delim) maaari kang magpasok ng tuldok-kuwit at puwang.
Mag-click sa OK at nakakakuha kami ng column na may mga e-mail address na kinuha mula sa orihinal na text na "sinigang":
Magic!
PS
Tulad ng sinasabi ng kasabihan: "Walang ganoong magandang bagay na hindi maaaring pahusayin pa." Ang Power Query ay cool sa sarili nitong, at kapag pinagsama sa mga regular na expression, ito ay nagbibigay sa amin ng ganap na hindi makatotohanang kapangyarihan at flexibility sa pagproseso ng anumang data ng text. Umaasa ako na balang araw ay magdagdag ang Microsoft ng suporta sa RegExp sa Power Query at Power BI update at lahat ng mga sayaw sa itaas na may tamburin ay magiging isang bagay ng nakaraan. Well, sa ngayon, oo.
Nais ko ring idagdag na maginhawang maglaro ng mga regular na expression sa site https://regexr.com/ – mismo sa online na editor. Doon sa section Mga Pattern ng Komunidad Mayroong isang malaking bilang ng mga handa na regular na panahon para sa lahat ng okasyon. Eksperimento – lahat ng kapangyarihan ng mga regular na expression ay nasa iyong serbisyo na ngayon sa Power Query!
- Ano ang mga regular na expression (RegExp) at kung paano gamitin ang mga ito sa Excel
- Malabo na paghahanap ng teksto sa Power Query
- Pag-assemble ng mga talahanayan mula sa iba't ibang mga file gamit ang Power Query