Sisällys
Jos olet ainakin hieman perehtynyt säännöllisiin lausekkeisiin, sinun ei tarvitse mainostaa niitä. Jos et ole aivan aiheessa, niin säännölliset lausekkeet (säännölliset lausekkeet = RegExp = "säännölliset lausekkeet" = "säännölliset") on kieli, jossa erikoismerkkejä ja sääntöjä käyttäen etsitään tekstistä tarvittavat osamerkkijonot, jotka puretaan tai korvata toisella tekstillä. Tämä on erittäin tehokas ja kaunis työkalu, joka on suuruusluokkaa parempi kuin kaikki muut tekstinkäsittelytavat.
Olen jo kuvaillut yksityiskohtaisesti ja joukolla esimerkkejä elämästä, kuinka voit lisätä säännöllisten lausekkeiden tuen Exceliin yksinkertaisilla makroilla – jos et ole lukenut tätä artikkelia, suosittelen, että luet sen ennen kuin jatkat. Löydät paljon uutta, takaan 🙂
Kysymys jää kuitenkin avoimeksi – kuinka lisätä mahdollisuus käyttää säännöllisiä lausekkeita Power Queryssä? Power Query on tietysti hyvä sellaisenaan ja voi tehdä paljon tekstin kanssa (leikkaus, liimaus, puhdistus jne.), mutta jos sen voisi ylittää säännöllisten lausekkeiden voimalla, se olisi vain pommi.
Valitettavasti Power Queryssä ei ole sisäänrakennettuja toimintoja RegExps-työskentelyyn, ja virallinen Microsoftin tuki ja tekninen tuki vastaavat tähän kysymykseen kieltävästi. Tämän rajoituksen voi kuitenkin kiertää 🙂
Menetelmän ydin
Pääidea on helppo häpeättävä.
Sisäänrakennettujen Power Query -ominaisuuksien luettelossa on toiminto Nettisivu. Tämän toiminnon kuvaus virallisella Microsoftin ohjesivustolla on erittäin ytimekäs:
Käännettynä tämä olisi: "Palauttaa HTML-dokumentin sisällön eriteltynä sen komponenttirakenteisiin sekä esityksen koko asiakirjasta ja sen rungosta sen jälkeen, kun tunnisteet on poistettu." Niin ja niin kuvaus, suoraan sanottuna.
Yleensä tätä toimintoa käytetään tuottaessa tietoja verkosta ja se korvataan automaattisesti, esimerkiksi kun valitsemme välilehdeltä Päiväys Komento Internetistä (Data – verkosta). Annamme funktiolle web-sivun argumenttina, ja se palauttaa meille sisältönsä taulukoiden muodossa tyhjennettyään aiemmin kaikki tagit.
Ohje EI sano, että HTML-kuvauskielen lisäksi toiminto Nettisivu tukee JavaScript-skriptejä, joka on nyt kaikkialla Internetin verkkosivustoilla. Ja JavaScript puolestaan on aina kyennyt toimimaan säännöllisten lausekkeiden kanssa, ja siinä on sisäänrakennetut toiminnot RegExpsille! Säännöllisten lausekkeiden toteuttamiseksi Power Queryssä meidän on syötettävä Web.Page-sivu argumenttina pienelle JavaScript-ohjelmalle, joka tekee kaiken Power Queryn työn.
Miltä se näyttää puhtaalla JavaScriptillä
Internetissä on paljon yksityiskohtaisia opetusohjelmia JavaScriptin säännöllisten lausekkeiden käyttämisestä (esimerkiksi yksi, kaksi).
Lyhyesti ja yksinkertaistettuna JavaScript-koodi näyttää tältä:
Tässä:
- var str = 'Maksa laskut 123 ja 789 makkarasta'; – Luo muuttuja str ja anna sille analysoitava lähdeteksti.
- var kuvio = /d+/gi; – Luo säännöllinen lauseke ja laita se muuttujaan kuvio.
Lauseke alkaa kauttaviivalla (/).
Itse ilmaisu tässä esimerkiksi on d+ tarkoittaa mitä tahansa numerosarjaa.
Lausekkeen jälkeisen murto-osan kautta on lisähakuparametreja (muuntajia) – ne voidaan määrittää missä tahansa järjestyksessä:
- g – tarkoittaa globaalia hakua, eli haun löytymisen jälkeen ei pidä lopettaa, vaan jatkaa hakua tekstin loppuun asti. Jos tätä muokkausta ei ole asetettu, skriptimme palauttaa vain ensimmäisen osuman (123)
- i – haku kirjainten kirjainkokoa ottamatta
- m – monirivinen haku (käytetään, kun lähdeteksti on jaettu usealle riville)
- var tulos = str.match(pattern).join(';'); – suorita haku lähdetekstistä (str) annetulla säännöllisellä lausekkeella (kuvio) ja aseta tulokset muuttujaan johtua, ketjuttamalla ne puolipisteellä komennolla yhdistää
- document.write(tulos); – näyttää tulosmuuttujan sisällön
Huomaa myös, että JavaScriptin tekstimerkkijonot (säännöllisiä lausekkeita lukuun ottamatta) on heitetty heittomerkkeihin, ei lainausmerkkeihin, kuten ne ovat Power Queryssä tai VBA:ssa.
Tulosteessa tämä komentosarja antaa meille kaikki lähdetekstissä olevat numerot:
123, 789
JavaScript-lyhytkurssi on ohi, kiitos kaikille. Toivottavasti ymmärrät logiikan 🙂
On vielä siirrettävä tämä rakenne Power Queryyn.
Etsi ja poimi tekstifunktio säännöllisillä lausekkeilla Power Queryssa
Teemme seuraavaa:
1. Avaa Excel ja luo välilehteen uusi tyhjä Power Query Tiedot – Hae tiedot / Luo pyyntö – Muista lähteistä – Tyhjä pyyntö (Tiedot — Hae tiedot / Uusi kysely — Muista lähteistä — Tyhjä kysely). Jos sinulla on vanha versio Excel 2010-2013:sta ja Power Querystä, jota sinulla ei ole sisäänrakennettua, mutta se asennettiin erillisenä apuohjelmana, kaikki tämä on välilehdellä TehokyselyJa ei Päiväys.
2. Kirjoita avautuvan kyselyeditorin tyhjään ikkunaan oikeaan paneeliin heti tulevan funktiomme nimi (esim. fxRegExpExtract)
3. Siirrytään välilehteen Näytä – Edistynyt editori (Näytä — Advanced Editor), pyyhimme tyhjän pyynnön koko M-koodin ja liitämme sinne superfunktiomme koodin:
Varo käsiäsi:
Ensimmäisellä rivillä sanomme, että funktiollamme on kolme tekstiargumenttia: txt – analysoitava alkuperäinen teksti, regex - säännöllinen lausekekuvio, erotin — erotinmerkki tulosten näyttämistä varten.
Seuraavaksi kutsumme funktiota Nettisivu, muodostaen yllä argumentissa kuvatun JavaScript-koodin. Liitämme ja korvaamme muuttujaargumenttimme koodiin.
Kappale:
[Data]{0}[Lapset]{0}[Lapset]{1}[Teksti]{0}
… tarvitaan, jotta voimme "pudota" taulukkoon tarvitsemillamme tuloksilla. Pointti on, että toiminto Nettisivu seurauksena se tuottaa useita sisäkkäisiä taulukoita, jotka toistavat verkkosivun rakenteen. Ilman tätä M-koodin osaa funktiomme antaisi tämän:
… ja meidän olisi napsautettava sanaa useita kertoja Pöytä, putoaa peräkkäin alatason sisäkkäisiin taulukoihin sarakkeissa Lapset:
Kaiken tämän lainauksen sijaan ilmoitamme heti funktiomme koodissa, mikä sisäkkäinen taulukko ja sarake (teksti) me tarvitsemme.
Tässä ovat itse asiassa kaikki salaisuudet. Jäljelle jää napin painaminen Suorittaa loppuun ikkunassa edistynyt editori, johon lisäsimme koodimme, ja voit siirtyä herkullisimpiin – kokeile toimintoamme töissä.
Tässä on pari siemenesimerkkiä.
Esimerkki 1. Tilinumeron ja päivämäärän hakeminen maksun kuvauksesta
Meillä on tiliote, jossa on maksujen kuvaus (tarkoitus), josta sinun tulee vetää maksettujen laskujen numerot ja päivämäärät erillisiin sarakkeisiin:
Lataamme taulukon Power Queryyn tavalliseen tapaan Tiedot – taulukosta/alueesta (Tiedot – Tkykenevä/Renkeli).
Sitten lisäämme lasketun sarakkeen funktiollamme via Lisää sarake – kutsu mukautettua toimintoa (Lisää sarake – kutsu mukautettua toimintoa) ja syötä sen argumentit:
Säännöllisenä lausekkeena (argumentti regex) käyttämämme malli:
(d{3,5}|d{2}.d{2}.d{4})
… käännettynä ihmiskielelle, mikä tarkoittaa:
numerot 3-5 numeroa (tilinumerot)
or
katkelmia muodossa "2-bittinen luku - piste - 2-bittinen luku - piste - 4-bittinen luku", eli päivämäärät muodossa PP.KK.VVVV.
Erotinmerkkinä (argumentti erotin) syötä puolipiste.
Napsauttamisen jälkeen OK taikafunktiomme analysoi kaikki lähtötiedot säännöllisen lausekkeen mukaan ja muodostaa meille sarakkeen, jossa on löydetty laskujen numerot ja päivämäärät:
On vielä erotettava se puolipisteellä komennolla Etusivu — Jaettu sarake — Erottimen mukaan (Etusivu – Jaettu sarake – Erottimen mukaan) ja saamme mitä halusimme:
Kauneus!
Esimerkki 2: Poimi sähköpostiosoitteet tekstistä
Oletetaan, että meillä on seuraava taulukko lähtötietona:
… mistä meidän on poimittava sieltä löytyneet sähköpostiosoitteet (selvyyden vuoksi korostin ne tekstissä punaisella).
Kuten edellisessä esimerkissä, lataamme taulukon Power Queryyn tavallisella tavalla kautta Tiedot – taulukosta/alueesta (Tiedot – Tkykenevä/Renkeli).
Sitten lisäämme lasketun sarakkeen funktiollamme via Lisää sarake – kutsu mukautettua toimintoa (Lisää sarake – kutsu mukautettua toimintoa) ja syötä sen argumentit:
Sähköpostiosoitteiden jäsentäminen on vaikeampi tehtävä, ja sen ratkaisemiseksi on joukko eriasteisia säännöllisiä lausekkeita. Käytin yhtä yksinkertaisista vaihtoehdoista – ei ihanteellinen, mutta melko toimiva useimmissa tapauksissa:
[w|.|-]*@w*.[w|.]*
Erottimena (erotin) voit kirjoittaa puolipisteen ja välilyönnin.
Valitse OK ja saamme sarakkeen sähköpostiosoitteilla, jotka on poimittu alkuperäisestä tekstistä "puuro":
Taika!
PS
Kuten sanonta kuuluu: "Ei ole niin hyvää asiaa, jota ei voisi tehdä vielä paremmaksi." Power Query on siistiä yksinään, ja yhdistettynä säännöllisiin lausekkeisiin se antaa meille täysin epärealistista tehoa ja joustavuutta minkä tahansa tekstidatan käsittelyssä. Toivon, että Microsoft lisää joskus RegExp-tuen Power Query- ja Power BI -päivityksiin, ja kaikista yllä mainituista tamburiinilla tanssimisesta tulee menneisyyttä. No, toistaiseksi kyllä.
Haluan myös lisätä, että on kätevää pelata säännöllisillä lausekkeilla sivustolla https://regexr.com/ – suoraan online-editorissa. Siellä osastolla Yhteisön mallit Valmiita vakiokausia on valtava määrä kaikkiin tilanteisiin. Kokeile – kaikki säännöllisten lausekkeiden teho on nyt palveluksessasi Power Queryssä!
- Mitä ovat säännölliset lausekkeet (RegExp) ja kuinka niitä käytetään Excelissä
- Sumea tekstihaku Power Queryssä
- Taulukoiden kokoaminen eri tiedostoista Power Queryllä