Edukiak
Adierazpen erregularrak apur bat ezagutzen badituzu, orduan ez dituzu iragarkirik egin behar. Gaian ez bazaude, esamolde erregularrak (Erregular adierazpenak = RegExp = “regexps” = “regulars”) hizkuntza bat da, non, karaktere eta arau bereziak erabiliz, beharrezkoak diren azpikateak testuan bilatzen diren, ateratzen diren. edo beste testu batekin ordezkatu. Oso tresna indartsua eta ederra da hau, testua lantzeko beste modu guztien gainetik dagoen magnitude-ordena.
Dagoeneko zehatz-mehatz deskribatu dut eta bizitzako adibide mordo batekin nola gehi dezakezun adierazpen erregular euskarria Excel-era makro sinpleak erabiliz - artikulu hau irakurri ez baduzu, jarraitu aurretik irakurtzea gomendatzen dizut. Gauza berri asko ezagutuko dituzu, ziurtatzen dut 🙂
Hala ere, galderak zabalik jarraitzen du: nola gehitu Power Query-n adierazpen erregularrak erabiltzeko gaitasuna? Power Query, noski, ona da berez eta asko egin dezake testuarekin (moztu, itsatsi, garbitu, etab.), baina adierazpen erregularren boterearekin gurutzatuko bazenu, bonba bat besterik ez litzateke izango.
Zoritxarrez, ez dago Power Query-n RegExps-ekin lan egiteko integratutako funtziorik, eta Microsoft-en laguntza eta laguntza tekniko ofizialek galdera honi ezezkoan erantzuten diote. Hala ere, badago muga hori 🙂
Metodoaren funtsa
Ideia nagusia lotsagarria da.
Power Query integratutako gaitasunen zerrendan, funtzio bat dago Web orria. Microsoft-en laguntza gune ofizialean funtzio honen deskribapena oso zehatza da:
Itzulitakoa, hauxe izango litzateke: "HTML dokumentuaren edukia bere osagai-egituretan banatuta itzultzen du, baita dokumentu osoaren eta bere gorputzaren irudikapena ere etiketak kendu ondoren". Halako deskribapena, egia esanda.
Normalean funtzio hau webetik datuak inportatzerakoan erabiltzen da eta automatikoki ordezkatzen da, adibidez, fitxan hautatzen dugunean Data Komandoa Internetetik (Datuak — Webetik). Funtzioari web orri bat ematen diogu argumentu gisa, eta bere edukia itzultzen digu taulen moduan, aurretik etiketa guztiak garbituta.
Laguntzak EZ dioena da HTML markatze-lengoaiaz gain funtzioa Web orria JavaScript script-ak onartzen ditu, gaur egun Interneteko webguneetan nonahikoa dena. Eta JavaScript, aldi berean, beti izan da adierazpen erregularrekin lan egiteko eta RegExps-en funtzioak barneratuak ditu! Beraz, Power Query-n adierazpen erregularrak ezartzeko, Web.Page funtzioak elikatu beharko ditugu Power Queryren lan guztia egingo duen JavaScript programa txiki baten argumentu gisa.
Nolakoa den JavaScript hutsean
Interneten JavaScript-en adierazpen erregularrak lantzeko tutorial zehatz asko daude (adibidez, bat, bi).
Laburbilduz eta sinplifikatuta, JavaScript kodea honela izango da:
Here:
- var str = 'Ordaindu 123 eta 789 fakturak txistorragatik'; – aldagai bat sortu str eta esleitu aztertuko dugun iturburu-testua.
- var eredua = /d+/gi; – Adierazpen erregular bat sortu eta aldagai batean jarri eredua.
Adierazpena barra (/) batekin hasten da.
Hemen esamoldea bera da, adibidez d+ edozein zifren sekuentzia adierazten du.
Adierazpenaren ondorengo zatikiaren bidez, bilaketa-parametro gehigarriak (aldatzaileak) daude; edozein ordenatan zehaztu daitezke:
- g – bilaketa globala esan nahi du, hau da, bat-etortze bat aurkitu ondoren, ez duzu gelditu behar, baizik eta bilaketarekin jarraitu testua amaitu arte. Aldatzaile hau ezartzen ez bada, orduan gure script-ak lehen partida (123) bakarrik itzuliko du.
- i – bilatu letren kasuak kontuan hartu gabe
- m - lerro anitzeko bilaketa (iturburu-testua hainbat lerrotan banatzen denean erabiltzen da)
- var emaitza = str.match(eredua).join(';'); – bilaketa bat egin sorburu-testuan (str) emandako adierazpen erregularraren bidez (eredua) eta jarri emaitzak aldagai batean ondorioz, komandoa erabiliz puntu eta koma batekin kateatuz batu
- dokumentua.idatzi(emaitza); – emaitza aldagaiaren edukia bistaratu
Kontuan izan, halaber, testu-kateak (esamolde erregularrak izan ezik) JavaScript-en apostrofoen artean sartzen direla, ez komatxoen artean Power Query-n edo VBA-n dauden bezala.
Irteeran, script honek sorburu-testuan aurkitutako zenbaki guztiak emango dizkigu ondorioz:
123, 789
JavaScript ikastaro laburra amaitu da, eskerrik asko guztioi. Espero dut logika jasotzea 🙂
Eraikuntza hau Power Query-ra transferitzea geratzen da.
Bilatu eta atera testu-funtzioa Adierazpen erregular bidez Power Query-n
Honako hau egiten dugu:
1. Ireki Excel eta sortu Power Query huts bat fitxan Datuak – Datuak lortu / Sortu eskaera – Beste iturri batzuetatik – Eskaera hutsa (Datuak — Lortu datuak / Kontsulta berria — Beste iturri batzuetatik — Kontsulta hutsik). Excel 2010-2013 eta Power Query-ren bertsio zahar bat baduzu, ez baduzu integratuta, baina aparteko gehigarri gisa instalatu zenuten, orduan hori guztia fitxan egongo da. Power kontsultaEta ez Data.
2. Irekitzen den kontsulta-editorearen leiho hutsean, eskuineko panelean, sartu berehala gure etorkizuneko funtzioaren izena (adibidez, fxRegExpExtract)
3. Goazen fitxara Ikusi - Editore aurreratua (Ikusi — Editore aurreratua), eskaera hutsaren M-kode osoa ezabatzen dugu eta gure superfuntzioaren kodea hor itsatsi dugu:
Begira zure eskuak:
Lehenengo lerroan, gure funtzioak hiru testu argumentu izango dituela esaten dugu: txt – aztertzen ari den jatorrizko testua, regex - Adierazpen erregular eredua, delim — Emaitzak bistaratzeko karaktere mugatzailea.
Ondoren, funtzioari deituko diogu Web orria, bere argumentuan goian deskribatutako JavaScript kodea osatuz. Gure argumentu aldagaiak kodean itsatsi eta ordezkatzen ditugu.
Fragmentoa:
[Datuak]{0}[Haurrak]{0}[Haurrak]{1}[Testua]{0}
… behar ditugun emaitzekin taulan “erortzea” behar da. Kontua da funtzioa Web orria ondorioz, web orri baten egitura errepikatzen duten hainbat habiaratu taula sortzen ditu. M-kode hau gabe, gure funtzioak hau aterako luke:
… eta hitza hainbat aldiz klikatu beharko genuke Taula, segidan "erortzen" zutabeetan habiaraturiko tauletan Haurrak:
Aipamen horren guztiaren ordez, berehala adierazten dugu gure funtzioaren kodean zein habiaratu diren taula eta zutabe (Testu) behar dugu.
Hona hemen, hain zuzen ere, sekretu guztiak. Botoia sakatzea geratzen da Amaitu leihoan editore aurreratua, non gure kodea txertatu genuen, eta goxoenera joan zaitezke: probatu gure funtzioa lanean.
Hona hemen hazien adibide pare bat.
Adibidea 1. Ordainketa deskribapenetik kontu-zenbakia eta data berreskuratzea
Ordainketen deskribapena (helburua) duen banku-laburpen bat dugu, non ordaindutako fakturen zenbakiak eta datak zutabe desberdinetan atera behar dituzun:
Taula Power Query-n kargatzen dugu modu estandarrean Datuak - Taula/Barrutitik (Datuak - Tgai/Raingerua).
Ondoren, kalkulatutako zutabe bat gehitzen dugu gure funtzioarekin Gehitu zutabea - Deitu funtzio pertsonalizatua (Gehitu zutabea — Deitu funtzio pertsonalizatua) eta sartu bere argumentuak:
Adierazpen erregular gisa (argumentua regex) erabiltzen dugun txantiloia:
(d{3,5}|d{2}.d{2}.d{4})
… giza hizkuntzaren esanahira itzulita:
3tik 5 zifrako zenbakiak (kontu zenbakiak)
or
"2 biteko zenbakia - puntua - 2 biteko zenbakia - puntua - 4 biteko zenbakia" formako zatiak, hau da, DD.MM.AAAA formako datak.
Karaktere mugatzaile gisa (argumentua delim) idatzi puntu eta koma.
Klik egin ondoren OK gure funtzio magikoak hasierako datu guztiak aztertzen ditu gure adierazpen erregularraren arabera eta zutabe bat osatzen digu fakturen aurkitutako zenbaki eta datekin:
Komandoa erabiliz puntu eta koma bidez bereiztea geratzen da Hasiera — Zatitu Zutabea — Mugatzailearen arabera (Hasiera — Zatitu zutabea — Mugatzailearen arabera) eta nahi genuena lortzen dugu:
Edertasuna!
2. adibidea: atera helbide elektronikoak testutik
Demagun taula hau hasierako datu gisa dugula:
… hortik atera behar ditugu bertan aurkitutako helbide elektronikoak (argitasunerako, gorriz nabarmendu ditut testuan).
Aurreko adibidean bezala, taula Power Query-n modu estandarrean kargatzen dugu Datuak - Taula/Barrutitik (Datuak - Tgai/Raingerua).
Ondoren, kalkulatutako zutabe bat gehitzen dugu gure funtzioarekin Gehitu zutabea - Deitu funtzio pertsonalizatua (Gehitu zutabea — Deitu funtzio pertsonalizatua) eta sartu bere argumentuak:
Helbide elektronikoak analizatzea lan zailagoa da eta amesgaizto maila ezberdineko adierazpen erregular mordoa dago konpontzeko. Aukera sinpleetako bat erabili nuen - ez da ideala, baina nahiko funtzionatzen du kasu gehienetan:
[w|.|-]*@w*.[w|.]*
Bereizle gisa (delim) puntu eta koma eta zuriune bat sar ditzakezu.
klik on OK eta "porridge" jatorrizko testutik ateratako helbide elektronikoak dituen zutabe bat jasoko dugu:
Magia!
PS
Esaerak dioen bezala: "Ez dago are hobetu ezin den gauza onik". Power Query oso polita da bere kabuz, eta adierazpen erregularrekin konbinatuta, edozein testu-datu prozesatzeko ahalmen eta malgutasun guztiz irrealistak ematen dizkigu. Espero dut Microsoft-ek noizbait RegExp euskarria gehitzea Power Query eta Power BI eguneraketetan eta goiko dantza danbolinarekin iraganeko gauza bihurtuko direla. Tira, oraingoz, bai.
Gainera, gaineratu nahi dut https://regexr.com/ gunean esamolde erregularrekin jolastea komenigarria dela – online editorean bertan. Hor atalean Komunitate ereduak Okasio guztietarako prest dauden denboraldi erregular ugari dago. Esperimentua - adierazpen erregularren ahalmen guztia zure zerbitzura dago Power Query-n!
- Zer dira adierazpen erregularrak (RegExp) eta nola erabili Excel-en
- Testu bilaketa lausoa Power Query-n
- Fitxategi ezberdinetako taulak biltzea Power Query erabiliz