Fiteny mahazatra (RegExp) amin'ny Power Query

Raha toa ianao ka zatra kely amin'ny fomba fiteny mahazatra, dia tsy mila manao dokam-barotra ianao. Raha toa ka tsy tena ao anatin'ilay lohahevitra ianao, dia ny fiteny mahazatra (Expression Regular = RegExp = "regexps" = "regulars") dia fiteny iray izay, amin'ny fampiasana tarehin-tsoratra sy fitsipika manokana, ny substrings ilaina dia karohina ao amin'ny lahatsoratra, alaina izy ireo. na soloina lahatsoratra hafa . Fitaovana tena matanjaka sy tsara tarehy ity, filaharana ambony noho ny fomba fiasa hafa rehetra amin'ny lahatsoratra.

Efa nofaritako tamin'ny an-tsipiriany sy miaraka amin'ny andiana ohatra avy amin'ny fiainana ny fomba ahafahanao manampy fanohanana fanehoan-kevitra tsy tapaka amin'ny Excel amin'ny fampiasana macro tsotra - raha mbola tsy namaky ity lahatsoratra ity ianao, dia manoro hevitra anao aho hamaky azy io alohan'ny hanohizana. Hahita zava-baovao maro ianao, miantoka aho 🙂

Na izany aza, mbola misokatra ny fanontaniana - ahoana ny fomba hanampiana ny fahafahana mampiasa fomba fiteny mahazatra ao amin'ny Power Query? Ny Power Query, mazava ho azy, dia tsara ho azy ary afaka manao zavatra betsaka amin'ny lahatsoratra (manapaka, mametaka, manadio, sns.), fa raha afaka mamakivaky azy amin'ny herin'ny fanehoan-kevitra mahazatra ianao dia mety ho baomba fotsiny.

Indrisy anefa fa tsy misy fiasa naorina amin'ny fiaraha-miasa amin'ny RegExps ao amin'ny Power Query, ary ny fanampiana ofisialy Microsoft sy ny fanohanana ara-teknika dia mamaly an'io fanontaniana io amin'ny lafiny ratsy. Na izany aza, misy fomba manodidina an'io fetra io 🙂

Ny votoatin'ny fomba

Ny hevi-dehibe dia tsotra amin'ny fahafaham-baraka.

Ao amin'ny lisitry ny fahaiza-manao Power Query naorina dia misy fiasa iray Pejin-tranonkala. Ny famaritana an'io fiasa io ao amin'ny tranokalan'ny fanampiana ofisialy Microsoft dia tena fohy:

Raha adika, dia izao: “Mamerina ny ao anatin'ny antontan-taratasy HTML nozaraina ho ao anatin'ny rafitra misy azy, ary koa fanehoana ny antontan-taratasy feno sy ny vatany rehefa nesorina ireo marika.” Famaritana, mazava ho azy.

Matetika io fiasa io dia ampiasaina rehefa manafatra data avy amin'ny tranonkala ary soloina ho azy, ohatra, rehefa misafidy eo amin'ny tabilao isika. Data Command Avy amin'ny Internet (Data - Avy amin'ny tranonkala). Omenay pejy web ho toy ny tohan-kevitra ny asa, ary averinay aminay amin'ny endrika latabatra ny votoatiny, rehefa nodiovina teo aloha ny marika rehetra.

Ny TSY lazain'ilay fanampiana dia ankoatra ny fiteny marika HTML function Pejin-tranonkala manohana script JavaScript, izay hita eny rehetra eny amin'ny tranokala amin'ny Internet. Ary ny JavaScript, indray, dia afaka niasa tamin'ny fomba fiteny mahazatra ary manana fiasa namboarina ho an'ny RegExps! Noho izany mba hampiharana fomba fiteny mahazatra ao amin'ny Power Query, dia mila mamahana ny Web.Page isika ho toy ny tohan-kevitra amin'ny programa JavaScript kely izay hanao ny asa rehetra ho an'ny Power Query.

Inona ny endriny amin'ny JavaScript madio

Betsaka ny fampianarana amin'ny antsipiriany momba ny fiasana amin'ny fiteny mahazatra amin'ny JavaScript amin'ny Internet (ohatra, iray, roa).

Raha fintinina sy notsorina dia ho toy izao ny code JavaScript:

Eto:

• var str = 'Mandoa faktiora 123 sy 789 ho an'ny saosisy'; – mamorona variable p ary omeo azy ny lahatsoratra loharano hodinihina.
• var pattern = /d+/gi; - Mamorona fomba fiteny mahazatra ary apetraho ao anaty faribolana lamina.

  Ny fitenenana dia manomboka amin'ny slash (/).

  Ny fitenenana mihitsy eto, ohatra, dia d+ mijoro ho an'ny filaharan'ny isa rehetra.

  Amin'ny alàlan'ny ampahany aorian'ny fitenenana dia misy masontsivana fikarohana fanampiny (mpanova) - azo faritana amin'ny filaharana rehetra:
  

  • g – midika hoe fikarohana eran-tany, izany hoe rehefa avy nahita lalao dia tsy tokony hijanona, fa tohizo ny fikarohana hatramin'ny faran'ny lahatsoratra. Raha tsy voapetraka io fanovana io, dia ny lalao voalohany ihany no hamerenan'ny scripty (123)
  • i – mikaroka tsy miraharaha ny litera
  • m - fikarohana andalana maro (ampiasaina rehefa mizara ho andalana maromaro ny lahatsoratra loharano)
• var result = str.match(pattern).join(';'); - manao fikarohana ao amin'ny lahatsoratra loharano (p) amin'ny fomba fiteny mahazatra nomena (lamina) ary apetraho ao anaty faribolana ny valiny vokatra, mampifandray azy ireo amin'ny semicolon mampiasa ny baiko anjara
• document.write(valiny); - asehoy ny votoatin'ny fari-pamokarana vokatra

Mariho ihany koa fa ny tady lahatsoratra (ankoatra ny fomba fiteny mahazatra) amin'ny JavaScript dia voarakitra ao anaty apostrophes, fa tsy teny nalaina tahaka ny ao amin'ny Power Query na VBA.

Amin'ny vokatra, ity script ity dia hanome antsika ny isa rehetra hita ao amin'ny lahatsoratra loharano:

123, 789

Tapitra ny fampianarana fohy JavaScript, misaotra anareo rehetra. Manantena anao hahazo ny lojika 🙂

Mbola hafindra amin'ny Power Query ity fananganana ity.

Fikarohana sy fakana lahatsoratra amin'ny alàlan'ny fitenenana mahazatra amin'ny Power Query

Manao izao manaraka izao izahay:

1. Sokafy Excel ary mamorona Power Query vaovao tsy misy na inona na inona ao amin'ny tabilao Data - Makà data / Mamorona fangatahana - Avy amin'ny loharano hafa - Fangatahana foana (Angona - Makà data / Fanontaniana vaovao - Avy amin'ny loharano hafa - Fanontaniana banga). Raha manana dikan-teny taloha an'ny Excel 2010-2013 sy Power Query ianao tsy manana naorina, fa napetraka ho fanampiny fanampiny, dia ho eo amin'ny tabilao izany rehetra izany. Fanontaniana momba ny herinaratraAry tsia Data.

2. Ao amin'ny varavarankelin'ny fanontana fanontaniana misokatra, eo amin'ny tontonana havanana, ampidiro avy hatrany ny anaran'ny asa ho avy (ohatra, fxRegExpExtract)

3. Andeha ho any amin'ny tabilao View – Tonian-dahatsoratra mandroso (Jereo - Tonian-dahatsoratra mandroso), fafantsika ny kaody M manontolo amin'ny fangatahana poakaty ary apetaho eo ny kaody superfunction:

Tandremo ny tananao:

Ao amin'ny andalana voalohany dia milaza isika fa ny asantsika dia hanana tohan-kevitra telo: txt - ny lahatsoratra tany am-boalohany hodinihina, My Notes - fomba fiteny mahazatra, delim - toetran'ny delimiter hanehoana valiny.

Manaraka izany dia antsoina hoe ny asa Pejin-tranonkala, mamorona ny code JavaScript voalaza etsy ambony ao amin'ny tohan-kevitra. Mametaka sy manolo ny hevitray miovaova amin'ny kaody izahay.

Sombiny:

[Angona]{0}[ankizy]{0}[ankizy]{1}[soratra]{0}

… dia ilaina mba “hilatsaka” eo amin'ny latabatra miaraka amin'ny vokatra ilaintsika. Ny tanjona dia ny hoe ny asa Pejin-tranonkala vokatr'izany dia mamokatra tabilao misy akany maromaro mamerina ny firafitry ny pejin-tranonkala. Raha tsy misy ity ampahany amin'ny M-code ity, ny asa ataontsika dia mamoaka izao:

… ary tsy maintsy manindry imbetsaka ilay teny table, "milatsaka" misesisesy ao anaty tabilao misy akany amin'ny tsanganana Ankizy:

Raha tokony ho ireo teny nindramina rehetra ireo, dia asehontsika avy hatrany ao amin'ny fehezan-dalàna momba ny asantsika izay mametraka latabatra sy tsanganana (Text) mila izahay.

Eto, raha ny marina, ny zava-miafina rehetra. Dia mijanona ny manindry ny bokotra farany eo am-baravarankely tonian-dahatsoratra mandroso, izay nampidiranay ny kaodinay, ary afaka miroso amin'ny matsiro indrindra ianao – andramo ny asa ataonay any am-piasana.

Ireto misy ohatra roa momba ny voa.

Ohatra 1. Ny fakana ny laharan'ny kaonty sy ny daty avy amin'ny famaritana ny fandoavam-bola

Manana fanambaràna amin'ny banky misy famaritana (tanjona) momba ny fandoavam-bola izahay, izay ilainao hanesorana ny isa sy ny datin'ny faktiora voaloa ho tsanganana misaraka:

Mampiditra ny latabatra ao amin'ny Power Query amin'ny fomba mahazatra izahay Data – Avy amin'ny tabilao/Salan'isa (Data - Avy amin'i Tafaka/Ranjely).

Avy eo dia manampy tsanganana kajy miaraka amin'ny fiasantsika amin'ny alalan'ny Ampio tsanganana - Antsoy ny fiasa mahazatra (Ampio tsanganana — Antsoy ny fiasa manokana) ary ampidiro ny arguments:

Amin'ny teny mahazatra (argument My Notes) modely ampiasainay:

(d{3,5}|d{2}.d{2}.d{4})

… nadika amin'ny fitenin'olombelona midika hoe: 

isa manomboka amin'ny 3 ka hatramin'ny 5 isa (kaonty nomerao)

or

sombiny amin'ny endrika "2-bit number - point - 2-bit number - point - 4-bit number", izany hoe daty amin'ny endrika DD.MM.YYYY.

Amin'ny maha-karazana delimiter (argument delim) ampidiro semicolon.

Taorian'ny fikitihana OK ny asan-tsika majika dia mamakafaka ny angona voalohany rehetra araka ny fomba fiteny mahazatra ary mamorona tsanganana ho antsika miaraka amin'ireo isa sy datin'ny faktiora hita:

Dia mijanona ny manasaraka azy amin'ny semicolon mampiasa ny baiko Home — Tsanganana Mizara — By Delimiter (Horonan-trano — Tsanganana mizara — Amin’ny alalan’ny delimiter) ary azonay izay tadiavinay:

Beauty!

Ohatra 2: Esory ny adiresy mailaka amin'ny lahatsoratra

Eritrereto hoe manana ity tabilao manaraka ity isika ho angona voalohany:

… avy any amin'izay ilaintsika hanesorana ireo adiresy imailaka hita ao (mba hazavaina dia nasongadiko tamin'ny loko mena izy ireo tao amin'ny lahatsoratra).

Toy ny tamin'ny ohatra teo aloha, dia mampiditra ny latabatra ao amin'ny Power Query amin'ny fomba mahazatra amin'ny alàlan'ny Data – Avy amin'ny tabilao/Salan'isa (Data - Avy amin'i Tafaka/Ranjely).

Avy eo dia manampy tsanganana kajy miaraka amin'ny fiasantsika amin'ny alalan'ny Ampio tsanganana - Antsoy ny fiasa mahazatra (Ampio tsanganana — Antsoy ny fiasa manokana) ary ampidiro ny arguments:

Ny famakafakana adiresy mailaka dia asa sarotra kokoa ary misy andiana fanehoan-kevitra tsy tapaka amin'ny ambaratonga samihafa amin'ny nofy ratsy hamahana azy. Nampiasa ny iray amin'ireo safidy tsotra aho - tsy mety, fa miasa amin'ny ankamaroan'ny tranga:

[w|.|-]*@w*.[w|.]*

Toy ny separator (delim) azonao atao ny mampiditra semicolon sy habaka.

tsindrio eo amin'ny OK ary mahazo tsanganana misy adiresy imailaka nalaina avy amin'ny lahatsoratra tany am-boalohany hoe "porridge":

Magic!

PS

Hoy ny fitenenana hoe: "Tsy misy zavatra tsara toy izany ka tsy azo hahatsara kokoa." Ny Power Query dia mangatsiatsiaka ho azy, ary rehefa atambatra amin'ny fomba fiteny mahazatra, dia manome hery sy fahafaha-manao tsy mitombina tanteraka amin'ny fanodinana ny angona lahatsoratra. Manantena aho fa indray andro any Microsoft dia hanampy ny RegExp fanohanana amin'ny Power Query sy Power BI fanavaozana ary ireo dihy etsy ambony miaraka amin'ny ampongatapaka dia ho lasa zavatra taloha. Eny, amin'izao fotoana izao, eny.

Te-hanampy ihany koa aho fa mety ny milalao amin'ny fanehoan-kevitra mahazatra ao amin'ny tranokala https://regexr.com/ - ao amin'ny tonian-dahatsoratra an-tserasera. Ao amin'ny fizarana Fomba fiaraha-monina Betsaka ny vanim-potoana tsy tapaka efa vonona ho an'ny fotoana rehetra. Andrana – efa eo am-pelatananao amin'ny Power Query izao ny herin'ny fanehoan-kevitra mahazatra!

• Inona no atao hoe fiteny mahazatra (RegExp) sy ny fomba fampiasana azy ireo amin'ny Excel
• Fikarohana lahatsoratra manjavozavo ao amin'ny Power Query
• Manangona latabatra avy amin'ny rakitra samihafa mampiasa Power Query