Indhold
Hvis du i det mindste er lidt bekendt med regulære udtryk, så behøver du ikke reklamere for dem. Hvis du ikke er helt inde i emnet, så er regulære udtryk (Regular Expressions = RegExp = “regexps” = “regulars”) et sprog, hvor der ved hjælp af specialtegn og regler søges efter de nødvendige understrenge i teksten, de udtrækkes eller erstattet med anden tekst. Dette er et meget kraftfuldt og smukt værktøj, en størrelsesorden overlegen i forhold til alle andre måder at arbejde med tekst på.
Jeg har allerede beskrevet detaljeret og med en masse eksempler fra livet, hvordan du kan tilføje regulært udtryksstøtte til Excel ved hjælp af simple makroer – hvis du ikke har læst denne artikel, anbefaler jeg stærkt, at du læser den, før du fortsætter. Du vil opdage en masse nye ting, det garanterer jeg 🙂
Spørgsmålet er dog stadig åbent - hvordan tilføjer man muligheden for at bruge regulære udtryk i Power Query? Power Query er selvfølgelig god i sig selv og kan meget med tekst (klippe, lime, rense osv.), men hvis man kunne krydse det med regulære udtryks magt, ville det bare være en bombe.
Desværre er der ingen indbyggede funktioner til at arbejde med RegExps i Power Query, og officiel Microsoft-hjælp og teknisk support besvarer dette spørgsmål benægtende. Der er dog en vej uden om denne begrænsning 🙂
Essensen af metoden
Hovedidéen er enkel at vanære.
På listen over indbyggede Power Query-funktioner er der en funktion Hjemmeside. Beskrivelsen af denne funktion på det officielle Microsoft-hjælpested er ekstremt kortfattet:
Oversat ville dette være: "Returnerer indholdet af HTML-dokumentet opdelt i dets komponentstrukturer, samt en repræsentation af det komplette dokument og dets krop, efter at taggene er blevet fjernet." Helt klart en halvdårlig beskrivelse.
Normalt bruges denne funktion ved import af data fra nettet og erstattes automatisk, f.eks. når vi vælger på fanen data Kommando Fra internettet (Data — fra nettet). Vi giver funktionen en webside som argument, og den returnerer dens indhold til os i form af tabeller, efter at have ryddet alle tags tidligere.
Hvad hjælpen IKKE siger, er det ud over HTML-markeringssproget funktion Hjemmeside understøtter JavaScript-scripts, som nu er allestedsnærværende på hjemmesider på internettet. Og JavaScript har til gengæld altid kunnet arbejde med regulære udtryk og har indbyggede funktioner til RegExps! Så for at implementere regulære udtryk i Power Query, bliver vi nødt til at feed Web.Page fungerer som et argument til et lille JavaScript-program, der vil gøre alt arbejdet for Power Query.
Sådan ser det ud i ren JavaScript
Der er en masse detaljerede tutorials om at arbejde med regulære udtryk i JavaScript på internettet (for eksempel en, to).
Kort og forenklet vil JavaScript-koden se sådan ud:
Her:
- var str = 'Betal regning 123 og 789 for pølse'; – oprette en variabel str og tildel den kildeteksten, som vi vil analysere.
- var mønster = /d+/gi; – opret et regulært udtryk og læg det i en variabel mønster.
Udtrykket starter med en skråstreg (/).
Selve udtrykket her er f.eks d+ står for enhver sekvens af cifre.
Gennem brøken efter udtrykket er der yderligere søgeparametre (modifikatorer) - de kan angives i vilkårlig rækkefølge:
- g – betyder global søgning, dvs. efter at have fundet et match, skal du ikke stoppe, men fortsætte søgningen til slutningen af teksten. Hvis denne modifikator ikke er indstillet, vil vores script kun returnere det første match (123)
- i – søgning uden hensyntagen til store og små bogstaver
- m – flerlinjesøgning (bruges når kildeteksten er opdelt i flere linjer)
- var resultat = str.match(pattern).join(';'); – udfør en søgning i kildeteksten (str) ved det givne regulære udtryk (mønster) og sæt resultaterne i en variabel resultere, sammenkæde dem med et semikolon ved hjælp af kommandoen deltage
- document.write(result); – Vis indholdet af resultatvariablen
Bemærk også, at tekststrenge (undtagen regulære udtryk) i JavaScript er omgivet af apostrof, ikke anførselstegn, som de er i Power Query eller VBA.
Ved udgangen vil dette script give os som et resultat alle de tal, der findes i kildeteksten:
123, 789
JavaScript kort kursus er slut, tak til jer alle. Håber du forstår logikken 🙂
Det er tilbage at overføre denne konstruktion til Power Query.
Søg og udpak tekstfunktion ved regulært udtryk i Power Query
Vi gør følgende:
1. Åbn Excel, og opret en ny tom Power Query på fanen Data – Hent data / Opret anmodning – Fra andre kilder – Tom anmodning (Data — Hent data / Ny forespørgsel — Fra andre kilder — Tom forespørgsel). Hvis du har en gammel version af Excel 2010-2013 og Power Query, du ikke har indbygget, men er installeret som et separat tilføjelsesprogram, så vil alt dette være på fanen StrømforespørgselOg ikke data.
2. Indtast straks navnet på vores fremtidige funktion i det tomme vindue i forespørgselseditoren, der åbnes, i højre panel (f.eks. fxRegExpExtract)
3. Lad os gå til fanen Vis – Avanceret editor (Vis — Avanceret editor), sletter vi hele M-koden for den tomme anmodning og indsætter koden for vores superfunktion der:
Pas på dine hænder:
I den første linje siger vi, at vores funktion vil have tre tekstargumenter: txt – den originale tekst, der analyseres, regulært udtryk - regulært udtryksmønster, afgrænse — afgrænsningstegn til visning af resultater.
Dernæst kalder vi funktionen Hjemmeside, der danner JavaScript-koden beskrevet ovenfor i sit argument. Vi indsætter og erstatter vores variable argumenter i koden.
Fragment:
[Data]{0}[Børn]{0}[Børn]{1}[Tekst]{0}
… er nødvendig for at "falde igennem" i tabellen med de resultater, vi har brug for. Pointen er, at funktionen Hjemmeside som et resultat, producerer den flere indlejrede tabeller, der gentager strukturen på en webside. Uden dette stykke M-kode ville vores funktion udsende dette:
… og vi bliver nødt til at klikke på ordet flere gange Bordlampe, successivt "falder igennem" i underordnede indlejrede tabeller i kolonner Børn:
I stedet for alt dette citat angiver vi straks i koden for vores funktion, hvilken indlejret tabel og kolonne (tekst) vi behøver.
Her er faktisk alle hemmelighederne. Det er tilbage at trykke på knappen Finish i vinduet avanceret editor, hvor vi har indsat vores kode, og du kan gå videre til det lækreste – prøv vores funktion på arbejdet.
Her er et par frøeksempler.
Eksempel 1. Hentning af kontonummer og dato fra betalingsbeskrivelsen
Vi har et kontoudtog med en beskrivelse (formål) med betalinger, hvor du skal trække numre og datoer på betalte fakturaer i separate kolonner:
Vi indlæser tabellen i Power Query som standard Data – Fra tabel/område (Data - Fra Tstand/REngel).
Så tilføjer vi en beregnet kolonne med vores funktion via Tilføj kolonne – Kald brugerdefineret funktion (Tilføj kolonne – Kald brugerdefineret funktion) og indtast dens argumenter:
Som et regulært udtryk (argument regulært udtryk) skabelon vi bruger:
(d{3,5}|d{2}.d{2}.d{4})
… oversat til menneskeligt sprog betyder:
tal fra 3 til 5 cifre (kontonumre)
or
fragmenter af formen "2-bit tal - punkt - 2-bit tal - punkt - 4-bit tal", dvs. datoer på formen DD.MM.ÅÅÅÅ.
Som afgrænsningstegn (argument afgrænse) indtast et semikolon.
Efter at have klikket på OK vores magiske funktion analyserer alle indledende data i henhold til vores regulære udtryk og danner en kolonne for os med de fundne tal og datoer på fakturaerne:
Det er tilbage at adskille det med semikolon ved hjælp af kommandoen Hjem — Opdelt kolonne — Efter afgrænsning (Hjem — Opdelt kolonne — Efter afgrænsning) og vi får, hvad vi ønskede:
Skønhed!
Eksempel 2: Udtræk e-mail-adresser fra tekst
Antag, at vi har følgende tabel som indledende data:
… hvorfra vi skal trække de e-mail-adresser, der findes der, (for klarhedens skyld fremhævede jeg dem med rødt i teksten).
Som i det foregående eksempel indlæser vi tabellen i Power Query på standard måde via Data – Fra tabel/område (Data - Fra Tstand/REngel).
Så tilføjer vi en beregnet kolonne med vores funktion via Tilføj kolonne – Kald brugerdefineret funktion (Tilføj kolonne – Kald brugerdefineret funktion) og indtast dens argumenter:
Parsing af e-mailadresser er en sværere opgave, og der er en masse regulære udtryk af varierende grader af mareridt for at løse det. Jeg brugte en af de enkle muligheder - ikke ideel, men fungerer ganske i de fleste tilfælde:
[w|.|-]*@w*.[w|.]*
Som separator (afgrænse) kan du indtaste et semikolon og et mellemrum.
Klik på OK og vi får en kolonne med e-mail-adresser udtrukket fra den originale tekst "grød":
Magi!
PS
Som man siger: "Der er ikke sådan en god ting, der ikke kunne gøres endnu bedre." Power Query er cool i sig selv, og når det kombineres med regulære udtryk, giver det os fuldstændig urealistisk kraft og fleksibilitet i behandlingen af tekstdata. Jeg håber, at Microsoft en dag vil tilføje RegExp-understøttelse i Power Query og Power BI-opdateringer, og at alle de ovennævnte danse med en tamburin vil blive en saga blot. Nå, for nu, ja.
Jeg vil også tilføje, at det er praktisk at lege med regulære udtryk på siden https://regexr.com/ – lige i online-editoren. Der i afsnittet Fællesskabsmønstre Der er et enormt antal færdige regulære sæsoner til alle lejligheder. Eksperimenter – al kraften ved regulære udtryk er nu til din tjeneste i Power Query!
- Hvad er regulære udtryk (RegExp) og hvordan man bruger dem i Excel
- Fuzzy tekstsøgning i Power Query
- Samling af tabeller fra forskellige filer ved hjælp af Power Query