Innhold
Hvis du i det minste er litt kjent med regulære uttrykk, trenger du ikke å annonsere dem. Hvis du ikke er helt inne på emnet, så er regulære uttrykk (Regular Expressions = RegExp = “regexps” = “regulars”) et språk der det ved hjelp av spesialtegn og regler søkes etter de nødvendige understrengene i teksten, de trekkes ut. eller erstattet med annen tekst. Dette er et veldig kraftig og vakkert verktøy, en størrelsesorden overlegen alle andre måter å jobbe med tekst på.
Jeg har allerede beskrevet i detalj og med en haug med eksempler fra livet hvordan du kan legge til regulære uttrykksstøtte til Excel ved å bruke enkle makroer – hvis du ikke har lest denne artikkelen, anbefaler jeg at du leser den før du fortsetter. Du vil oppdage mye nytt, jeg garanterer 🙂
Spørsmålet forblir imidlertid åpent - hvordan legge til muligheten til å bruke regulære uttrykk i Power Query? Power Query er selvfølgelig bra alene og kan gjøre mye med tekst (klippe, lime, rense osv.), men hvis du kunne krysse det med kraften til regulære uttrykk, ville det bare vært en bombe.
Dessverre er det ingen innebygde funksjoner for å jobbe med RegExps i Power Query, og offisiell Microsoft-hjelp og teknisk støtte svarer negativt på dette spørsmålet. Det er imidlertid en vei rundt denne begrensningen 🙂
Essensen av metoden
Hovedideen er enkel å vanære.
I listen over innebygde Power Query-funksjoner er det en funksjon Nettside. Beskrivelsen av denne funksjonen på Microsofts offisielle hjelpeside er ekstremt kortfattet:
Oversatt vil dette være: "Returnerer innholdet i HTML-dokumentet brutt ned i dets komponentstrukturer, samt en representasjon av hele dokumentet og dets hovedtekst etter at kodene er fjernet." Ujevne beskrivelse, ærlig talt.
Vanligvis brukes denne funksjonen ved import av data fra nettet og erstattes automatisk, for eksempel når vi velger på fanen Data Kommando Fra Internett (Data – fra nettet). Vi gir funksjonen en nettside som argument, og den returnerer innholdet til oss i form av tabeller, etter å ha fjernet alle kodene tidligere.
Hva hjelpen IKKE sier er det i tillegg til HTML-markeringsspråket funksjon Nettside støtter JavaScript-skript, som nå er allestedsnærværende på nettsteder på Internett. Og JavaScript har på sin side alltid kunnet jobbe med regulære uttrykk og har innebygde funksjoner for RegExps! Så for å implementere regulære uttrykk i Power Query, må vi mate Web.Page som et argument til et lite JavaScript-program som vil gjøre alt arbeidet for Power Query.
Hvordan det ser ut i ren JavaScript
Det er mange detaljerte veiledninger om å jobbe med regulære uttrykk i JavaScript på Internett (for eksempel en, to).
Kort og forenklet vil JavaScript-koden se slik ut:
Her:
- var str = 'Betal regningene 123 og 789 for pølse'; – lage en variabel str og tilordne den kildeteksten som vi skal analysere.
- var mønster = /d+/gi; – lag et regulært uttrykk og sett det i en variabel mønster.
Uttrykket starter med en skråstrek (/).
Selve uttrykket her er for eksempel d+ står for en hvilken som helst sekvens av sifre.
Gjennom brøken etter uttrykket er det flere søkeparametere (modifikatorer) – de kan spesifiseres i hvilken som helst rekkefølge:
- g – betyr globalt søk, dvs. etter å ha funnet en match, skal du ikke stoppe, men fortsette søket til slutten av teksten. Hvis denne modifikatoren ikke er angitt, vil skriptet vårt bare returnere det første treffet (123)
- i – søk uten hensyn til store og små bokstaver
- m – flerlinjesøk (brukes når kildeteksten er delt inn i flere linjer)
- var resultat = str.match(pattern).join(';'); – utfør et søk i kildeteksten (str) av det gitte regulære uttrykket (mønster) og sett resultatene i en variabel resultere, koble dem sammen med et semikolon ved å bruke kommandoen bli medlem
- document.write(result); – vise innholdet i resultatvariabelen
Vær også oppmerksom på at tekststrenger (unntatt regulære uttrykk) i JavaScript er omsluttet av apostrof, ikke anførselstegn slik de er i Power Query eller VBA.
Ved utgangen vil dette skriptet gi oss som et resultat alle tallene som finnes i kildeteksten:
123, 789
JavaScript-kortkurset er over, takk alle sammen. Håper du skjønner logikken 🙂
Det gjenstår å overføre denne konstruksjonen til Power Query.
Søk og trekk ut tekstfunksjonen etter regulære uttrykk i Power Query
Vi gjør følgende:
1. Åpne Excel og opprett en ny tom Power Query i fanen Data – Hent data / Opprett forespørsel – Fra andre kilder – Tom forespørsel (Data — Hent data / Nytt søk — Fra andre kilder — Tomt søk). Hvis du har en gammel versjon av Excel 2010-2013 og Power Query du ikke har innebygd, men ble installert som et eget tillegg, så vil alt dette være på fanen StrømforespørselOg ikke Data.
2. I det tomme vinduet til spørringsredigeringsprogrammet som åpnes, i høyre panel, skriv umiddelbart inn navnet på vår fremtidige funksjon (for eksempel, fxRegExpExtract)
3. La oss gå til fanen Vis – Advanced Editor (Vis — Avansert redaktør), vi sletter hele M-koden for den tomme forespørselen og limer inn koden til superfunksjonen vår der:
Pass på hendene dine:
I den første linjen sier vi at funksjonen vår vil ha tre tekstargumenter: txt – den opprinnelige teksten som analyseres, regex - regelmessig uttrykksmønster, avgrense — skilletegn for å vise resultater.
Deretter kaller vi funksjonen Nettside, og danner JavaScript-koden beskrevet ovenfor i argumentet. Vi limer inn og erstatter variabelargumentene våre i koden.
Fragment:
[Data]{0}[Barn]{0}[Barn]{1}[Tekst]{0}
… er nødvendig for å "falle gjennom" i tabellen med de resultatene vi trenger. Poenget er at funksjonen Nettside som et resultat produserer den flere nestede tabeller som gjentar strukturen til en nettside. Uten denne delen av M-koden ville funksjonen vår gi ut dette:
… og vi måtte klikke på ordet flere ganger Bord, suksessivt "faller gjennom" inn i underordnede nestede tabeller i kolonner Barn:
I stedet for alt dette sitatet, indikerer vi umiddelbart i koden til funksjonen vår hvilken nestet tabell og kolonne (tekst) vi trenger.
Her er faktisk alle hemmelighetene. Det gjenstår å trykke på knappen Finish i vinduet avansert redaktør, hvor vi satte inn koden vår, og du kan fortsette til det deiligste – prøv funksjonen vår på jobben.
Her er et par frøeksempler.
Eksempel 1. Henting av kontonummer og dato fra betalingsbeskrivelsen
Vi har en kontoutskrift med beskrivelse (formål) med betalinger, der du må trekke ut tall og datoer for betalte fakturaer i separate kolonner:
Vi laster tabellen inn i Power Query på standard måte Data – Fra tabell/område (Data - Fra Tstand/Rengel).
Deretter legger vi til en beregnet kolonne med vår funksjon via Legg til kolonne – Ring egendefinert funksjon (Legg til kolonne – Aktiver egendefinert funksjon) og skriv inn argumentene:
Som et regulært uttrykk (argument regex) mal vi bruker:
(d{3,5}|d{2}.d{2}.d{4})
… oversatt til menneskelig språk betyr:
tall fra 3 til 5 sifre (kontonumre)
or
fragmenter av formen "2-bit tall - punkt - 2-bit tall - punkt - 4-bit tall", det vil si datoer på formen DD.MM.ÅÅÅÅ.
Som et skilletegn (argument avgrense) skriv inn et semikolon.
Etter å ha klikket på OK den magiske funksjonen vår analyserer alle de innledende dataene i henhold til vårt regulære uttrykk og danner en kolonne for oss med de funnet tallene og datoene for fakturaene:
Det gjenstår å skille det med semikolon ved å bruke kommandoen Hjem — Delt kolonne — Etter skilletegn (Hjem — Del kolonne — Etter skilletegn) og vi får det vi ønsket:
Skjønnhet!
Eksempel 2: Trekk ut e-postadresser fra tekst
Anta at vi har følgende tabell som startdata:
… hvorfra vi må hente ut e-postadressene som ble funnet der (for klarhets skyld har jeg markert dem med rødt i teksten).
Som i forrige eksempel laster vi tabellen inn i Power Query på standard måte via Data – Fra tabell/område (Data - Fra Tstand/Rengel).
Deretter legger vi til en beregnet kolonne med vår funksjon via Legg til kolonne – Ring egendefinert funksjon (Legg til kolonne – Aktiver egendefinert funksjon) og skriv inn argumentene:
Å analysere e-postadresser er en vanskeligere oppgave, og det er en haug med regulære uttrykk av ulik grad av mareritt for å løse det. Jeg brukte et av de enkle alternativene - ikke ideelt, men fungerer ganske i de fleste tilfeller:
[w|.|-]*@w*.[w|.]*
Som skilletegn (avgrense) kan du skrive inn et semikolon og et mellomrom.
Klikk på OK og vi får en spalte med e-postadresser hentet fra originalteksten "grøt":
Magi!
PS
Som ordtaket sier: "Det er ingen god ting som ikke kunne blitt enda bedre." Power Query er kult i seg selv, og når det kombineres med regulære uttrykk, gir det oss helt urealistisk kraft og fleksibilitet i behandlingen av tekstdata. Jeg håper Microsoft en dag vil legge til RegExp-støtte i Power Query og Power BI-oppdateringer, og at alle de ovennevnte dansene med en tamburin vil bli en saga blott. Vel, foreløpig, ja.
Jeg vil også legge til at det er praktisk å leke med regulære uttrykk på siden https://regexr.com/ – rett i nettredaktøren. Der i seksjonen Fellesskapsmønstre Det finnes et stort antall ferdige vanlige sesonger for alle anledninger. Eksperimenter – all kraften til regulære uttrykk står nå til tjeneste i Power Query!
- Hva er regulære uttrykk (RegExp) og hvordan du bruker dem i Excel
- Uklar tekstsøk i Power Query
- Sette sammen tabeller fra forskjellige filer ved hjelp av Power Query