Dieses versteckte Excel-Tool kann Ihre Daten automatisch schnell bereinigen
M ist die leistungsstarke Formelsprache, die Power Query im Hintergrund steuert. Obwohl es wie komplexer Code aussehen mag, sind viele gängige Aufgaben in M überraschend anfängerfreundlich und können die Datenbereinigung schnell automatisieren. Wenn Sie eine grundlegende Excel-Funktion schreiben können, können Sie anfangen, M zu lernen, um Ihre umfangreichsten Datensätze zu bearbeiten.
Viele Menschen beginnen im Power Query-Editor, indem sie die Schaltflächen im Menüband verwenden, und das ist in Ordnung. Aber diese Methode zwingt einen zu langen Klickmarathons. M hilft, das Durcheinander zu durchbrechen, indem es ermöglicht, mehrere Transformationen in einem einzelnen Schritt zu kombinieren, der leichter zu verwalten, wiederzuverwenden und zu pflegen ist.
M-Sprache: Wie man anfängt
Machen Sie sich mit der Power Query-Oberfläche vertraut
Stellen Sie sich vor, Sie haben gerade den oben genannten Datensatz erhalten. Es ist ein Wirrwarr aus verwirrenden Bestellnummern, inkonsistent großgeschriebenen Namen und unvollständigen Regionen.
Um beim Lesen dieses Leitfadens mitzugehen, laden Sie eine kostenlose Kopie der Excel-Arbeitsmappe herunter, die in den Beispielen verwendet wird. Nachdem Sie auf den Link geklickt haben, finden Sie die Schaltfläche zum Herunterladen in der oberen rechten Ecke Ihres Bildschirms.
Um es mit M zu beheben, müssen Sie zunächst Ihre Umgebung vorbereiten. Angenommen, Ihr Datensatz ist bereits als Excel-Tabelle mit dem Namen T_SalesData formatiert, befolgen Sie diese Schritte:
- Wählen Sie eine beliebige Zelle in Ihrer Tabelle aus, und klicken Sie im Daten-Tab auf Aus Tabelle/Bereich.
- Sobald der Power Query-Editor geöffnet ist, stellen Sie sicher, dass die weiße Leiste über Ihrem Datenraster (die Formelleiste) sichtbar ist. Falls nicht, öffnen Sie die Ansicht-Registerkarte und aktivieren Sie Formelleiste.
- Suchen Sie die fx-Taste links neben der Formelleiste. Wenn Sie auf dieses Symbol klicken, erstellen Sie einen Benutzerdefinierten Schritt, in den Sie Ihren Code eingeben oder einfügen können.
Snippet 1: Unordentliche Namensschreibweise standardisieren
Verwendung von Text.Proper, um die Großschreibung vor Ort zu korrigieren
Das Ziel: Die Namen in der CustomerName-Spalte zu korrigieren, die entweder komplett in Großbuchstaben, komplett in Kleinbuchstaben oder zufällig gemischt sind, sodass nur der erste Buchstabe jedes Wortes großgeschrieben wird.
Klicken Sie auf das fx-Symbol links neben der Formelleiste, um einen neuen Schritt zu erstellen. Ersetzen Sie dann alles in der Formelleiste durch den folgenden Code und drücken Sie Enter:
= Table.TransformColumns(#"Changed Type", {{"CustomerName", each Text.Proper(_), type text}})
Power Query weist dem Schritt einen generischen Namen wie Custom1 zu. Klicken Sie mit der rechten Maustaste auf diesen generischen Schrittnamen im Bereich Angenommene Schritte und klicken Sie auf Umbenennen, um ihm einen beschreibenderen Namen wie FixCasing zu geben.
Für diesen ersten Ausschnitt werde ich seine vollständige Anatomie in der untenstehenden Tabelle aufschlüsseln. Für die verbleibenden drei Ausschnitte werde ich mich nur auf die neuen oder sich ändernden Teile konzentrieren, da das Gesamtmuster gleich bleibt.
Element | Beschreibung |
|---|---|
Tabelle.SpaltenTransformieren(...) | Die primäre Funktion wird verwendet, um bestehende Spalten zu ändern. Die Klammern, die danach folgen, ‚umhüllen‘ die Argumente der Funktion. |
#"Geänderter Typ" | Bezieht sich auf den unmittelbar vorhergehenden Schritt in der Liste der angewendeten Schritte. Jeder Schritt muss wissen, welche Version der Tabelle als nächstes verarbeitet werden soll. |
Geschweifte Klammern: { } |
Eine Liste darstellen. Da Sie mehrere Spalten gleichzeitig transformieren können, verlangt M, dass Ihre Anweisungen innerhalb dieser geschweiften Klammern enthalten sind. |
"Kundenname" | Identifiziert die spezifische Spalte, die geändert werden soll. Doppelte Anführungszeichen definieren den Namen als wörtlichen Text. Geben Sie einfach einen anderen Namen ein, um denselben Schritt auf eine andere Spalte anzuwenden. |
Text eingeben | Erzwingt, dass die Spalte einen bestimmten Datentyp hat. Dies verhindert, dass Excel Namen als Zahlen oder Daten falsch erkennt. |
jeder | Ein Schlüsselwort, das Power Query anweist, die folgende Logik für jede einzelne Zeile in der Spalte zu wiederholen. |
Text.Richtig | Die spezifische Funktion, die angewendet wird. In diesem Fall konvertiert sie den Text in die Titel-Schreibweise. Wenn Sie alles in Großbuchstaben möchten, tauschen Sie dies gegen Text.Upper aus. |
Unterstrich: _ | Ein Platzhalter, der "den aktuellen Wert in dieser Zelle" darstellt. |
Im Gegensatz zu standardmäßigen Excel-Formeln ist die M-Sprache case-sensitiv. Wenn man table.transformcolumns anstelle von Table.TransformColumns schreibt, führt dies zu einem Fehler, daher sollte man genau auf die Groß- und Kleinschreibung achten!
Snippet 2: Unsichtbare Formatierungsfehler beseitigen
Aufräumen des Layouts einer Spalte
Das Ziel: Führende Leerzeichen und Zeilenumbrüche in der CustomerName-Spalte zu entfernen. Entscheidend ist, dass Sie Zeilenumbrüche durch ein Leerzeichen ersetzen, sodass "jOHN [Zeilenumbruch] sMITH" zu "John Smith" wird und nicht zu "JohnSmith."
Wie zuvor, klicken Sie auf fx, ersetzen Sie den vorhandenen Text durch den untenstehenden Code und drücken Sie Enter:
= Table.TransformColumns(FixCasing, {{"CustomerName", each Text.Clean(Text.Trim(Text.Replace(_, "#(lf)", " "))), type text}})
Denken Sie daran, dass das allererste Element innerhalb der Klammern immer dem Namen des vorherigen Schritts in Ihrer Liste der angewendeten Schritte entsprechen muss.
Benenne den Schritt CleanTrim um.
Hier ist, was jeder wichtige Teil macht:
- #(lf): Dies ist die M-Abkürzung für einen Zeilenumbruch (das unsichtbare Zeichen, das durch Alt+Enter in Excel erstellt wird - ein Zeilenwechsel).
- Text.Replace(_, "#(lf)", " "): Dies findet den Zeilenumbruch und ersetzt ihn durch ein Leerzeichen.
- Text.Trim: Entfernt überflüssige Leerzeichen am Anfang und Ende einer Zelle.
- Text.Clean: Entfernt verbleibende nicht druckbare Steuerzeichen (wie versteckte ASCII-Zeichen).
Snippet 3: Ziffern aus einem chaotischen String extrahieren
Zeichen mit Text.Select filtern
Das Ziel: Alles außer numerischen Ziffern aus der OrderID-Spalte zu entfernen.
Nach dem Klicken auf fx fügen Sie den folgenden Code ein und drücken Sie Enter:
= Table.TransformColumns(CleanTrim, {{"OrderID", each Text.Select(_, {"0".."9"}), type text}})
Benenne den Schritt ExtractNumbers um.
Lassen Sie uns die neuen Elemente ansehen, die wir hier eingeführt haben:
- Text.Select: Sagt M, nur die spezifischen Zeichen auszuwählen, die Sie auflisten, und alles andere zu verwerfen.
- {"0".."9"}: Dies definiert eine Liste erlaubter Zeichen. Die beiden Punkte (..) bedeuten „und alles dazwischen“, was bedeutet, dass alle Zahlen von 0 bis 9 extrahiert werden. Da Buchstaben und Symbole nicht in dieser Liste enthalten sind, werden sie vollständig verworfen.
Snippet 4: Spalten zusammenführen und "null"-Fehler behandeln
Erstellen eines sauberen ID-Regionscodes
Das Ziel: Die bereinigte OrderID und die Region zu einem einzigen Code zu kombinieren (wie 101N). Wenn die Region leer (null) ist, soll nur die ID angezeigt werden, ohne dass das Wort "null" erscheint, und anschließend sollen die ursprünglichen Spalten gelöscht werden, um die Tabelle schlank zu halten.
Klicken Sie auf fx, fügen Sie den folgenden Code ein und drücken Sie Enter:
= Table.SelectColumns(Table.AddColumn(ExtractNumbers, "ID", each [OrderID] & (if [Region] = null then "" else [Region])), {"ID", "CustomerName"})
Bennen Sie den Schritt MergeIDRegion um.
So funktioniert dieser Code:
- wenn [Region]=null dann "" sonst [Region]: Sagt Power Query, dass es sich die Region ansehen soll. Wenn es ein null sieht, behandelt es dies stattdessen als leeren String (""). Dies stellt sicher, dass die Kombination von 103 mit einem null-Wert zu 103 führt, nicht zu 103null.
- [OrderID] &...: Nimmt die bereinigte numerische ID aus Snippet 3, und das Und-Zeichen sagt Power Query, den Regionstext direkt daran anzuhängen.
- "ID": Definiert den Spaltennamen für Ihre neue kombinierte Spalte.
- {"ID", "CustomerName"}: Indem Sie am Ende der Anweisung nur diese beiden Spalten auflisten, sagen Sie Power Query, dass die ursprünglichen Spalten OrderID und Region verworfen werden sollen.
Sobald Sie erkennen, dass die M-Sprache nur eine Sammlung von leistungsstarken Formeln ist, verschwindet der Angstfaktor. Diese vier Ausschnitte sind Ihr erster Schritt hin zu Effizienz, Präzision und zur Befreiung von Klick-Müdigkeit durch die Verwendung von schlankem, handhabbarem Code. Jetzt, wo Sie begonnen haben, wie ein Entwickler zu denken, warum nicht auf Python in Excel umsteigen - ein Werkzeug, das noch mehr Leistung für Ihre täglichen Aufgaben bietet.