Werrej
Jekk int mill-inqas kemmxejn familjari ma 'espressjonijiet regolari, allura m'għandekx bżonn tirreklamahom. Jekk m'intix pjuttost fis-suġġett, allura espressjonijiet regolari (Regular Expressions = RegExp = "regexps" = "regulars") hija lingwa fejn, bl-użu ta 'karattri u regoli speċjali, is-substrings meħtieġa huma mfittxija fit-test, huma estratti jew mibdula b'test ieħor . Din hija għodda qawwija ħafna u sabiħa, ordni ta 'kobor superjuri għall-modi l-oħra kollha ta' kif taħdem mat-test.
Diġà ddeskrivejt fid-dettall u b'ħafna eżempji mill-ħajja kif tista 'żżid appoġġ ta' espressjoni regolari ma 'Excel billi tuża macros sempliċi - jekk ma qrajtx dan l-artikolu, nirrakkomanda ħafna li taqrah qabel ma tkompli. Se tiskopri ħafna affarijiet ġodda, niggarantixxi 🙂
Madankollu, il-mistoqsija tibqa 'miftuħa - kif iżżid il-kapaċità li tuża espressjonijiet regolari fil-Power Query? Power Query, ovvjament, huwa tajjeb waħdu u jista 'jagħmel ħafna bit-test (qtugħ, inkullar, tindif, eċċ.), Imma jekk tista' taqsamha bil-qawwa ta 'espressjonijiet regolari, tkun biss bomba.
Sfortunatament, m'hemm l-ebda funzjonijiet integrati biex taħdem ma 'RegExps f'Power Query, u l-għajnuna u l-appoġġ tekniku uffiċjali ta' Microsoft iwieġbu din il-mistoqsija fin-negattiv. Madankollu, hemm mod kif madwar din il-limitazzjoni 🙂
L-essenza tal-metodu
L-idea ewlenija hija sempliċi biex tiddisgrace.
Fil-lista ta 'kapaċitajiet ta' Mistoqsija tal-Enerġija integrati, hemm funzjoni Web.Paġna. Id-deskrizzjoni ta’ din il-funzjoni fuq is-sit ta’ għajnuna uffiċjali ta’ Microsoft hija estremament konċiża:
Tradott, dan ikun: "Jirritorna l-kontenut tad-dokument HTML imqassam fl-istrutturi komponenti tiegħu, kif ukoll rappreżentazzjoni tad-dokument sħiħ u l-korp tiegħu wara li t-tikketti jkunu tneħħew." So-so deskrizzjoni, franchement.
Normalment din il-funzjoni tintuża meta timporta data mill-web u tiġi sostitwita awtomatikament, pereżempju, meta nagħżlu fuq it-tab Data Kmand Mill-Internet (Data — Mill-web). Aħna nagħtu lill-funzjoni paġna web bħala argument, u tirritorna l-kontenut tagħha lilna fil-forma ta 'tabelli, wara li qabel neħħiet it-tikketti kollha.
Dak li l-għajnuna MA tgħid huwa li minbarra l-lingwa tal-markup HTML funzjoni Web.Paġna jappoġġja skripts JavaScript, li issa tinsab kullimkien fuq websajts fuq l-Internet. U JavaScript, min-naħa tiegħu, dejjem kien kapaċi jaħdem ma 'espressjonijiet regolari u għandu funzjonijiet mibnija għal RegExps! Allura biex nimplimentaw espressjonijiet regolari f'Power Query, ser ikollna bżonn nagħtu l-funzjonijiet Web.Page bħala argument għal programm żgħir JavaScript li se jagħmel ix-xogħol kollu għal Power Query.
Kif jidher f'JavaScript pur
Hemm ħafna tutorials dettaljati dwar il-ħidma ma 'espressjonijiet regolari fil-JavaScript fuq l-Internet (per eżempju, wieħed, tnejn).
Fil-qosor u ssimplifikat, il-kodiċi JavaScript se jidher bħal dan:
Hawnhekk:
- var str = 'Ħallas kontijiet 123 u 789 għaz-zalzett'; – toħloq varjabbli str u jassenjah it-test sors li se nanalizzaw.
- var mudell = /d+/gi; – toħloq espressjoni regolari u poġġiha f'varjabbli mudell.
L-espressjoni tibda bi mmejla (/).
L-espressjoni nnifisha hawn, pereżempju, hija d+ tirrappreżenta kwalunkwe sekwenza ta' ċifri.
Permezz tal-frazzjoni wara l-espressjoni, hemm parametri ta’ tfittxija addizzjonali (modifikaturi) – jistgħu jiġu speċifikati fi kwalunkwe ordni:
- g – tfisser tfittxija globali, jiġifieri wara li ssib taqbila, m'għandekx tieqaf, iżda tkompli t-tfittxija sa tmiem it-test. Jekk dan il-modifikatur ma jkunx issettjat, allura l-iskript tagħna se jirritorna biss l-ewwel logħba (123)
- i – tfittxija mingħajr ma jitqies il-każ tal-ittri
- m – tfittxija b'ħafna linji (użata meta t-test sors huwa maqsum f'diversi linji)
- var result = str.match(pattern).join(';'); – agħmel tfittxija fit-test sors (str) bl-espressjoni regolari mogħtija (mudell) u poġġi r-riżultati f'varjabbli tirriżulta, jgħaqqadhom b'punt u virgola bl-użu tal-kmand jingħaqdu
- document.write(riżultat); – juri l-kontenut tal-varjabbli tar-riżultat
Innota wkoll li strings tat-test (esklużi espressjonijiet regolari) f'JavaScript huma magħluqa f'apostrofi, mhux kwotazzjonijiet kif inhuma f'Power Query jew VBA.
Fl-output, din l-iskrittura se tagħtina bħala riżultat in-numri kollha misjuba fit-test sors:
123, 789
Il-kors qasir ta' JavaScript spiċċa, grazzi kollha. Nispera li tikseb il-loġika 🙂
Għad fadal li tittrasferixxi din il-kostruzzjoni lil Power Query.
Fittex u Oħroġ il-Funzjoni tat-Test b'Espressjoni Regolari f'Mistoqsija dwar l-Enerġija
Aħna nagħmlu dan li ġej:
1. Iftaħ Excel u oħloq Power Query vojta ġdida fit-tab Dejta – Ikseb data / Oħloq talba – Minn sorsi oħra – Talba vojta (Dejta — Ikseb data / Mistoqsija ġdida — Minn sorsi oħra — Mistoqsija vojta). Jekk għandek verżjoni l-qadima ta 'Excel 2010-2013 u Power Query m'għandekx built-in, iżda ġie installat bħala add-in separat, allura dan kollu se jkun fuq it-tab Mistoqsija dwar l-EnerġijaU le Data.
2. Fit-tieqa vojta tal-editur tal-mistoqsijiet li tiftaħ, fil-pannell tal-lemin, daħħal immedjatament l-isem tal-funzjoni futura tagħna (per eżempju, fxRegExpExtract)
3. Ejja mmorru fit-tab Ara – Editur Avvanzat (Ara — Editur Avvanzat), aħna nħassru l-kodiċi M kollu tat-talba vojta u nwaħħal il-kodiċi tas-superfunzjoni tagħna hemmhekk:
Oqgħod attent idejk:
Fl-ewwel linja, ngħidu li l-funzjoni tagħna se jkollha tliet argumenti tat-test: txt – it-test oriġinali li qed jiġi analizzat, regex – mudell ta' espressjoni regolari, delim — karattru delimitatur għall-wiri tar-riżultati.
Sussegwentement insejħu l-funzjoni Web.Paġna, li jiffurmaw il-kodiċi JavaScript deskritt hawn fuq fl-argument tiegħu. Aħna paste u nissostitwixxu l-argumenti varjabbli tagħna fil-kodiċi.
Framment:
[Dejta]{0}[Tfal]{0}[Tfal]{1}[Test]{0}
... hija meħtieġa biex "taqgħu" fit-tabella bir-riżultati li neħtieġu. Il-punt huwa li l-funzjoni Web.Paġna bħala riżultat, jipproduċi diversi tabelli nested li jirrepetu l-istruttura ta 'paġna web. Mingħajr din il-biċċa tal-kodiċi M, il-funzjoni tagħna toħroġ dan:
… u jkollna nikklikkjaw il-kelma diversi drabi Tabella, suċċessivament "taqa '" f'tabelli nedjati tfal f'kolonni Tfal:
Minflok din il-kwotazzjoni kollha, aħna immedjatament nindikaw fil-kodiċi tal-funzjoni tagħna liema tabella u kolonna nested (Test) għandna bżonn.
Hawn, fil-fatt, huma s-sigrieti kollha. Fadal biex tagħfas il-buttuna Finatura fit-tieqa editur avvanzat, fejn daħħalna l-kodiċi tagħna, u tista 'tipproċedi għall-aktar Delicious - ipprova l-funzjoni tagħna fuq ix-xogħol.
Hawn huma ftit eżempji taż-żerriegħa.
Eżempju 1. Irkupru tan-numru tal-kont u d-data mid-deskrizzjoni tal-ħlas
Għandna rendikont bankarju b'deskrizzjoni (għan) tal-ħlasijiet, fejn għandek bżonn tiġbed in-numri u d-dati tal-fatturi mħallsa f'kolonni separati:
Aħna tagħbija t-tabella f'Power Query bil-mod standard Dejta – Minn Tabella/Firxa (Dejta — Minn Tkapaċi/Ranġlu).
Imbagħad inżidu kolonna kkalkulata bil-funzjoni tagħna permezz Żid Kolonna - Sejħa Funzjoni Custom (Żid Kolonna — Invoka Funzjoni Custom) u daħħal l-argumenti tagħha:
Bħala espressjoni regolari (argument regex) mudell li nużaw:
(d{3,5}|d{2}.d{2}.d{4})
… tradott fil-lingwa umana li jfisser:
numri minn 3 sa 5 ċifri (numri tal-kont)
or
frammenti tal-forma “numru 2-bit – punt – numru 2-bit – punt – numru 4-bit”, jiġifieri, dati tal-formola JJ.XX.SSSS.
Bħala karattru delimitatur (argument delim) daħħal punt u virgola.
Wara li tikklikkja fuq OK Il-funzjoni maġika tagħna tanalizza d-dejta inizjali kollha skont l-espressjoni regolari tagħna u tifforma kolonna għalina bin-numri misjuba u d-dati tal-fatturi:
Jibqa' li tisseparaha b'punt u virgola bl-użu tal-kmand Home — Kolonna maqsuma — Permezz tad-Delimitatur (Dar — Kolonna maqsuma — Permezz ta' delimitatur) u niksbu dak li ridna:
Sbuħija!
Eżempju 2: Oħroġ indirizzi tal-email mit-test
Ejja ngħidu li għandna t-tabella li ġejja bħala data inizjali:
… minn fejn irridu noħorġu l-indirizzi tal-email li nstabu hemmhekk (għal ċarezza, enfasizzajhom bl-aħmar fit-test).
Bħal fl-eżempju preċedenti, aħna tagħbija t-tabella fis-Power Query bil-mod standard permezz Dejta – Minn Tabella/Firxa (Dejta — Minn Tkapaċi/Ranġlu).
Imbagħad inżidu kolonna kkalkulata bil-funzjoni tagħna permezz Żid Kolonna - Sejħa Funzjoni Custom (Żid Kolonna — Invoka Funzjoni Custom) u daħħal l-argumenti tagħha:
L-analiżi tal-indirizzi tal-email hija biċċa xogħol aktar diffiċli u hemm mazz ta 'espressjonijiet regolari ta' gradi varji ta 'ħmar il-lejl biex issolviha. Jien użajt waħda mill-għażliet sempliċi - mhux ideali, iżda pjuttost taħdem f'ħafna każijiet:
[w|.|-]*@w*.[w|.]*
Bħala separatur (delim) tista' ddaħħal punt u virgola u spazju.
Ikklikkja fuq OK u jkollna kolonna b'indirizzi tal-posta elettronika estratti mit-test oriġinali "porridge":
Maġika!
PS
Kif jgħid il-qal: “M’hemm l-ebda ħaġa tajba bħal din li ma setgħetx issir saħansitra aħjar.” Power Query huwa jibred waħdu, u meta kkombinat ma 'espressjonijiet regolari, jagħtina setgħa u flessibilità kompletament mhux realistiċi fl-ipproċessar ta' kwalunkwe dejta tat-test. Nittama li Microsoft xi darba żżid l-appoġġ ta' RegExp fl-aġġornamenti ta' Power Query u Power BI u ż-żfin kollha ta' hawn fuq b'tamburin isiru xi ħaġa tal-passat. Ukoll, għalissa, iva.
Irrid inżid ukoll li huwa konvenjenti li tilgħab bl-espressjonijiet regolari fuq is-sit https://regexr.com/ – dritt fl-editur onlajn. Hemm fit-taqsima Mudelli tal-Komunità Hemm numru kbir ta 'staġuni regolari lesti għall-okkażjonijiet kollha. Esperiment - il-qawwa kollha tal-espressjonijiet regolari issa hija għas-servizz tiegħek f'Power Query!
- X'inhuma l-espressjonijiet regolari (RegExp) u kif tużahom f'Excel
- Tiftix tat-test fuzzy fil-Power Query
- Assemblaġġ ta' tabelli minn fajls differenti bl-użu ta' Power Query