Turinys
Jei esate bent šiek tiek susipažinę su reguliariosiomis išraiškomis, jums nereikia jų reklamuoti. Jei nesate visiškai susipažinę su tema, tai reguliarūs posakiai (Regular Expressions = RegExp = "regexps" = "reguliarus") yra kalba, kurioje naudojant specialiuosius simbolius ir taisykles tekste ieškoma reikiamų poeilučių, jos išgaunamos. arba pakeistas kitu tekstu . Tai labai galingas ir gražus įrankis, daug geresnis už visus kitus darbo su tekstu būdus.
Jau išsamiai ir su krūva gyvenimo pavyzdžių aprašiau, kaip naudojant paprastas makrokomandas galima pridėti reguliariųjų reiškinių palaikymą prie Excel – jei neskaitėte šio straipsnio, labai rekomenduoju jį perskaityti prieš tęsiant. Atrasite daug naujo, garantuoju 🙂
Tačiau klausimas lieka atviras – kaip „Power Query“ pridėti galimybę naudoti reguliariąsias išraiškas? Power Query, žinoma, yra geras ir pats savaime ir gali daug nuveikti su tekstu (karpyti, klijuoti, valyti ir t.t.), bet jei pavyktų perbraukti taisyklingųjų posakių galia, tai būtų tiesiog bomba.
Deja, Power Query darbui su RegExps nėra integruotų funkcijų, o oficiali Microsoft pagalba ir techninė pagalba į šį klausimą atsako neigiamai. Tačiau yra būdas apeiti šį apribojimą 🙂
Metodo esmė
Pagrindinė mintis yra paprasta sugėdinti.
Integruotų „Power Query“ galimybių sąraše yra funkcija Tinklo puslapis. Šios funkcijos aprašymas oficialioje „Microsoft“ pagalbos svetainėje yra labai glaustas:
Išvertus tai būtų: „Pateikia HTML dokumento turinį, suskirstytą į jo sudedamąsias struktūras, taip pat viso dokumento ir jo turinio atvaizdavimą pašalinus žymas. Atvirai kalbant, toks aprašymas.
Paprastai ši funkcija naudojama importuojant duomenis iš žiniatinklio ir automatiškai pakeičiama, pavyzdžiui, kai pasirenkame skirtuke Duomenys Komanda Iš interneto (Duomenys – iš žiniatinklio). Kaip argumentą funkcijai pateikiame tinklalapį, o ji grąžina mums savo turinį lentelių pavidalu, prieš tai išvaliusi visas žymas.
Žinynas NĖRA sako, kad be HTML žymėjimo kalbos funkcija Tinklo puslapis palaiko JavaScript scenarijus, kuris dabar yra visur interneto svetainėse. „JavaScript“, savo ruožtu, visada galėjo dirbti su reguliariosiomis išraiškomis ir turi integruotas RegExps funkcijas! Taigi, norėdami įdiegti reguliariąsias išraiškas Power Query, turėsime pateikti Web.Page funkcijas kaip argumentą mažai JavaScript programai, kuri atliks visą Power Query darbą.
Kaip tai atrodo gryname „JavaScript“.
Internete yra daug išsamių vadovėlių, kaip dirbti su reguliariosiomis „JavaScript“ išraiškomis (pavyzdžiui, vienas, du).
Trumpai ir supaprastintai, JavaScript kodas atrodys taip:
Čia:
- var str = 'Apmokėkite 123 ir 789 sąskaitas už dešrą'; – sukurti kintamąjį g ir priskirkite jam šaltinio tekstą, kurį analizuosime.
- var modelis = /d+/gi; – sukurti reguliariąją išraišką ir įdėti ją į kintamąjį modelis.
Išraiška prasideda pasviruoju brūkšniu (/).
Pati išraiška čia, pavyzdžiui, yra d+ reiškia bet kokią skaitmenų seką.
Per trupmeną po išraiškos yra papildomų paieškos parametrų (modifikatorių) – juos galima nurodyti bet kokia tvarka:
- g – reiškia visuotinę paiešką, ty radus atitikmenį reikia nesustoti, o tęsti paiešką iki teksto pabaigos. Jei šis modifikatorius nenustatytas, mūsų scenarijus pateiks tik pirmąją atitiktį (123)
- i – paieška neatsižvelgiant į raidžių raides
- m – kelių eilučių paieška (naudojama, kai šaltinio tekstas padalintas į kelias eilutes)
- var rezultatas = str.match(pattern).join(';'); – atlikti paiešką šaltinio tekste (g) pagal pateiktą reguliariąją išraišką (modelis) ir rezultatus sudėkite į kintamąjį rezultatas, sujungdami juos kabliataškiu naudodami komandą prisijungti
- document.write(result); – rodyti rezultato kintamojo turinį
Taip pat atminkite, kad teksto eilutės (išskyrus reguliariąsias išraiškas) „JavaScript“ yra apostrofos, o ne kabutės, kaip „Power Query“ ar VBA.
Išvestyje šis scenarijus pateiks mums visus skaičius, rastus šaltinio tekste:
123, 789
„JavaScript“ trumpasis kursas baigėsi, ačiū visiems. Tikiuosi supratai logiką 🙂
Belieka šią konstrukciją perkelti į Power Query.
„Power Query“ ieškokite ir ištraukite teksto funkciją pagal reguliariąją išraišką
Mes atliekame šiuos veiksmus:
1. Atidarykite „Excel“ ir skirtuke sukurkite naują tuščią „Power Query“. Duomenys – Gauti duomenis / Sukurti užklausą – Iš kitų šaltinių – Tuščia užklausa (Duomenys – gauti duomenis / nauja užklausa – iš kitų šaltinių – tuščia užklausa). Jei turite seną „Excel 2010–2013“ versiją ir „Power Query“, kurios neturite integruoto, bet buvo įdiegtas kaip atskiras priedas, visa tai bus skirtuke. „Power Query“Ir ne Duomenys.
2. Atsidariusiame tuščiame užklausų rengyklės lange dešiniajame skydelyje iš karto įveskite būsimos funkcijos pavadinimą (pvz., fxRegExpExtract)
3. Eikime į skirtuką Rodinys – išplėstinis redaktorius (Žiūrėti – išplėstinis redaktorius), ištriname visą tuščios užklausos M kodą ir ten įklijuojame savo superfunkcijos kodą:
Stebėkite savo rankas:
Pirmoje eilutėje sakome, kad mūsų funkcija turės tris teksto argumentus: txt – analizuojamas originalus tekstas, reguliariai - reguliarios išraiškos šablonas, atriboti — skiriamasis ženklas rezultatams rodyti.
Toliau vadiname funkciją Tinklo puslapis, sudarydamas anksčiau savo argumente aprašytą JavaScript kodą. Mes įklijuojame ir pakeičiame savo kintamuosius argumentus į kodą.
Fragmentas:
[Duomenys]{0}[Vaikai]{0}[Vaikai]{1}[Tekstas]{0}
… reikia, kad „patektų“ į lentelę su rezultatais, kurių mums reikia. Esmė ta, kad funkcija Tinklo puslapis todėl sukuriamos kelios įdėtos lentelės, kurios pakartoja tinklalapio struktūrą. Be šio M kodo dalies mūsų funkcija išvestų tai:
... ir turėtume kelis kartus spustelėti žodį Lentelė, paeiliui „nukrenta“ į antrines įdėtas lenteles stulpeliuose Vaikai:
Vietoj visos šios citatos savo funkcijos kode iš karto nurodome įdėtą lentelę ir stulpelį (Tekstas) mums reikia.
Tiesą sakant, čia yra visos paslaptys. Belieka paspausti mygtuką apdaila lange išplėstinis redaktorius, kur įdėjome savo kodą, ir galite pereiti prie skaniausio – išbandykite mūsų funkciją darbe.
Štai keletas sėklų pavyzdžių.
1 pavyzdys. Sąskaitos numerio ir datos gavimas iš mokėjimo aprašymo
Turime banko išrašą su mokėjimų aprašymu (paskirtimi), kuriame į atskirus stulpelius reikia ištraukti apmokėtų sąskaitų numerius ir datas:
Lentelę įkeliame į „Power Query“ standartiniu būdu Duomenys – iš lentelės/diapazono (Duomenys – iš Tgalintis/Rangelas).
Tada pridedame apskaičiuotą stulpelį su funkcija via Pridėti stulpelį – iškviesti pasirinktinę funkciją (Pridėti stulpelį – iškviesti pasirinktinę funkciją) ir įveskite savo argumentus:
Kaip reguliarioji išraiška (argumentas reguliariai) šabloną naudojame:
(d{3,5}|d{2}.d{2}.d{4})
... išvertus į žmonių kalbą reiškia:
skaičiai nuo 3 iki 5 skaitmenų (sąskaitos numeriai)
or
formos „2 bitų skaičius – taškas – 2 bitų skaičius – taškas – 4 bitų skaičius“ fragmentai, tai yra DD.MM.YYYY formos datos.
Kaip skiriamasis simbolis (argumentas atriboti) įveskite kabliataškį.
Spustelėję OK mūsų magic funkcija analizuoja visus pradinius duomenis pagal mūsų reguliariąją išraišką ir sudaro mums stulpelį su rastais sąskaitų faktūrų numeriais ir datomis:
Belieka jį atskirti kabliataškiu naudojant komandą Pagrindinis puslapis – Padalytas stulpelis – pagal skyriklį (Pagrindinis – padalintas stulpelis – pagal skyriklį) ir mes gauname tai, ko norėjome:
Grožis!
2 pavyzdys: ištraukite el. pašto adresus iš teksto
Tarkime, kad turime šią lentelę kaip pradinius duomenis:
… iš kur turime ištraukti ten rastus el. pašto adresus (aiškumo dėlei tekste juos paryškinau raudonai).
Kaip ir ankstesniame pavyzdyje, lentelę įkeliame į „Power Query“ standartiniu būdu per Duomenys – iš lentelės/diapazono (Duomenys – iš Tgalintis/Rangelas).
Tada pridedame apskaičiuotą stulpelį su funkcija via Pridėti stulpelį – iškviesti pasirinktinę funkciją (Pridėti stulpelį – iškviesti pasirinktinę funkciją) ir įveskite savo argumentus:
El. pašto adresų analizė yra sunkesnė užduotis, todėl jai išspręsti reikia daugybės įvairaus laipsnio reguliariųjų išraiškų. Naudojau vieną iš paprastų variantų – ne idealų, bet daugeliu atvejų gana veikiantį:
[w|.|-]*@w*.[w|.]*
Kaip skyriklis (atriboti) galite įvesti kabliataškį ir tarpą.
Paspauskite ant OK ir gauname stulpelį su elektroninio pašto adresais, ištrauktais iš originalaus teksto „košė“:
Magija!
PS
Kaip sakoma: „Nėra tokio gėrio, kurio nebūtų galima padaryti dar geresniu“. „Power Query“ yra šauni ir pati savaime, o kartu su reguliariosiomis išraiškomis ji suteikia mums visiškai nerealios galios ir lankstumo apdorojant bet kokius tekstinius duomenis. Tikiuosi, kad „Microsoft“ kada nors pridės RegExp palaikymą „Power Query“ ir „Power BI“ atnaujinimuose, o visi aukščiau išvardyti šokiai su tamburinu taps praeitimi. Na, kol kas taip.
Taip pat noriu pridurti, kad patogu žaisti su reguliariais posakiais svetainėje https://regexr.com/ – tiesiog internetiniame redaktoriuje. Ten skyriuje Bendruomenės modeliai Yra daugybė paruoštų reguliarių sezonų visoms progoms. Eksperimentuokite – visa įprastų išraiškų galia dabar yra jūsų paslaugoms Power Query!
- Kas yra reguliariosios išraiškos (RegExp) ir kaip jas naudoti „Excel“.
- Neaiškia teksto paieška Power Query
- Lentelių surinkimas iš skirtingų failų naudojant Power Query