Inhalte
Wenn Sie mit regulären Ausdrücken zumindest ein wenig vertraut sind, müssen Sie sie nicht bewerben. Falls Sie noch nicht ganz im Thema sind, dann sind reguläre Ausdrücke (Regular Expressions = RegExp = „regexps“ = „regulars“) eine Sprache, bei der anhand von Sonderzeichen und Regeln im Text nach den notwendigen Teilzeichenfolgen gesucht, diese extrahiert werden oder durch anderen Text ersetzt werden. Dies ist ein sehr leistungsfähiges und schönes Werkzeug, das allen anderen Arten der Arbeit mit Text um eine Größenordnung überlegen ist.
Ich habe bereits ausführlich und mit vielen Beispielen aus dem Leben beschrieben, wie Sie mit einfachen Makros die Unterstützung regulärer Ausdrücke zu Excel hinzufügen können – wenn Sie diesen Artikel noch nicht gelesen haben, empfehle ich Ihnen dringend, ihn zu lesen, bevor Sie fortfahren. Ihr werdet viel Neues entdecken, das garantiere ich 🙂
Die Frage bleibt jedoch offen – wie fügt man die Möglichkeit hinzu, reguläre Ausdrücke in Power Query zu verwenden? Power Query ist natürlich gut für sich allein und kann viel mit Text machen (schneiden, kleben, reinigen usw.), aber wenn Sie es mit der Kraft regulärer Ausdrücke kreuzen könnten, wäre es nur eine Bombe.
Leider gibt es keine integrierten Funktionen für die Arbeit mit RegExps in Power Query, und die offizielle Microsoft-Hilfe und der technische Support beantworten diese Frage negativ. Es gibt jedoch einen Weg, diese Einschränkung zu umgehen 🙂
Das Wesen der Methode
Die Hauptidee ist einfach zu blamieren.
In der Liste der integrierten Power Query-Funktionen gibt es eine Funktion Website. Die Beschreibung dieser Funktion auf der offiziellen Hilfeseite von Microsoft ist äußerst knapp:
Übersetzt wäre dies: „Liefert den Inhalt des HTML-Dokuments, zerlegt in seine Komponentenstrukturen, sowie eine Darstellung des vollständigen Dokuments und seines Bodys, nachdem die Tags entfernt wurden.“ So lala Beschreibung, ehrlich gesagt.
Normalerweise wird diese Funktion beim Importieren von Daten aus dem Web verwendet und beispielsweise automatisch ersetzt, wenn wir auf der Registerkarte auswählen Datum Befehl Aus dem Internet (Daten — Aus dem Internet). Wir geben der Funktion eine Webseite als Argument und sie gibt uns ihren Inhalt in Form von Tabellen zurück, nachdem sie zuvor alle Tags gelöscht hat.
Was die Hilfe NICHT sagt, ist das zusätzlich zur HTML-Auszeichnungssprache Funktion Website unterstützt JavaScript-Skripte, die mittlerweile auf Websites im Internet allgegenwärtig ist. Und JavaScript wiederum konnte schon immer mit regulären Ausdrücken arbeiten und hat eingebaute Funktionen für RegExps! Um also reguläre Ausdrücke in Power Query zu implementieren, müssen wir die Web.Page-Funktionen als Argument an ein kleines JavaScript-Programm übergeben, das die gesamte Arbeit für Power Query erledigt.
Wie es in reinem JavaScript aussieht
Im Internet gibt es viele ausführliche Tutorials zum Arbeiten mit regulären Ausdrücken in JavaScript (z. B. eins, zwei).
Kurz und vereinfacht sieht der JavaScript-Code so aus:
Hier:
- var str = 'Bezahle Rechnungen 123 und 789 für Wurst'; – Erstellen Sie eine Variable str und weisen Sie ihm den Quelltext zu, den wir analysieren.
- var Muster = /d+/gi; – Erstellen Sie einen regulären Ausdruck und fügen Sie ihn in eine Variable ein Anleitungen.
Der Ausdruck beginnt mit einem Schrägstrich (/).
Der Ausdruck selbst ist hier zum Beispiel d+ steht für eine beliebige Ziffernfolge.
Durch den Bruch nach dem Ausdruck gibt es zusätzliche Suchparameter (Modifikatoren) – sie können in beliebiger Reihenfolge angegeben werden:
- g – bedeutet globale Suche, dh Sie sollten nach einem Treffer nicht aufhören, sondern die Suche bis zum Ende des Textes fortsetzen. Wenn dieser Modifikator nicht gesetzt ist, gibt unser Skript nur die erste Übereinstimmung zurück (123).
- i – Suche ohne Rücksicht auf Buchstaben
- m – Mehrzeilige Suche (wird verwendet, wenn der Ausgangstext in mehrere Zeilen unterteilt ist)
- var result = str.match(pattern).join(';'); – eine Suche im Quelltext durchführen (str) durch den gegebenen regulären Ausdruck (Anleitungen) und die Ergebnisse in eine Variable schreiben Folge, indem Sie sie mit dem Befehl mit einem Semikolon verketten join
- document.write (Ergebnis); – den Inhalt der Ergebnisvariablen anzeigen
Beachten Sie auch, dass Textzeichenfolgen (mit Ausnahme von regulären Ausdrücken) in JavaScript in Apostrophe und nicht in Anführungszeichen eingeschlossen sind, wie dies in Power Query oder VBA der Fall ist.
Als Ergebnis liefert uns dieses Skript alle im Quelltext gefundenen Zahlen:
123, 789
Der JavaScript-Kurzkurs ist beendet, danke an alle. Hoffe du verstehst die Logik 🙂
Es bleibt, diese Konstruktion auf Power Query zu übertragen.
Suchen und extrahieren Sie Textfunktionen nach regulären Ausdrücken in Power Query
Wir machen Folgendes:
1. Öffnen Sie Excel und erstellen Sie im Tab eine neue leere Power Query Daten – Daten abrufen / Anfrage erstellen – Aus anderen Quellen – Leere Anfrage (Daten – Daten abrufen / Neue Abfrage – Aus anderen Quellen – Leere Abfrage). Wenn Sie eine alte Version von Excel 2010-2013 und Power Query haben, die Sie nicht integriert haben, sondern als separates Add-In installiert wurde, wird dies alles auf der Registerkarte angezeigt Power QueryUnd nicht Datum.
2. Geben Sie im sich öffnenden leeren Fenster des Abfrage-Editors im rechten Bereich sofort den Namen unserer zukünftigen Funktion ein (z. B. fxRegExpExtract)
3. Kommen wir zur Registerkarte Ansicht – Erweiterter Editor (Ansicht – Erweiterter Editor), löschen wir den gesamten M-Code der leeren Anfrage und fügen dort den Code unserer Superfunktion ein:
Pass auf deine Hände auf:
In der ersten Zeile sagen wir, dass unsere Funktion drei Textargumente haben wird: txt – der zu analysierende Originaltext, Regex – reguläres Ausdrucksmuster, delim — Trennzeichen zum Anzeigen von Ergebnissen.
Als nächstes rufen wir die Funktion auf Website, das den oben beschriebenen JavaScript-Code in seinem Argument bildet. Wir fügen unsere Variablenargumente in den Code ein und ersetzen sie.
Fragment:
[Daten]{0}[Kinder]{0}[Kinder]{1}[Text]{0}
… wird benötigt, um in die Tabelle mit den von uns benötigten Ergebnissen „durchzufallen“. Der Punkt ist, dass die Funktion Website Als Ergebnis werden mehrere verschachtelte Tabellen erstellt, die die Struktur einer Webseite wiederholen. Ohne diesen M-Code würde unsere Funktion Folgendes ausgeben:
… und wir müssten das Wort mehrmals anklicken Tisch, die sukzessive in untergeordnete verschachtelte Tabellen in Spalten „durchfallen“. weltweit:
Anstelle dieses ganzen Zitats geben wir im Code unserer Funktion sofort an, welche verschachtelte Tabelle und Spalte (Text) wir brauchen.
Hier sind in der Tat alle Geheimnisse. Es bleibt die Taste zu drücken Endziel im Fenster fortgeschrittener Editor, wo wir unseren Code eingefügt haben, und Sie können mit dem Leckersten fortfahren – probieren Sie unsere Funktion bei der Arbeit aus.
Hier sind ein paar Seed-Beispiele.
Beispiel 1. Abrufen der Kontonummer und des Datums aus der Zahlungsbeschreibung
Wir haben einen Kontoauszug mit einer Beschreibung (Zweck) der Zahlungen, wo Sie die Nummern und Daten der bezahlten Rechnungen in separate Spalten ziehen müssen:
Wir laden die Tabelle auf dem Standardweg in Power Query Daten – Aus Tabelle/Bereich (Daten — Von Tfähig/REngel).
Dann fügen wir mit unserer Funktion via eine berechnete Spalte hinzu Spalte hinzufügen – Benutzerdefinierte Funktion aufrufen (Spalte hinzufügen – Benutzerdefinierte Funktion aufrufen) und geben Sie seine Argumente ein:
Als regulärer Ausdruck (argument Regex) Vorlage verwenden wir:
(d{3,5}|d{2}.d{2}.d{4})
… in die menschliche Sprache übersetzt bedeutet:
3- bis 5-stellige Zahlen (Kontonummern)
or
Fragmente der Form „2-Bit-Zahl – Punkt – 2-Bit-Zahl – Punkt – 4-Bit-Zahl“, also Datumsangaben im Format TT.MM.JJJJ.
Als Trennzeichen (Argument delim) geben Sie ein Semikolon ein.
Nach dem Klicken auf OK unsere magische Funktion analysiert alle Ausgangsdaten nach unserem regulären Ausdruck und bildet für uns eine Spalte mit den gefundenen Nummern und Daten der Rechnungen:
Es bleibt, es mit dem Befehl durch Semikolon zu trennen Startseite – Spalte teilen – Nach Trennzeichen (Home — Spalte teilen — Nach Trennzeichen) und wir bekommen, was wir wollten:
Schönheit!
Beispiel 2: E-Mail-Adressen aus Text extrahieren
Angenommen, wir haben die folgende Tabelle als Ausgangsdaten:
… wo wir die dort gefundenen E-Mail-Adressen herausziehen müssen (der Übersichtlichkeit halber habe ich sie im Text rot hervorgehoben).
Wie im vorigen Beispiel laden wir die Tabelle ganz normal über in Power Query Daten – Aus Tabelle/Bereich (Daten — Von Tfähig/REngel).
Dann fügen wir mit unserer Funktion via eine berechnete Spalte hinzu Spalte hinzufügen – Benutzerdefinierte Funktion aufrufen (Spalte hinzufügen – Benutzerdefinierte Funktion aufrufen) und geben Sie seine Argumente ein:
Das Analysieren von E-Mail-Adressen ist eine schwierigere Aufgabe, und es gibt eine Reihe von regulären Ausdrücken mit unterschiedlichem Alptraum, um sie zu lösen. Ich habe eine der einfachen Optionen verwendet – nicht ideal, aber in den meisten Fällen recht gut funktionierend:
[w|.|-]*@w*.[w|.]*
Als Trennzeichen (delim) können Sie ein Semikolon und ein Leerzeichen eingeben.
Klicken Sie auf OK und wir erhalten eine Spalte mit E-Mail-Adressen, die aus dem Originaltext „Brei“ extrahiert wurden:
Magie!
PS
Frei nach dem Motto: „Es gibt nichts Gutes, das man nicht noch besser machen könnte.“ Power Query an sich ist cool, und in Kombination mit regulären Ausdrücken gibt es uns völlig unrealistische Leistung und Flexibilität bei der Verarbeitung beliebiger Textdaten. Ich hoffe, Microsoft wird eines Tages RegExp-Unterstützung in Power Query- und Power BI-Updates hinzufügen und all die oben genannten Tänze mit einem Tamburin gehören der Vergangenheit an. Nun, für den Moment, ja.
Ich möchte auch hinzufügen, dass es bequem ist, mit regulären Ausdrücken auf der Seite https://regexr.com/ zu spielen – direkt im Online-Editor. Dort in der Sektion Gemeinschaftsmuster Es gibt eine große Anzahl vorgefertigter regulärer Saisons für alle Gelegenheiten. Experimentieren Sie – in Power Query steht Ihnen jetzt die gesamte Leistungsfähigkeit regulärer Ausdrücke zur Verfügung!
- Was sind reguläre Ausdrücke (RegExp) und wie werden sie in Excel verwendet?
- Fuzzy-Textsuche in Power Query
- Zusammenstellen von Tabellen aus verschiedenen Dateien mit Power Query