Kaundan
Kung pamilyar ka sa mga regular nga ekspresyon, nan dili nimo kinahanglan nga i-anunsyo kini. Kung wala ka sa hilisgutan, nan ang mga regular nga ekspresyon (Regular Expressions = RegExp = "regexps" = "regulars") usa ka pinulongan diin, gamit ang espesyal nga mga karakter ug mga lagda, ang gikinahanglan nga mga substrings gipangita sa teksto, kini gikuha. o gipulihan sa ubang teksto. Kini usa ka kusgan kaayo ug matahum nga himan, usa ka han-ay sa kadako nga labaw sa tanan nga uban pang mga paagi sa pagtrabaho sa teksto.
Gihubit na nako sa detalye ug uban ang daghang mga panig-ingnan sa kinabuhi kung giunsa nimo madugang ang regular nga suporta sa ekspresyon sa Excel gamit ang mga yano nga macros - kung wala pa nimo mabasa kini nga artikulo, girekomenda ko nga basahon nimo kini sa wala pa magpadayon. Makadiskubre ka ug daghang bag-ong mga butang, garantiya ko 🙂
Bisan pa, ang pangutana nagpabilin nga bukas - unsaon pagdugang ang abilidad sa paggamit sa mga regular nga ekspresyon sa Power Query? Ang Power Query, siyempre, maayo sa iyang kaugalingon ug makahimo og daghan sa teksto (pagputol, pagpapilit, paglimpyo, ug uban pa), apan kung mahimo nimo kini matabok gamit ang gahum sa regular nga mga ekspresyon, kini usa lamang ka bomba.
Ikasubo, wala'y mga built-in nga function alang sa pagtrabaho kauban ang RegExps sa Power Query, ug ang opisyal nga tabang sa Microsoft ug teknikal nga suporta tubagon kini nga pangutana sa negatibo. Bisan pa, adunay usa ka paagi sa kini nga limitasyon 🙂
Ang diwa sa pamaagi
Ang panguna nga ideya mao ang yano nga pagpakaulaw.
Sa lista sa mga built-in nga Power Query nga kapabilidad, adunay function Web.Pahina. Ang paghulagway niini nga function sa opisyal nga Microsoft help site hilabihan ka mubo:
Kung hubaron, kini mahimong: "Ibalik ang mga sulud sa dokumento sa HTML nga gibahin sa mga istruktura nga sangkap niini, ingon usab usa ka representasyon sa kompleto nga dokumento ug ang lawas niini pagkahuman natangtang ang mga tag." So-so nga paghulagway, prangka.
Kasagaran kini nga function gigamit sa pag-import sa datos gikan sa web ug awtomatiko nga gipuli, pananglitan, kung gipili namon ang tab. Data Command Gikan sa Internet (Data — Gikan sa web). Gihatagan namon ang function sa usa ka web page ingon usa ka argumento, ug gibalik niini ang mga sulud niini kanamo sa porma sa mga lamesa, nga kaniadto gitangtang ang tanan nga mga tag.
Unsa ang DILI gisulti sa tabang mao nga dugang sa HTML markup language function Web.Pahina nagsuporta sa mga script sa JavaScript, nga karon kaylap na sa mga website sa Internet. Ug ang JavaScript, sa baylo, kanunay nga makahimo sa pagtrabaho uban ang mga regular nga ekspresyon ug adunay mga built-in nga function alang sa RegExps! Busa aron mapatuman ang mga regular nga ekspresyon sa Power Query, kinahanglan natong pakan-on ang mga function sa Web.Page isip argumento sa gamay nga JavaScript nga programa nga mobuhat sa tanang trabaho para sa Power Query.
Unsa ang hitsura niini sa puro nga JavaScript
Adunay daghang detalyado nga mga panudlo sa pagtrabaho kauban ang mga regular nga ekspresyon sa JavaScript sa Internet (pananglitan, usa, duha).
Sa laktod ug gipasimple, ang JavaScript code mahimong sama niini:
Dinhi:
- var str = 'Bayri ang mga bayranan 123 ug 789 para sa sausage'; – paghimo ug variable str ug i-assign niini ang source text nga atong analisahon.
- var pattern = /d+/gi; – paghimo ug regular nga ekspresyon ug ibutang kini sa usa ka variable sumbanan.
Ang ekspresyon nagsugod sa usa ka slash (/).
Ang ekspresyon mismo dinhi, pananglitan, mao d+ nagbarug alang sa bisan unsang pagkasunod-sunod sa mga digit.
Pinaagi sa fraction pagkahuman sa ekspresyon, adunay dugang nga mga parameter sa pagpangita (mga modifier) - mahimo silang itakda sa bisan unsang pagkasunud:
- g – nagpasabot sa global nga pagpangita, ie human sa pagpangita sa usa ka duwa, kamo kinahanglan nga dili mohunong, apan magpadayon sa pagpangita hangtud sa katapusan sa teksto. Kung kini nga modifier wala gitakda, nan ang among script ibalik ra ang una nga tugma (123)
- i – pagpangita nga walay pagtagad sa kaso sa mga sulat
- m - multi-line nga pagpangita (gigamit kung ang gigikanan nga teksto gibahin sa daghang linya)
- var resulta = str.match(pattern).join(';'); – paghimo og pagpangita sa tinubdan nga teksto (str) pinaagi sa gihatag nga regular nga ekspresyon (sumbanan) ug ibutang ang mga resulta sa usa ka variable resulta, pagdugtong kanila sa usa ka semicolon gamit ang command apil
- document.write(resulta); - ipakita ang mga sulud sa variable nga resulta
Timan-i usab nga ang mga string sa teksto (walay labot ang regular nga mga ekspresyon) sa JavaScript gilakip sa mga apostrophe, dili mga kinutlo sama sa naa sa Power Query o VBA.
Sa output, kini nga script maghatag kanato isip resulta sa tanan nga mga numero nga makita sa tinubdan nga teksto:
123, 789
Ang mubo nga kurso sa JavaScript nahuman na, salamat sa tanan. Hinaot makuha nimo ang logic 🙂
Nagpabilin nga ibalhin kini nga pagtukod sa Power Query.
Pagpangita ug Pagkuha sa Text Function pinaagi sa Regular nga Pagpahayag sa Power Query
Atong buhaton ang mosunod:
1. Ablihi ang Excel ug paghimo og bag-ong walay sulod nga Power Query sa tab Data – Pagkuhag datos / Paghimo hangyo – Gikan sa ubang mga tinubdan – Walay sulod nga hangyo (Data — Pagkuhag datos / Bag-ong pangutana — Gikan sa ubang tinubdan — Blangko nga pangutana). Kung aduna kay daan nga bersyon sa Excel 2010-2013 ug Power Query nga wala kay built-in, apan gi-install isip bulag nga add-in, nan kining tanan anaa sa tab Kusog nga PagpangutanaUg dili Data.
2. Sa walay sulod nga bintana sa editor sa pangutana nga moabli, sa tuo nga panel, isulod dayon ang ngalan sa atong umaabot nga function (pananglitan, fxRegExpExtract)
3. Adto ta sa tab Pagtan-aw - Advanced nga Editor (Tan-awa — Advanced Editor), atong papason ang tibuok M-code sa walay sulod nga hangyo ug idikit ang code sa atong superfunction didto:
Tan-awa ang imong mga kamot:
Sa una nga linya, giingon namon nga ang among function adunay tulo nga argumento sa teksto: txt - ang orihinal nga teksto nga gisusi, regex - regular nga pattern sa ekspresyon, delim - delimiter nga karakter alang sa pagpakita sa mga resulta.
Sunod among gitawag ang function Web.Pahina, nga nagporma sa JavaScript code nga gihulagway sa ibabaw sa iyang argumento. Among i-paste ug ilisan ang among variable nga mga argumento sa code.
Tipik:
[Data]{0}[Mga Bata]{0}[Mga Bata]{1}[Text]{0}
... gikinahanglan aron "mahulog" ngadto sa lamesa uban sa mga resulta nga atong gikinahanglan. Ang punto mao nga ang function Web.Pahina ingon usa ka sangputanan, naghimo kini daghang mga salag nga mga lamesa nga nagsubli sa istruktura sa usa ka panid sa web. Kung wala kini nga piraso sa M-code, ang among function magpagawas niini:
… ug kinahanglan natong i-klik ang pulong sa makadaghang higayon Table, sunodsunod nga "nahulog" ngadto sa mga child nested table sa mga column mga anak:
Imbis niining tanan nga kinutlo, among gipakita dayon sa code sa among function nga nagsalag sa lamesa ug kolum (Teksto) kinahanglan nato.
Dinhi, sa tinuud, ang tanan nga mga sekreto. Nagpabilin kini sa pagpindot sa buton Humana sa bintana abante nga editor, diin among gisal-ot ang among code, ug mahimo ka nga magpadayon sa labing lamian - sulayi ang among function sa trabaho.
Ania ang pipila ka mga pananglitan sa binhi.
Pananglitan 1. Pagbawi sa numero sa account ug petsa gikan sa paghulagway sa pagbayad
Kami adunay usa ka bank statement nga adunay usa ka paghulagway (katuyoan) sa mga pagbayad, diin kinahanglan nimo nga ibira ang mga numero ug petsa sa mga bayad nga mga invoice sa lainlaing mga kolum:
Gikarga namo ang lamesa sa Power Query sa standard nga paagi Data – Gikan sa Talaan/Sakop (Data — Gikan sa Tmakahimo/Ranghel).
Dayon magdugang kami og usa ka kalkulado nga kolum sa among function pinaagi sa Idugang ang Column - Tawga ang Custom Function (Idugang ang Column — I-invoke ang Custom Function) ug isulod ang mga argumento niini:
Ingon usa ka regular nga ekspresyon (argumento regex) template nga among gigamit:
(d{3,5}|d{2}.d{2}.d{4})
… gihubad ngadto sa tawhanong pinulongan nga nagpasabot:
mga numero gikan sa 3 hangtod 5 ka numero (mga numero sa account)
or
mga tipik sa porma nga "2-bit number - point - 2-bit number - point - 4-bit number", kana mao, mga petsa sa porma nga DD.MM.YYYY.
Ingon usa ka delimiter nga karakter (argumento delim) pagsulod ug semicolon.
Human sa pag-klik OK Ang among magic function nag-analisar sa tanan nga inisyal nga datos sumala sa among regular nga ekspresyon ug nagporma og usa ka kolum alang kanamo nga adunay nakit-an nga mga numero ug petsa sa mga invoice:
Nagpabilin kini sa pagbulag niini pinaagi sa semicolon gamit ang command Panimalay — Split Column — Pinaagi sa Delimiter (Balay — Split column — Pinaagi sa delimiter) ug makuha namo ang among gusto:
Katahum!
Pananglitan 2: Kuhaa ang mga email address gikan sa teksto
Ibutang ta nga kita adunay mosunod nga lamesa isip inisyal nga datos:
... gikan sa diin kinahanglan naton kuhaon ang mga adres sa email nga makit-an didto (alang sa katin-awan, gipasiugda nako kini nga pula sa teksto).
Sama sa miaging pananglitan, among gikarga ang lamesa sa Power Query sa standard nga paagi pinaagi sa Data – Gikan sa Talaan/Sakop (Data — Gikan sa Tmakahimo/Ranghel).
Dayon magdugang kami og usa ka kalkulado nga kolum sa among function pinaagi sa Idugang ang Column - Tawga ang Custom Function (Idugang ang Column — I-invoke ang Custom Function) ug isulod ang mga argumento niini:
Ang pag-parse sa mga adres sa email usa ka mas lisud nga buluhaton ug adunay usa ka hugpong sa mga regular nga ekspresyon sa lain-laing ang-ang sa nightmare aron masulbad kini. Gigamit nako ang usa sa yano nga mga kapilian - dili maayo, apan nagtrabaho sa kadaghanan nga mga kaso:
[w|.|-]*@w*.[w|.]*
Ingon nga separator (delim) makasulod ka ug semicolon ug space.
I-klik ang OK ug nakakuha kami usa ka kolum nga adunay mga adres sa e-mail nga gikuha gikan sa orihinal nga teksto nga "linugaw":
Salamangka!
PS
Sama sa giingon sa panultihon: "Wala'y ingon ka maayo nga butang nga dili mahimo nga labi ka maayo." Ang Power Query kay cool sa iyang kaugalingon, ug kung giubanan sa mga regular nga ekspresyon, naghatag kini kanamo nga hingpit nga dili realistiko nga gahum ug kadali sa pagproseso sa bisan unsang datos sa teksto. Nanghinaut ko nga ang Microsoft sa umaabot makadugang sa suporta sa RegExp sa Power Query ug Power BI nga mga update ug ang tanan nga mga sayaw sa ibabaw nga adunay tamburin mahimong usa ka butang sa nangagi. Aw, sa pagkakaron, oo.
Gusto usab nako nga idugang nga dali nga magdula sa mga regular nga ekspresyon sa site https://regexr.com/ - diha mismo sa editor sa online. Didto sa section Mga Sumbanan sa Komunidad Adunay usa ka dako nga gidaghanon sa andam nga gihimo nga regular nga mga panahon alang sa tanan nga mga okasyon. Eksperimento – ang tanang gahom sa regular nga mga ekspresyon anaa na sa imong serbisyo sa Power Query!
- Unsa ang mga regular nga ekspresyon (RegExp) ug kung giunsa kini gamiton sa Excel
- Fuzzy nga pagpangita sa teksto sa Power Query
- Pag-assemble sa mga lamesa gikan sa lainlaing mga file gamit ang Power Query