Përmbajtje
Nëse jeni të paktën pak të njohur me shprehjet e rregullta, atëherë nuk keni nevojë t'i reklamoni ato. Nëse nuk jeni plotësisht në temë, atëherë shprehjet e rregullta (Shprehjet e rregullta = RegExp = "regexps" = "të rregullta") është një gjuhë ku, duke përdorur karaktere dhe rregulla të veçanta, kërkohen nënvargjet e nevojshme në tekst, ato nxirren. ose zëvendësohet me tekst tjetër. Ky është një mjet shumë i fuqishëm dhe i bukur, një rend i madhësisë më i lartë se të gjitha mënyrat e tjera të punës me tekst.
Unë kam përshkruar tashmë në detaje dhe me një mori shembujsh nga jeta se si mund të shtoni mbështetje të shprehjeve të rregullta në Excel duke përdorur makro të thjeshta - nëse nuk e keni lexuar këtë artikull, ju rekomandoj ta lexoni përpara se të vazhdoni. Do të zbuloni shumë gjëra të reja, ju garantoj 🙂
Sidoqoftë, pyetja mbetet e hapur - si të shtohet aftësia për të përdorur shprehje të rregullta në Power Query? Power Query, natyrisht, është i mirë më vete dhe mund të bëjë shumë me tekst (prerje, ngjitje, pastrim, etj.), por nëse mund ta kaloni me fuqinë e shprehjeve të rregullta, do të ishte thjesht një bombë.
Fatkeqësisht, nuk ka funksione të integruara për të punuar me RegExps në Power Query, dhe ndihma zyrtare dhe mbështetja teknike e Microsoft-it i përgjigjen kësaj pyetjeje negativisht. Sidoqoftë, ekziston një mënyrë për ta shmangur këtë kufizim 🙂
Thelbi i metodës
Ideja kryesore është e thjeshtë deri në turp.
Në listën e aftësive të integruara të Power Query, ekziston një funksion Faqe interneti. Përshkrimi i këtij funksioni në faqen zyrtare të ndihmës së Microsoft-it është jashtëzakonisht konciz:
Përkthyer, kjo do të ishte: "Kthen përmbajtjen e dokumentit HTML të zbërthyer në strukturat përbërëse të tij, si dhe një paraqitje të dokumentit të plotë dhe trupit të tij pasi të jenë hequr etiketat." Përshkrim kaq-kaq, sinqerisht.
Zakonisht ky funksion përdoret kur importoni të dhëna nga uebi dhe zëvendësohet automatikisht, për shembull, kur zgjedhim në skedën Data Komandë Nga interneti (Të dhënat - Nga ueb). Ne i japim funksionit një faqe interneti si argument dhe ai na e kthen përmbajtjen e tij në formën e tabelave, pasi i kemi pastruar më parë të gjitha etiketat.
Ajo që NUK thotë ndihma është se përveç gjuhës së shënjimit HTML funksion Faqe interneti mbështet skriptet JavaScript, i cili tani është i kudondodhur në faqet e internetit në internet. Dhe JavaScript, nga ana tjetër, ka qenë gjithmonë në gjendje të punojë me shprehje të rregullta dhe ka funksione të integruara për RegExps! Pra, për të zbatuar shprehje të rregullta në Power Query, do të na duhet të ushqejmë funksionet Web.Page si një argument për një program të vogël JavaScript që do të bëjë të gjithë punën për Power Query.
Si duket në JavaScript të pastër
Ka shumë mësime të detajuara për të punuar me shprehje të rregullta në JavaScript në internet (për shembull, një, dy).
Shkurtimisht dhe thjeshtuar, kodi JavaScript do të duket kështu:
këtu:
- var str = 'Paguaj faturat 123 dhe 789 për sallamin'; – krijoni një variabël str dhe caktojeni tekstin burimor që do të analizojmë.
- var model = /d+/gi; – krijoni një shprehje të rregullt dhe vendoseni në një ndryshore model.
Shprehja fillon me një të pjerrët (/).
Vetë shprehja këtu, për shembull, është d+ qëndron për çdo sekuencë shifrash.
Përmes fraksionit pas shprehjes, ka parametra shtesë të kërkimit (modifikues) - ato mund të specifikohen në çdo mënyrë:
- g – nënkupton kërkimin global, dmth pas gjetjes së përputhjes nuk duhet të ndalesh, por të vazhdosh kërkimin deri në fund të tekstit. Nëse ky modifikues nuk është vendosur, atëherë skripti ynë do të kthejë vetëm përputhjen e parë (123)
- i – kërko pa marrë parasysh shkronjat
- m - Kërkimi me shumë rreshta (përdoret kur teksti burimor ndahet në disa rreshta)
- var rezultat = str.match(modeli).join(';'); – kryeni një kërkim në tekstin burimor (str) nga shprehja e rregullt e dhënë (model) dhe vendosni rezultatet në një ndryshore rezultat, duke i bashkuar me një pikëpresje duke përdorur komandën të bashkohen
- dokument.shkruaj(rezultat); – shfaqni përmbajtjen e ndryshores së rezultatit
Gjithashtu vini re se vargjet e tekstit (me përjashtim të shprehjeve të rregullta) në JavaScript janë të mbyllura në apostrofa, jo thonjëza siç janë në Power Query ose VBA.
Në dalje, ky skript do të na japë si rezultat të gjithë numrat e gjetur në tekstin burimor:
123, 789
Kursi i shkurtër i JavaScript ka përfunduar, faleminderit të gjithëve. Shpresoj ta keni kuptuar logjikën 🙂
Mbetet për ta transferuar këtë ndërtim në Power Query.
Kërkimi dhe nxjerrja e funksionit të tekstit sipas shprehjes së rregullt në Power Query
Ne bëjmë sa vijon:
1. Hapni Excel dhe krijoni një Power Query të re bosh në skedën Të dhënat – Merr të dhëna / Krijo kërkesë – Nga burime të tjera – Kërkesë boshe (Të dhënat — Merrni të dhëna / Pyetje e re — Nga burime të tjera — Pyetje bosh). Nëse keni një version të vjetër të Excel 2010-2013 dhe Power Query që nuk e keni të integruar, por është instaluar si një shtesë e veçantë, atëherë e gjithë kjo do të jetë në skedën Kërkesa e energjisëDhe jo Data.
2. Në dritaren e zbrazët të redaktuesit të pyetjeve që hapet, në panelin e djathtë, futni menjëherë emrin e funksionit tonë të ardhshëm (për shembull, fxRegExpExtract)
3. Le të shkojmë te skeda Shiko - Redaktues i avancuar (Shiko - Redaktues i avancuar), fshijmë të gjithë kodin M të kërkesës boshe dhe ngjisim kodin e superfunksionit tonë atje:
Kujdesuni për duart tuaja:
Në rreshtin e parë, themi se funksioni ynë do të ketë tre argumente teksti: txt – teksti origjinal që po analizohet, regex - modeli i shprehjes së rregullt, delim — karakteri kufizues për shfaqjen e rezultateve.
Më pas e quajmë funksionin Faqe interneti, duke formuar kodin JavaScript të përshkruar më sipër në argumentin e tij. Ne ngjitim dhe zëvendësojmë argumentet tona të ndryshueshme në kod.
Fragment:
[Të dhënat]{0}[Fëmijët]{0}[Fëmijët]{1}[Text]{0}
… është e nevojshme për të "përmbysur" në tabelë me rezultatet që na duhen. Çështja është se funksioni Faqe interneti si rezultat, ai prodhon disa tabela të mbivendosura që përsërisin strukturën e një faqe interneti. Pa këtë pjesë të kodit M, funksioni ynë do të nxirrte këtë:
… dhe ne do të duhet të klikojmë fjalën disa herë Tryezë, "duke rënë" në mënyrë të njëpasnjëshme në tabelat e mbivendosura të fëmijëve në kolona Fëmijë:
Në vend të gjithë këtij citimi, ne menjëherë tregojmë në kodin e funksionit tonë se cilën tabelë dhe kolonë të mbivendosur (Tekst) na duhen.
Këtu, në fakt, janë të gjitha sekretet. Mbetet të shtypni butonin Fund në dritare redaktues i avancuar, ku kemi futur kodin tonë, dhe ju mund të vazhdoni tek më e shijshmja – provoni funksionin tonë në punë.
Këtu janë disa shembuj të farës.
Shembull 1. Marrja e numrit të llogarisë dhe datës nga përshkrimi i pagesës
Ne kemi një deklaratë bankare me një përshkrim (qëllim) të pagesave, ku duhet të nxirrni numrat dhe datat e faturave të paguara në kolona të veçanta:
Ne ngarkojmë tabelën në Power Query në mënyrën standarde Të dhënat – Nga tabela/vargu (Të dhënat - Nga Ti aftë/Rengjëll).
Pastaj shtojmë një kolonë të llogaritur me funksionin tonë nëpërmjet Shto kolonë - Thirrni funksionin e personalizuar (Shto kolonë - Thirr funksionin e personalizuar) dhe shkruani argumentet e tij:
Si shprehje e rregullt (argument regex) shabllon që përdorim:
(d{3,5}|d{2}.d{2}.d{4})
… përkthyer në gjuhën njerëzore do të thotë:
numrat nga 3 deri në 5 shifra (numrat e llogarisë)
or
fragmente të formës “numër 2-bit – pikë – numër 2-bit – pikë – numër 4-bit”, pra datat e formës DD.MM.VVVV.
Si karakter përcaktues (argument delim) shkruani një pikëpresje.
Pas klikimit OK funksioni ynë magjik analizon të gjitha të dhënat fillestare sipas shprehjes sonë të rregullt dhe formon një kolonë për ne me numrat dhe datat e gjetura të faturave:
Mbetet për ta ndarë atë me pikëpresje duke përdorur komandën Faqja kryesore — Kolona e ndarë — Nga kufizuesi (Faqja kryesore — Kolona e ndarë — Sipas kufizuesit) dhe ne marrim atë që donim:
Bukuroshja!
Shembulli 2: Nxjerrja e adresave të emailit nga teksti
Supozoni se kemi tabelën e mëposhtme si të dhëna fillestare:
… nga ku duhet të nxjerrim adresat e emailit të gjetura atje (për qartësi, i theksova me të kuqe në tekst).
Ashtu si në shembullin e mëparshëm, ne ngarkojmë tabelën në Power Query në mënyrën standarde nëpërmjet Të dhënat – Nga tabela/vargu (Të dhënat - Nga Ti aftë/Rengjëll).
Pastaj shtojmë një kolonë të llogaritur me funksionin tonë nëpërmjet Shto kolonë - Thirrni funksionin e personalizuar (Shto kolonë - Thirr funksionin e personalizuar) dhe shkruani argumentet e tij:
Analizimi i adresave të emailit është një detyrë më e vështirë dhe ka një mori shprehjesh të rregullta të shkallëve të ndryshme të makthit për ta zgjidhur atë. Kam përdorur një nga opsionet e thjeshta - jo ideale, por mjaft funksionale në shumicën e rasteve:
[w|.|-]*@w*.[w|.]*
Si ndarës (delim) mund të futni një pikëpresje dhe një hapësirë.
Kliko në OK dhe marrim një kolonë me adresat e postës elektronike të nxjerra nga teksti origjinal "qull":
Magjike!
PS
Siç thotë shprehja: "Nuk ka një gjë kaq të mirë që nuk mund të bëhet edhe më mirë." Power Query është më vete dhe kur kombinohet me shprehje të rregullta, na jep fuqi dhe fleksibilitet krejtësisht jorealiste në përpunimin e të dhënave të tekstit. Shpresoj që Microsoft një ditë të shtojë mbështetjen RegExp në përditësimet e Power Query dhe Power BI dhe të gjitha kërcimet e mësipërme me një dajre do të bëhen një gjë e së kaluarës. Epo, tani për tani, po.
Unë gjithashtu dua të shtoj se është e përshtatshme të luash me shprehje të rregullta në faqen https://regexr.com/ – pikërisht në redaktorin në internet. Aty në seksion Modelet e komunitetit Ka një numër të madh sezonesh të rregullta të gatshme për të gjitha rastet. Eksperimentoni - e gjithë fuqia e shprehjeve të rregullta është tani në shërbimin tuaj në Power Query!
- Cilat janë shprehjet e rregullta (RegExp) dhe si t'i përdorim ato në Excel
- Kërkimi i tekstit fuzzy në Power Query
- Montimi i tabelave nga skedarë të ndryshëm duke përdorur Power Query