Saturs
Ja esat vismaz nedaudz iepazinies ar regulārām izteiksmēm, tad jums tās nav jāreklamē. Ja neesat gluži priekšmetā, tad regulārās izteiksmes (Regular Expressions = RegExp = "regexps" = "regulars") ir valoda, kurā, izmantojot speciālās rakstzīmes un noteikumus, tekstā tiek meklētas nepieciešamās apakšvirknes, tās tiek izvilktas. vai aizstāt ar citu tekstu . Šis ir ļoti spēcīgs un skaists rīks, kas ir daudz augstāks par visiem citiem veidiem, kā strādāt ar tekstu.
Es jau detalizēti un ar kaudzi piemēriem no dzīves aprakstīju, kā Excel var pievienot regulārās izteiksmes atbalstu, izmantojot vienkāršus makro – ja neesat izlasījis šo rakstu, ļoti iesaku to izlasīt pirms turpināt. Jūs atklāsiet daudz jauna, es garantēju 🙂
Tomēr atklāts paliek jautājums – kā Power Query pievienot iespēju izmantot regulāras izteiksmes? Power Query, protams, ir labs pats par sevi un var daudz darīt ar tekstu (griezt, līmēt, tīrīt utt.), bet, ja jūs varētu to krustot ar regulāro izteiksmju spēku, tas būtu tikai bumba.
Diemžēl Power Query nav iebūvētu funkciju darbam ar RegExps, un oficiālā Microsoft palīdzība un tehniskais atbalsts uz šo jautājumu atbild noraidoši. Tomēr ir veids, kā apiet šo ierobežojumu 🙂
Metodes būtība
Galvenā ideja ir vienkārši apkaunojama.
Iebūvēto Power Query iespēju sarakstā ir funkcija Web.Lapa. Šīs funkcijas apraksts oficiālajā Microsoft palīdzības vietnē ir ļoti īss:
Tulkojumā tas būtu: “Atgriež HTML dokumenta saturu, kas sadalīts tā sastāvdaļu struktūrās, kā arī visa dokumenta un tā pamatteksta attēlojumu pēc tagu noņemšanas.” Tāds apraksts, atklāti sakot.
Parasti šī funkcija tiek izmantota, importējot datus no tīmekļa, un tā tiek automātiski aizstāta, piemēram, kad cilnē atlasām Datums Komanda No interneta (Dati — no tīmekļa). Mēs piešķiram funkcijai tīmekļa lapu kā argumentu, un tā atgriež mums savu saturu tabulu veidā, iepriekš notīrot visus tagus.
Palīdzībā NAV teikts, ka papildus HTML iezīmēšanas valodai funkcija Web.Lapa atbalsta JavaScript skriptus, kas tagad ir visuresoša interneta vietnēs. Un JavaScript, savukārt, vienmēr ir spējis strādāt ar regulārām izteiksmēm, un tajā ir iebūvētas funkcijas RegExps! Tātad, lai ieviestu regulārās izteiksmes programmā Power Query, mums būs jāievada Web.Page funkcijas kā arguments nelielai JavaScript programmai, kas veiks visu Power Query darbu.
Kā tas izskatās tīrā JavaScript
Internetā ir daudz detalizētu pamācību par darbu ar regulārām izteiksmēm JavaScript (piemēram, viens, divi).
Īsumā un vienkāršotā veidā JavaScript kods izskatīsies šādi:
Šeit:
- var str = 'Maksājiet rēķinus 123 un 789 par desu'; - izveidot mainīgo str un piešķiriet tam avota tekstu, ko mēs analizēsim.
- var modelis = /d+/gi; – izveidot regulāru izteiksmi un ievietot to mainīgajā modelis.
Izteiksme sākas ar slīpsvītru (/).
Pati izteiksme šeit, piemēram, ir d+ apzīmē jebkuru ciparu secību.
Caur daļskaitli pēc izteiksmes ir papildu meklēšanas parametri (modifikatori) – tos var norādīt jebkurā secībā:
- g – nozīmē globālo meklēšanu, proti, pēc atbilstības atrašanas nevajag apstāties, bet turpināt meklēšanu līdz teksta beigām. Ja šis modifikators nav iestatīts, mūsu skripts atgriezīs tikai pirmo atbilstību (123)
- i – meklēt, neņemot vērā burtu reģistru
- m - vairāku rindiņu meklēšana (izmanto, ja avota teksts ir sadalīts vairākās rindās)
- var rezultāts = str.match(pattern).join(';'); - veikt meklēšanu avota tekstā (str) ar doto regulāro izteiksmi (modelis) un ievietojiet rezultātus mainīgajā radīt, savienojot tos ar semikolu, izmantojot komandu pievienoties
- document.write(rezultāts); – parādīt rezultāta mainīgā saturu
Ņemiet vērā arī to, ka teksta virknes (izņemot regulārās izteiksmes) JavaScript ir ietvertas apostrofos, nevis pēdiņās, kā tas ir Power Query vai VBA.
Izvadā šis skripts mums sniegs visus avota tekstā atrodamos skaitļus:
123, 789
JavaScript īsais kurss ir beidzies, paldies visiem. Cerams, ka sapratāt loģiku 🙂
Atliek šo konstrukciju pārnest uz Power Query.
Meklēt un izvilkt teksta funkciju, izmantojot regulāro izteiksmi programmā Power Query
Mēs veicam šādas darbības:
1. Atveriet programmu Excel un cilnē izveidojiet jaunu tukšu Power Query Dati – Saņemt datus / Izveidot pieprasījumu – No citiem avotiem – Tukšs pieprasījums (Dati — Iegūt datus / Jauns vaicājums — No citiem avotiem — Tukšs vaicājums). Ja jums ir veca Excel 2010–2013 versija un Power Query, kas jums nav iebūvēta, bet tika instalēta kā atsevišķa pievienojumprogramma, tas viss būs cilnē. Jaudas vaicājumsUn nē Datums.
2. Atvērtajā vaicājumu redaktora tukšajā logā labajā panelī nekavējoties ievadiet mūsu nākotnes funkcijas nosaukumu (piemēram, fxRegExpExtract)
3. Dosimies uz cilni Skats — uzlabotais redaktors (Skatīt — uzlabotais redaktors), mēs izdzēšam visu tukšā pieprasījuma M kodu un ielīmējam tur savas superfunkcijas kodu:
Uzmaniet savas rokas:
Pirmajā rindā mēs sakām, ka mūsu funkcijai būs trīs teksta argumenti: txt - analizējamais oriģinālais teksts, regex - regulāras izteiksmes modelis, atdalīt — norobežotāja rakstzīme rezultātu parādīšanai.
Tālāk mēs izsaucam funkciju Web.Lapa, veidojot iepriekš tā argumentā aprakstīto JavaScript kodu. Mēs ielīmējam un aizstājam mainīgos argumentus kodā.
Fragments:
[Dati]{0}[Bērni]{0}[Bērni]{1}[Teksts]{0}
… ir nepieciešams, lai “iekļūtu” tabulā ar mums vajadzīgajiem rezultātiem. Lieta tāda, ka funkcija Web.Lapa tā rezultātā tiek izveidotas vairākas ligzdotas tabulas, kas atkārto tīmekļa lapas struktūru. Bez šī M-koda fragmenta mūsu funkcija izvadītu šo:
... un mums būtu vairākas reizes jānoklikšķina uz vārda Tabula, secīgi “iekrītot cauri” pakārtotajās ligzdotajās tabulās kolonnās Bērni:
Visa šī citāta vietā mēs savas funkcijas kodā nekavējoties norādām, kura ligzdotā tabula un kolonna (Teksts) mums vajag.
Šeit patiesībā ir visi noslēpumi. Atliek nospiest pogu apdare logā uzlabots redaktors, kurā ievietojām savu kodu, un jūs varat pāriet uz garšīgāko – izmēģiniet mūsu funkciju darbā.
Šeit ir daži sēklu piemēri.
Piemērs 1. Konta numura un datuma izgūšana no maksājuma apraksta
Mums ir bankas izraksts ar maksājumu aprakstu (mērķi), kur atsevišķās kolonnās jāizvelk apmaksāto rēķinu numuri un datumi:
Mēs ielādējam tabulu programmā Power Query standarta veidā Dati — no tabulas/diapazona (Dati — no Tspējīgs/Reņģelis).
Tad mēs pievienojam aprēķināto kolonnu ar mūsu funkciju via Pievienot kolonnu — izsaukt pielāgoto funkciju (Pievienot kolonnu — izsaukt pielāgoto funkciju) un ievadiet tā argumentus:
Kā regulāra izteiksme (arguments regex) veidne, ko mēs izmantojam:
(d{3,5}|d{2}.d{2}.d{4})
... tulkots cilvēku valodā nozīmē:
cipari no 3 līdz 5 cipariem (konta numuri)
or
formas "2 bitu skaitlis - punkts - 2 bitu skaitlis - punkts - 4 bitu skaitlis" fragmenti, tas ir, datumi formā DD.MM.GGGG.
Kā norobežojošais raksturs (arguments atdalīt) ievadiet semikolu.
Pēc noklikšķināšanas uz OK mūsu burvju funkcija analizē visus sākotnējos datus saskaņā ar mūsu regulāro izteiksmi un veido mums kolonnu ar atrastajiem rēķinu numuriem un datumiem:
Atliek to atdalīt ar semikolu, izmantojot komandu Sākums — sadalīta kolonna — pēc norobežotāja (Sākums — Sadalīta kolonna — Pēc norobežotāja) un mēs iegūstam to, ko gribējām:
Skaistums!
2. piemērs: izņemiet e-pasta adreses no teksta
Pieņemsim, ka mums ir šāda tabula kā sākotnējie dati:
... no kurienes mums ir jāizvelk tur atrastās e-pasta adreses (skaidrības labad tekstā tās iezīmēju sarkanā krāsā).
Tāpat kā iepriekšējā piemērā, mēs ielādējam tabulu programmā Power Query standarta veidā, izmantojot Dati — no tabulas/diapazona (Dati — no Tspējīgs/Reņģelis).
Tad mēs pievienojam aprēķināto kolonnu ar mūsu funkciju via Pievienot kolonnu — izsaukt pielāgoto funkciju (Pievienot kolonnu — izsaukt pielāgoto funkciju) un ievadiet tā argumentus:
E-pasta adrešu parsēšana ir sarežģītāks uzdevums, un, lai to atrisinātu, ir daudz regulāru izteiksmju ar dažādas pakāpes murgiem. Es izmantoju vienu no vienkāršajām iespējām – ne ideāli, bet vairumā gadījumu diezgan darbojošos:
[w|.|-]*@w*.[w|.]*
Kā atdalītājs (atdalīt) varat ievadīt semikolu un atstarpi.
Noklikšķiniet uz OK un mēs iegūstam kolonnu ar e-pasta adresēm, kas izvilktas no oriģinālā teksta “putra”:
Burvju!
PS
Kā saka: "Nav tādas labas lietas, ko nevarētu padarīt vēl labāku." Power Query ir foršs pats par sevi, un, apvienojot to ar regulārām izteiksmēm, tas sniedz mums pilnīgi nereālu jaudu un elastību jebkādu teksta datu apstrādē. Es ceru, ka Microsoft kādreiz pievienos RegExp atbalstu Power Query un Power BI atjauninājumos, un visas iepriekš minētās dejas ar tamburīnu kļūs par pagātni. Nu, pagaidām jā.
Es arī vēlos piebilst, ka ir ērti spēlēt ar regulārām izteiksmēm vietnē https://regexr.com/ – tieši tiešsaistes redaktorā. Tur sadaļā Kopienas modeļi Ir milzīgs skaits gatavu regulāro sezonu visiem gadījumiem. Eksperimentējiet — visas regulāro izteiksmju iespējas tagad ir jūsu rīcībā Power Query!
- Kas ir regulārās izteiksmes (RegExp) un kā tās izmantot programmā Excel
- Izplūdusi teksta meklēšana programmā Power Query
- Tabulu salikšana no dažādiem failiem, izmantojot Power Query