Massentextersetzung in Power Query mit List.Accumulate-Funktion

Wie man den Text laut Literaturverzeichnis schnell und massenweise durch Formeln ersetzt – wir haben es schon aussortiert. Versuchen wir es jetzt in Power Query.

Wie so oft ausführen Diese Aufgabe ist viel einfacher als zu erklären warum es funktioniert, aber lass uns versuchen, beides zu tun 🙂

Wir haben also zwei „intelligente“ dynamische Tabellen, die aus gewöhnlichen Bereichen mit einer Tastenkombination erstellt wurden Ctrl+T oder Team Startseite – Als Tabelle formatieren (Home — Als Tabelle formatieren):

Ich rief den ersten Tisch an Datum, der zweite Tisch – Verzeichnismit Feld Tabellenname (Tabellenname) Tab Bauherr (Design).

Aufgabe: Ersetzen Sie Adressen in der Tabelle Datum alle Vorkommen aus einer Spalte Finden Handbuch zu ihren entsprechenden korrekten Gegenstücken aus der Spalte Ersatz. Der Rest des Textes in den Zellen sollte unberührt bleiben.

Schritt 1. Laden Sie das Verzeichnis in Power Query und wandeln Sie es in eine Liste um

Nachdem Sie die aktive Zelle an eine beliebige Stelle in der Referenztabelle gesetzt haben, klicken Sie auf die Registerkarte Datum (Datum)oder auf der Registerkarte Power Query (wenn Sie eine alte Excel-Version haben und Power Query als Add-In auf einer separaten Registerkarte installiert haben) auf die Schaltfläche Aus Tabelle/Sortiment (Aus Tabelle/Bereich).

Die Referenztabelle wird in den Power Query-Abfrageeditor geladen:

Um nicht zu stören, ein automatisch hinzugefügter Schritt modifizierter Typ (Geänderter Typ) Im rechten Bereich können die angewendeten Schritte sicher gelöscht werden, wobei nur der Schritt übrig bleibt Quelle (Quelle):

Um nun weitere Transformationen und Ersetzungen durchzuführen, müssen wir diese Tabelle in eine Liste (Liste) umwandeln.

Lyrischer Exkurs

• Tisch ist ein zweidimensionales Array, das aus mehreren Zeilen und Spalten besteht.
• Aufzeichnung (Aufzeichnung) – eindimensionaler Array-String, bestehend aus mehreren Felder-Elementen mit zB Namen [Name = „Mascha“, Geschlecht = „w“, Alter = 25]
  
• Liste – eine eindimensionale Array-Spalte, die beispielsweise aus mehreren Elementen besteht {1, 2, 3, 10, 42} or { "Glaube Hoffnung Liebe" }
  

Um unser Problem zu lösen, interessiert uns in erster Linie der Typ Liste.

Der Trick dabei ist, dass Listenelemente in Power Query nicht nur banale Zahlen oder Text sein können, sondern auch andere Listen oder Datensätze. Es ist in solch einer kniffligen Liste (Liste), bestehend aus Aufzeichnungen (Aufzeichnungen), dass wir unser Verzeichnis umdrehen müssen. In der syntaktischen Notation von Power Query (Einträge in eckigen Klammern, Listen in geschweiften Klammern) würde das so aussehen:

{

    [ Suchen = „St. Petersburg“, Ersetzen = „St. Petersburg“ ] ,

    [ Suchen = „St. Petersburg“, Ersetzen = „St. Petersburg“ ] ,

    [ Suchen = „Peter“, Ersetzen = „St. Petersburg“ ] ,

usw.

}

Eine solche Transformation wird mit einer speziellen Funktion der in Power Query integrierten M-Sprache durchgeführt – Table.ToRecords. Um es direkt in der Bearbeitungsleiste anzuwenden, fügen Sie diese Funktion dort dem Schrittcode hinzu Quelle.

Es war:

Nach:

Nach dem Hinzufügen der Funktion Table.ToRecords ändert sich das Aussehen unserer Tabelle – sie wird zu einer Liste von Datensätzen. Der Inhalt einzelner Datensätze kann unten im Ansichtsbereich angezeigt werden, indem Sie neben einem beliebigen Wort auf den Zellenhintergrund klicken Rekord (aber nicht mit einem Wort!)

Zusätzlich zu den oben genannten ist es sinnvoll, einen weiteren Strich hinzuzufügen – um unsere erstellte Liste zwischenzuspeichern (zu puffern). Dadurch wird Power Query gezwungen, unsere Nachschlageliste einmal in den Speicher zu laden und sie nicht erneut zu berechnen, wenn wir später darauf zugreifen, um sie zu ersetzen. Wickeln Sie dazu unsere Formel in eine andere Funktion ein – List.Buffer:

Ein solches Caching führt zu einer deutlich spürbaren Geschwindigkeitssteigerung (um ein Vielfaches!), wenn eine große Menge anfänglicher Daten gelöscht werden muss.

Damit ist die Erstellung des Handbuchs abgeschlossen.

Es bleibt zu klicken Startseite – Schließen und Laden – Schließen und Laden in… (Home — Schließen&Laden — Schließen&Laden nach..), Wähle eine Option Stellen Sie einfach eine Verbindung her (Nur Verbindung herstellen) und zurück zu Excel.

Schritt 2. Laden der Datentabelle

Hier ist alles banal. Wie zuvor beim Nachschlagewerk gelangen wir an eine beliebige Stelle in der Tabelle, klicken auf den Reiter Datum Taste im nun erscheinenden Bestätigungsfenster nun wieder los. Aus Tabelle/Bereich und unser Tisch Datum kommt in Power Query. Automatisch hinzugefügter Schritt modifizierter Typ (Geänderter Typ) Sie können auch entfernen:

Es sind keine besonderen vorbereitenden Maßnahmen erforderlich, und wir kommen zum Wichtigsten.

Schritt 3. Führen Sie Ersetzungen mit der List.Accumulate-Funktion durch

Lassen Sie uns mit dem Befehl eine berechnete Spalte zu unserer Datentabelle hinzufügen Hinzufügen einer Spalte – Benutzerdefinierte Spalte (Spalte hinzufügen – Benutzerdefinierte Spalte): und geben Sie im sich öffnenden Fenster den Namen der hinzugefügten Spalte ein (z. B. korrigierte Adresse) und unsere magische Funktion List.Accumulate:

Es bleibt zu klicken OK – und wir erhalten eine Spalte mit den vorgenommenen Ersetzungen:

Beachten Sie, dass:

• Da bei Power Query zwischen Groß- und Kleinschreibung unterschieden wird, gab es in der vorletzten Zeile keinen Ersatz, denn im Verzeichnis steht „SPb“, nicht „SPb“.
• Wenn in den Quelldaten mehrere Teilstrings auf einmal zu ersetzen sind (z. B. müssen Sie in der 7. Zeile sowohl „S-Pb“ als auch „Prospectus“ ersetzen), dann verursacht dies keine Probleme (anders als das Ersetzen durch Formeln aus die vorherige Methode).
• Wenn im Quelltext (9. Zeile) nichts zu ersetzen ist, treten keine Fehler auf (wiederum anders als beim Ersetzen durch Formeln).

Die Geschwindigkeit einer solchen Anfrage ist sehr, sehr anständig. Beispielsweise wurde diese Abfrage für eine Tabelle mit Anfangsdaten mit einer Größe von 5000 Zeilen in weniger als einer Sekunde aktualisiert (ohne Pufferung übrigens etwa 3 Sekunden!).

Funktionsweise der List.Accumulate-Funktion

Im Prinzip könnte dies das Ende dieses Artikels sein (für mich zum Schreiben und für Sie zum Lesen). Wenn Sie nicht nur können, sondern auch verstehen möchten, wie es „unter der Haube“ funktioniert, müssen Sie etwas tiefer in den Kaninchenbau eintauchen und sich mit der List.Accumulate-Funktion auseinandersetzen, die den gesamten Massenaustausch erledigt hat Arbeit für uns.

Die Syntax für diese Funktion lautet:

=List.Accumulate(Liste, Samen, Akkumulator)

woher

• Liste ist die Liste, über deren Elemente wir iterieren. 
• Samen - Ausgangszustand
• Akkumulator – eine Funktion, die eine Operation (Mathematik, Text usw.) am nächsten Element der Liste ausführt und das Ergebnis der Verarbeitung in einer speziellen Variablen akkumuliert.
  

Im Allgemeinen sieht die Syntax zum Schreiben von Funktionen in Power Query wie folgt aus:

(argument1, argument2, … argumentN) => einige Aktionen mit Argumenten

Die Summationsfunktion könnte beispielsweise wie folgt dargestellt werden:

(a, b) => a + b

Für List.Accumulate hat diese Akkumulatorfunktion zwei erforderliche Argumente (sie können beliebig benannt werden, aber die üblichen Namen sind Zustand и Strom, wie in der offiziellen Hilfe für diese Funktion, wobei:

• Zustand – eine Variable, in der das Ergebnis akkumuliert wird (ihr Anfangswert ist der oben erwähnte Samen)
  
• Strom – der nächste iterierte Wert aus der Liste Liste
  

Schauen wir uns zum Beispiel die Schritte der Logik der folgenden Konstruktion an:

=List.Accumulate({3, 2, 5}, 10, (Zustand, Strom) => Zustand + Strom)

Dies ist die neueste kumulierte Zustand Der Wert ist unsere List.Accumulate-Funktion und gibt als Ergebnis aus:

Wenn Sie ein wenig phantasieren, können Sie mit der List.Accumulate-Funktion beispielsweise die Excel-Funktion CONCATENATE simulieren (in Power Query heißt ihr Analogon Text.Kombinieren) mit dem Ausdruck:

Oder suchen Sie sogar nach dem Maximalwert (Nachahmung der MAX-Funktion von Excel, die in Power Query aufgerufen wird Liste.Max):

Das Hauptmerkmal von List.Accumulate ist jedoch die Fähigkeit, nicht nur einfache Text- oder numerische Listen als Argumente zu verarbeiten, sondern komplexere Objekte – zum Beispiel Listen-von-Listen oder Listen-von-Datensätzen (Hallo, Verzeichnis!)

Schauen wir uns noch einmal die Konstruktion an, die die Ersetzung in unserem Problem durchgeführt hat:

List.Accumulate(Verzeichnis, [Adresse], (state,current) => Text.Replace(state, current[Find], current[Replace]) )

Was ist hier wirklich los?

1. Als Anfangswert (Samen) nehmen wir den ersten ungeschickten Text aus der Spalte [Adresse] unser Tisch: 199034, St. Petersburg, str. Beringa, gest. 1
2. Dann iteriert List.Accumulate nacheinander über die Elemente der Liste – Handbuch. Jedes Element dieser Liste ist ein Datensatz, der aus einem Feldpaar „Was zu finden – Wodurch zu ersetzen“ besteht, oder mit anderen Worten, die nächste Zeile im Verzeichnis.
3. Die Akkumulatorfunktion setzt in eine Variable Zustand Anfangswert (erste Adresse 199034, St. Petersburg, str. Beringa, gest. 1) und führt darauf eine Akkumulatorfunktion aus – die Ersatzoperation mit der Standard-M-Funktion Text.Ersetzen (analog zur Excel-Funktion SUBSTITUTE). Seine Syntax ist:

   Text.Replace(ursprünglicher Text, wonach wir suchen, womit wir ersetzen)

   und hier haben wir:

• Zustand ist unsere schmutzige Adresse, die in liegt Zustand (Anreise von Samen)
  
• aktuell[Suchen] - Feldwert Finden vom nächsten iterierten Eintrag der Liste Verzeichnis, die in der Variablen liegt Strom
• aktuell[Ersetzen] - Feldwert Ersatz vom nächsten iterierten Eintrag der Liste Verzeichnisliegt in Strom

Somit wird für jede Adresse jedes Mal ein vollständiger Zyklus der Aufzählung aller Zeilen im Verzeichnis ausgeführt, wobei der Text aus dem Feld [Suchen] durch den Wert aus dem Feld [Ersetzen] ersetzt wird.

Hoffe du hast die Idee 🙂

• Massenersetzen von Text in einer Liste mithilfe von Formeln
  
• Reguläre Ausdrücke (RegExp) in Power Query