Inhoud
Als u op zijn minst enigszins bekend bent met reguliere expressies, hoeft u er geen reclame voor te maken. Als je niet helemaal in het onderwerp zit, dan zijn reguliere expressies (Regular Expressions = RegExp = "regexps" = "regulars") een taal waarin, met behulp van speciale tekens en regels, de benodigde substrings in de tekst worden gezocht, ze worden geëxtraheerd of vervangen door andere tekst. Dit is een zeer krachtige en mooie tool, een orde van grootte die superieur is aan alle andere manieren om met tekst te werken.
Ik heb al in detail en met een heleboel voorbeelden uit het leven beschreven hoe je ondersteuning voor reguliere expressies aan Excel kunt toevoegen met behulp van eenvoudige macro's - als je dit artikel niet hebt gelezen, raad ik je ten zeerste aan het te lezen voordat je verder gaat. Je zult veel nieuwe dingen ontdekken, dat garandeer ik
De vraag blijft echter open: hoe voeg je de mogelijkheid toe om reguliere expressies te gebruiken in Power Query? Power Query is natuurlijk goed op zichzelf en kan veel met tekst (knippen, lijmen, opschonen, enz.), maar als je het zou kunnen kruisen met de kracht van reguliere expressies, zou het gewoon een bom zijn.
Helaas zijn er geen ingebouwde functies voor het werken met RegExps in Power Query, en officiële Microsoft-hulp en technische ondersteuning beantwoorden deze vraag ontkennend. Er is echter een manier om deze beperking te omzeilen
De essentie van de methode
Het hoofdidee is eenvoudig te schande te maken.
In de lijst met ingebouwde Power Query-mogelijkheden staat een functie: Webpagina. De beschrijving van deze functie op de officiële Microsoft-helpsite is uiterst beknopt:
Vertaald, zou dit zijn: "Retourneert de inhoud van het HTML-document, opgesplitst in zijn componentstructuren, evenals een weergave van het volledige document en zijn hoofdtekst nadat de tags zijn verwijderd." Zo-zo beschrijving, eerlijk gezegd.
Meestal wordt deze functie gebruikt bij het importeren van gegevens van het web en wordt deze automatisch vervangen, bijvoorbeeld wanneer we selecteren op het tabblad Data commando Van het internet (Gegevens — Van internet). We geven de functie een webpagina als argument, en het geeft ons de inhoud terug in de vorm van tabellen, nadat we eerder alle tags hebben gewist.
Wat de help NIET zegt, is dat naast de HTML-opmaaktaal functie Webpagina ondersteunt JavaScript-scripts, die nu alomtegenwoordig is op websites op internet. En JavaScript heeft op zijn beurt altijd met reguliere expressies kunnen werken en heeft ingebouwde functies voor RegExps! Dus om reguliere expressies in Power Query te implementeren, moeten we de Web.Page-functies als argument aan een klein JavaScript-programma geven dat al het werk voor Power Query zal doen.
Hoe het eruit ziet in puur JavaScript
Er zijn veel gedetailleerde tutorials over het werken met reguliere expressies in JavaScript op internet (bijvoorbeeld één, twee).
In het kort en vereenvoudigd ziet de JavaScript-code er als volgt uit:
Hier:
- var str = 'Betaal rekeningen 123 en 789 voor worst'; – maak een variabele str en wijs het de brontekst toe die we zullen analyseren.
- var patroon = /d+/gi; – maak een reguliere expressie en plaats deze in een variabele patroon.
De uitdrukking begint met een schuine streep (/).
De uitdrukking zelf is hier bijvoorbeeld d+ staat voor elke reeks cijfers.
Door de breuk na de uitdrukking zijn er aanvullende zoekparameters (modifiers) - ze kunnen in elke volgorde worden opgegeven:
- g – betekent globaal zoeken, dwz na het vinden van een overeenkomst moet u niet stoppen, maar doorgaan met zoeken tot het einde van de tekst. Als deze modifier niet is ingesteld, retourneert ons script alleen de eerste overeenkomst (123)
- i – zoeken zonder rekening te houden met hoofdletters
- m – zoeken op meerdere regels (gebruikt wanneer de brontekst in meerdere regels is verdeeld)
- var resultaat = str.match(patroon).join(';'); – zoek in de brontekst (str) door de gegeven reguliere expressie (patroon) en zet de resultaten in een variabele resultaat, voeg ze samen met een puntkomma met het commando mee
- document.schrijven(resultaat); – toon de inhoud van de resultaatvariabele
Houd er ook rekening mee dat tekenreeksen (exclusief reguliere expressies) in JavaScript tussen apostrofs staan, geen aanhalingstekens zoals in Power Query of VBA.
Bij de uitvoer geeft dit script ons als resultaat alle nummers die in de brontekst worden gevonden:
123, 789
De korte cursus JavaScript is voorbij, bedankt allemaal. Hoop dat je de logica snapt
Het blijft om deze constructie over te zetten naar Power Query.
Zoek en extraheer tekstfunctie op reguliere expressie in Power Query
Wij doen het volgende:
1. Open Excel en maak een nieuwe lege Power Query aan op het tabblad Gegevens – Gegevens ophalen / Aanvraag maken – Van andere bronnen – Leeg verzoek (Gegevens — Gegevens ophalen / Nieuwe query — Van andere bronnen — Lege query). Als je een oude versie van Excel 2010-2013 en Power Query hebt die je niet ingebouwd hebt, maar als aparte invoegtoepassing was geïnstalleerd, dan staat dit allemaal op het tabblad Power QueryEn niet Data.
2. Voer in het lege venster van de query-editor die wordt geopend, in het rechterdeelvenster, onmiddellijk de naam van onze toekomstige functie in (bijvoorbeeld fxRegExpExtract)
3. Laten we naar het tabblad gaan Bekijken – Geavanceerde Editor (Bekijken - Geavanceerde editor), wissen we de volledige M-code van het lege verzoek en plakken daar de code van onze superfunctie:
Let op je handen:
In de eerste regel zeggen we dat onze functie drie tekstargumenten zal hebben: txt – de originele tekst die wordt geanalyseerd, regex – patroon van reguliere expressies, delim — scheidingsteken voor het weergeven van resultaten.
Vervolgens noemen we de functie Webpagina, waardoor de JavaScript-code wordt gevormd die hierboven in zijn argument is beschreven. We plakken en vervangen onze variabele argumenten in de code.
Fragment:
[Gegevens]{0}[Kinderen]{0}[Kinderen]{1}[Tekst]{0}
... is nodig om "door te vallen" in de tabel met de resultaten die we nodig hebben. Het punt is dat de functie Webpagina als resultaat produceert het verschillende geneste tabellen die de structuur van een webpagina herhalen. Zonder dit stuk M-code zou onze functie dit uitvoeren:
… en we zouden meerdere keren op het woord moeten klikken tafel, achtereenvolgens "doorvallen" in onderliggende geneste tabellen in kolommen Kinderen:
In plaats van al deze citaten, geven we meteen in de code van onze functie aan welke geneste tabel en kolom (Tekst) wij hebben nodig.
Hier zijn in feite alle geheimen. Het blijft om op de knop te drukken Finish in het raam geavanceerde editor, waar we onze code hebben ingevoegd, en u kunt doorgaan naar de meest heerlijke - probeer onze functie op het werk.
Hier zijn een paar zaadvoorbeelden.
Voorbeeld 1. Rekeningnummer en datum ophalen uit de betalingsomschrijving
We hebben een bankafschrift met een omschrijving (doel) van betalingen, waarbij u de aantallen en datums van betaalde facturen in aparte kolommen moet zetten:
We laden de tabel op de standaard manier in Power Query Gegevens – Uit tabel/bereik (Gegevens — Van Tstaat/Rengel).
Dan voegen we een berekende kolom toe met onze functie via Kolom toevoegen – Aangepaste functie aanroepen (Kolom toevoegen — Aangepaste functie oproepen) en voer de argumenten in:
Als reguliere expressie (argument regex) sjabloon die we gebruiken:
(d{3,5}|d{2}.d{2}.d{4})
… vertaald in mensentaal betekenis:
nummers van 3 tot 5 cijfers (rekeningnummers)
or
fragmenten van de vorm “2-bits getal – punt – 2-bits getal – punt – 4-bits getal”, dat wil zeggen data van de vorm DD.MM.JJJJ.
Als scheidingsteken (argument delim) voer een puntkomma in.
Na het klikken op OK onze magische functie analyseert alle initiële gegevens volgens onze reguliere expressie en vormt voor ons een kolom met de gevonden nummers en datums van de facturen:
Het blijft om het te scheiden door een puntkomma met behulp van de opdracht Home — Kolom splitsen — Per scheidingsteken (Home — Kolom splitsen — Per scheidingsteken) en we krijgen wat we wilden:
Schoonheid!
Voorbeeld 2: E-mailadressen uit tekst extraheren
Stel dat we de volgende tabel als initiële gegevens hebben:
... van waaruit we de e-mailadressen die daar worden gevonden moeten halen (voor de duidelijkheid heb ik ze in de tekst rood gemarkeerd).
Net als in het vorige voorbeeld laden we de tabel op de standaard manier in Power Query via Gegevens – Uit tabel/bereik (Gegevens — Van Tstaat/Rengel).
Dan voegen we een berekende kolom toe met onze functie via Kolom toevoegen – Aangepaste functie aanroepen (Kolom toevoegen — Aangepaste functie oproepen) en voer de argumenten in:
Het ontleden van e-mailadressen is een moeilijkere taak en er zijn een aantal reguliere expressies van verschillende gradaties van nachtmerries om het op te lossen. Ik heb een van de eenvoudige opties gebruikt - niet ideaal, maar in de meeste gevallen best werkend:
[w|.|-]*@w*.[w|.]*
Als scheidingsteken (delim) kunt u een puntkomma en een spatie invoeren.
Klik op OK en we krijgen een kolom met e-mailadressen die zijn geëxtraheerd uit de originele tekst "pap":
Magic!
PS
Zoals het gezegde luidt: "Er is niet zoiets goeds dat niet nog beter kan worden gemaakt." Power Query is op zichzelf al cool en in combinatie met reguliere expressies geeft het ons volledig onrealistische kracht en flexibiliteit bij het verwerken van tekstgegevens. Ik hoop dat Microsoft ooit RegExp-ondersteuning zal toevoegen in Power Query- en Power BI-updates en dat alle bovenstaande dansen met een tamboerijn tot het verleden zullen behoren. Nou ja, voorlopig wel.
Ik wil er ook aan toevoegen dat het handig is om met reguliere expressies te spelen op de site https://regexr.com/ – direct in de online editor. Daar in de sectie Gemeenschapspatronen Er zijn een groot aantal kant-en-klare reguliere seizoenen voor alle gelegenheden. Experimenteer - alle kracht van reguliere expressies staat nu tot uw dienst in Power Query!
- Wat zijn reguliere expressies (RegExp) en hoe gebruik je ze in Excel?
- Fuzzy tekst zoeken in Power Query
- Tabellen samenstellen uit verschillende bestanden met Power Query