Cuprins
Dacă sunteți cel puțin puțin familiarizat cu expresiile regulate, atunci nu trebuie să le faceți publicitate. Dacă nu sunteți chiar în subiect, atunci expresiile regulate (Expresii regulate = RegExp = „regexps” = „regulars”) este o limbă în care, folosind caractere și reguli speciale, se caută în text subșirurile necesare, sunt extrase. sau înlocuit cu alt text. Acesta este un instrument foarte puternic și frumos, un ordin de mărime superior tuturor celorlalte moduri de lucru cu textul.
Am descris deja în detaliu și cu o grămadă de exemple din viață cum puteți adăuga suport pentru expresii regulate la Excel folosind macrocomenzi simple – dacă nu ați citit acest articol, vă recomand cu căldură să îl citiți înainte de a continua. Veți descoperi o mulțime de lucruri noi, vă garantez 🙂
Cu toate acestea, întrebarea rămâne deschisă - cum să adăugați capacitatea de a folosi expresii regulate în Power Query? Power Query, desigur, este bun de la sine și poate face multe cu text (tăiere, lipire, curățare etc.), dar dacă l-ai putea încrucișa cu puterea expresiilor regulate, ar fi doar o bombă.
Din păcate, nu există funcții încorporate pentru lucrul cu RegExps în Power Query, iar ajutorul oficial Microsoft și asistența tehnică răspund la această întrebare în mod negativ. Cu toate acestea, există o cale de a ocoli această limitare 🙂
Esența metodei
Ideea principală este simplu de dezamăgit.
În lista de capabilități Power Query încorporate, există o funcție Pagină web. Descrierea acestei funcții pe site-ul oficial de ajutor Microsoft este extrem de concisă:
Tradus, acesta ar fi: „Returnează conținutul documentului HTML defalcat în structurile sale componente, precum și o reprezentare a documentului complet și a corpului său după ce etichetele au fost eliminate.” Așa așa descriere, sincer.
De obicei, această funcție este folosită la importul de date de pe web și este înlocuită automat, de exemplu, când selectăm din filă Date Comandă De pe internet (Date - De pe web). Oferim funcției o pagină web ca argument și ne returnează conținutul ei sub formă de tabele, după ce în prealabil a șters toate etichetele.
Ceea ce ajutorul NU spune este că, pe lângă limbajul de marcare HTML funcţie Pagină web acceptă scripturi JavaScript, care este acum omniprezent pe site-urile web de pe Internet. Și JavaScript, la rândul său, a fost întotdeauna capabil să funcționeze cu expresii regulate și are funcții încorporate pentru RegExps! Deci, pentru a implementa expresii regulate în Power Query, va trebui să alimentam funcțiile Web.Page ca argument pentru un mic program JavaScript care va face toată munca pentru Power Query.
Cum arată în JavaScript pur
Există o mulțime de tutoriale detaliate despre lucrul cu expresii regulate în JavaScript pe Internet (de exemplu, unu, doi).
Pe scurt și simplificat, codul JavaScript va arăta astfel:
Aici:
- var str = 'Plătește facturile 123 și 789 pentru cârnați'; – creați o variabilă str și atribuiți-i textul sursă pe care îl vom analiza.
- var model = /d+/gi; – creați o expresie regulată și puneți-o într-o variabilă model.
Expresia începe cu o bară oblică (/).
Expresia însăși aici, de exemplu, este d+ reprezintă orice succesiune de cifre.
Prin fracția de după expresie există parametri de căutare suplimentari (modificatori) – pot fi specificați în orice ordine:
- g – înseamnă căutare globală, adică după găsirea unei potriviri, nu trebuie să te oprești, ci să continui căutarea până la sfârșitul textului. Dacă acest modificator nu este setat, atunci scriptul nostru va returna doar prima potrivire (123)
- i – căutare fără a ține seama de cazul literelor
- m – căutare pe mai multe rânduri (utilizată atunci când textul sursă este împărțit în mai multe rânduri)
- var rezultat = str.match(pattern).join(';'); – efectuați o căutare în textul sursă (str) prin expresia regulată dată (model) și puneți rezultatele într-o variabilă rezultat, concatenându-le cu punct și virgulă folosind comanda alătura
- document.write(rezultat); – afișați conținutul variabilei rezultat
De asemenea, rețineți că șirurile de text (excluzând expresiile regulate) în JavaScript sunt incluse în apostrofe, nu ghilimele, așa cum sunt în Power Query sau VBA.
La ieșire, acest script ne va oferi ca rezultat toate numerele găsite în textul sursă:
123, 789
Cursul scurt JavaScript sa încheiat, vă mulțumesc tuturor. Sper că înțelegeți logica 🙂
Rămâne să transferăm această construcție la Power Query.
Funcția de căutare și extragere a textului prin expresie regulată în Power Query
Facem următoarele:
1. Deschideți Excel și creați un nou Power Query gol în filă Date – Obțineți date / Creați cerere – Din alte surse – Solicitare goală (Date — Obține date / Interogare nouă — Din alte surse — Interogare goală). Dacă aveți o versiune veche de Excel 2010-2013 și Power Query pe care nu o aveți încorporată, dar a fost instalată ca supliment separat, atunci toate acestea vor fi în filă Interogare de alimentareSi nu Date.
2. În fereastra goală a editorului de interogări care se deschide, în panoul din dreapta, introduceți imediat numele funcției viitoare (de exemplu, fxRegExpExtract)
3. Să mergem la filă Vizualizare – Editor avansat (Vizualizare — Editor avansat), ștergem întregul cod M al cererii goale și lipim acolo codul superfuncției noastre:
Ai grijă la mâini:
În prima linie, spunem că funcția noastră va avea trei argumente text: txt – se analizează textul original, regex – model de expresie regulată, delim — caracter delimitator pentru afișarea rezultatelor.
Apoi numim funcția Pagină web, formând codul JavaScript descris mai sus în argumentul său. Lipim și substituim argumentele noastre variabile în cod.
Fragment:
[Date]{0}[Copii]{0}[Copii]{1}[Text]{0}
… este necesar să „cadă” în tabelul cu rezultatele de care avem nevoie. Ideea este că funcția Pagină web ca urmare, produce mai multe tabele imbricate care repetă structura unei pagini web. Fără această bucată de cod M, funcția noastră ar scoate următoarele:
… și ar trebui să facem clic pe cuvânt de mai multe ori Tabel, „căzând” succesiv în tabele imbricate copii în coloane Copii:
În loc de toate aceste citate, indicăm imediat în codul funcției noastre care tabel și coloană imbricate (Text) avem nevoie.
Aici, de fapt, sunt toate secretele. Rămâne să apăsați butonul finalizarea La fereastră editor avansat, unde am introdus codul nostru și puteți trece la cel mai delicios – încercați funcția noastră la serviciu.
Iată câteva exemple de semințe.
Exemplul 1. Preluarea numărului de cont și a datei din descrierea plății
Avem un extras de cont bancar cu o descriere (scopul) plăților, în care trebuie să extrageți numerele și datele facturilor plătite în coloane separate:
Încărcăm tabelul în Power Query în modul standard Date – Din tabel/gamă (Date - de la Tcapabil/Rînger).
Apoi adăugăm o coloană calculată cu funcția noastră via Adăugați coloană – Apelați funcție personalizată (Adăugați o coloană — Invocați funcția personalizată) și introduceți argumentele sale:
Ca expresie regulată (argument regex) șablon pe care îl folosim:
(d{3,5}|d{2}.d{2}.d{4})
… tradus în limbajul uman însemnând:
numere de la 3 la 5 cifre (numere de cont)
or
fragmente de forma „număr de 2 biți – punct – număr de 2 biți – punct – număr de 4 biți”, adică datele din formularul ZZ.LL.AAAA.
Ca caracter delimitator (argument delim) introduceți punct și virgulă.
După ce faceți clic pe OK funcția noastră magică analizează toate datele inițiale conform expresiei noastre regulate și formează o coloană pentru noi cu numerele și datele găsite ale facturilor:
Rămâne să-l separăm prin punct și virgulă folosind comanda Acasă — Împărțire coloană — După delimitare (Acasă — Împărțire coloană — După delimitator) și obținem ceea ce ne-am dorit:
Frumuseţe!
Exemplul 2: extrageți adresele de e-mail din text
Să presupunem că avem următorul tabel ca date inițiale:
… de unde trebuie să scoatem adresele de e-mail găsite acolo (pentru claritate, le-am evidențiat cu roșu în text).
Ca și în exemplul anterior, încărcăm tabelul în Power Query în mod standard prin Date – Din tabel/gamă (Date - de la Tcapabil/Rînger).
Apoi adăugăm o coloană calculată cu funcția noastră via Adăugați coloană – Apelați funcție personalizată (Adăugați o coloană — Invocați funcția personalizată) și introduceți argumentele sale:
Analizarea adreselor de e-mail este o sarcină mai dificilă și există o grămadă de expresii regulate de diferite grade de coșmar pentru a o rezolva. Am folosit una dintre opțiunile simple – nu ideală, dar destul de funcțională în majoritatea cazurilor:
[w|.|-]*@w*.[w|.]*
Ca separator (delim) puteți introduce un punct și virgulă și un spațiu.
Faceţi clic pe OK și obținem o coloană cu adrese de e-mail extrase din textul original „terci”:
Magie!
PS
După cum se spune: „Nu există un lucru atât de bun care să nu poată fi făcut și mai bun.” Power Query este grozav în sine și, atunci când este combinat cu expresii regulate, ne oferă putere și flexibilitate complet nerealistă în procesarea oricăror date text. Sper că Microsoft va adăuga într-o zi suport RegExp în actualizările Power Query și Power BI și toate dansurile de mai sus cu o tamburină vor deveni un lucru din trecut. Ei bine, deocamdată, da.
De asemenea, vreau să adaug că este convenabil să te joci cu expresii regulate pe site-ul https://regexr.com/ – chiar în editorul online. Acolo in sectiune Modele comunitare Există un număr mare de sezoane regulate gata făcute pentru toate ocaziile. Experimentați – toată puterea expresiilor regulate este acum la dispoziție în Power Query!
- Ce sunt expresiile regulate (RegExp) și cum să le folosești în Excel
- Căutare de text neclar în Power Query
- Asamblarea tabelelor din diferite fișiere folosind Power Query