Daten bereinigen mit Power Query

Kurz gesagt: Spalte markieren, dann auf der Registerkarte Transformieren im Menü Format zum Beispiel Kürzen (Leerzeichen am Rand weg) oder Ersten Buchstaben im Wort großschreiben wählen. Spalte teilen zerlegt Text an einem Trennzeichen, Werte ersetzen tauscht Abkürzungen oder Tippfehler aus.

Probier es gleich aus

Die Namen in dieser Liste wurden von Hand eingetippt. Manche haben Leerzeichen am Anfang oder Ende, die Schreibweise ist bunt gemischt. In der Vorschau siehst du die Leerzeichen, weil die Zellen sie genau so anzeigen, wie sie gespeichert sind.

Aufgabe Die Namen wurden von Hand erfasst: Leerzeichen am Anfang und Ende, mal klein, mal groß. Bereinige die Spalte Name, damit jeder Name ohne Rand-Leerzeichen und mit großen Anfangsbuchstaben dasteht.

	A	B
1	Name	Abteilung
2	anna schmidt	Vertrieb
3	BERND MEIER	Einkauf
4	carla weber	Vertrieb
5	dirk VOGEL	Personal
6	eva klein	Einkauf
7	Frank Lang	Vertrieb

Die Befehle im Menü Format

Befehl	Was er macht	M-Funktion
Kleinbuchstaben	alles klein: ANNA wird anna	Text.Lower
Großbuchstaben	alles groß	Text.Upper
Ersten Buchstaben im Wort großschreiben	erster Buchstabe je Wort groß, Rest klein	Text.Proper
Kürzen	Leerzeichen am Anfang und Ende entfernen	Text.Trim
Bereinigen	nicht druckbare Steuerzeichen entfernen, etwa Zeilenumbrüche aus Webformularen	Text.Clean

Wichtig für alle, die GLÄTTEN aus Excel kennen: Kürzen entfernt nur Leerzeichen am Rand. Doppelte Leerzeichen mitten im Text bleiben stehen. Laut Microsoft-Dokumentation entfernt Text.Trim nur führende und nachfolgende Leerzeichen. Für doppelte Leerzeichen innen ersetzt du mit Werte ersetzen zwei Leerzeichen durch eines.

Die Befehle findest du auf zwei Registerkarten: Auf Transformieren ändern sie die markierte Spalte selbst, auf Spalte hinzufügen legen sie eine neue Spalte mit dem Ergebnis an und lassen das Original stehen.

Spalte nach Trennzeichen teilen

Steht „Schmidt, Anna“ in einer Zelle, brauchst du für Serienbriefe oder Sortierungen zwei Spalten. In Power Query geht das so:

1. Spalte markieren.
2. Auf der Registerkarte Start Spalte teilen wählen, dann Nach Trennzeichen.
3. Trennzeichen eingeben, hier Komma und Leerzeichen, und festlegen, ob bei jedem Vorkommen oder nur beim ersten geteilt wird.

Die neuen Spalten heißen zunächst Mitarbeiter.1 und Mitarbeiter.2. Per Doppelklick auf die Überschrift benennst du sie um.

Werte ersetzen

Werte ersetzen tauscht einen Text in allen Zeilen der markierten Spalte. Im Dialog gibst du den zu suchenden Wert und den Ersatz ein. Ohne weitere Option ersetzt Power Query den Text auch, wenn er nur ein Teil der Zelle ist. Mit Gesamten Zellinhalt abgleichen nur dann, wenn die ganze Zelle genau so lautet. In der Formelleiste siehst du den Unterschied: Replacer.ReplaceText für Teile, Replacer.ReplaceValue für ganze Werte.

Noch eine Übung: Namen teilen und Abkürzungen ersetzen

Aufgabe Teile die Spalte Mitarbeiter in Nachname und Vorname (Trennzeichen: Komma und Leerzeichen) und ersetze in Abteilung die Abkürzung „Vertr.“ durch „Vertrieb“.

	A	B
1	Mitarbeiter	Abteilung
2	Schmidt, Anna	Vertr.
3	Meier, Bernd	Einkauf
4	Weber, Carla	Vertr.
5	Vogel, Dirk	Personal
6	Klein, Eva	Vertr.
7	Lang, Frank	Einkauf

Diese Übung gehört zu Premium.

Mit Premium öffnest du alle Übungen jeder Lektion, übst unbegrenzt mit neuen Aufgaben und kannst den Abschlusstest mit Zertifikat machen.

Premium ansehen Schon Premium? Anmelden

Die erste Übung dieser Lektion ist kostenlos.

Der M-Code hinter den Schritten

Nach der zweiten Übung stehen unter Angewendete Schritte „Spalte nach Trennzeichen teilen“ und „Ersetzter Wert“. In der Formelleiste siehst du dazu:

= Table.SplitColumn(Quelle, "Mitarbeiter", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"Mitarbeiter.1", "Mitarbeiter.2"})
= Table.ReplaceValue(#"Spalte nach Trennzeichen teilen","Vertr.","Vertrieb",Replacer.ReplaceText,{"Abteilung"})

Jeder Schritt bekommt das Ergebnis des vorherigen als erstes Argument. Daran erkennst du, warum ein gelöschter Schritt weiter unten zu einem Fehler führen kann: Der nächste Schritt verweist dann auf eine Spalte oder einen Schrittnamen, den es nicht mehr gibt. Die Namen der neuen Spalten stehen fest in der Formel. Bringt ein späterer Export einmal drei Teile statt zwei, landet der dritte nicht automatisch in einer neuen Spalte.

Typische Fehler beim Bereinigen

Nach dem Teilen beginnt jeder Vorname mit einem Leerzeichen

Das Trennzeichen war nur das Komma. Entweder teilst du mit Komma und Leerzeichen, oder du hängst einen Schritt Kürzen für die zweite Spalte an.

Ersetzen trifft zu viel

Wer „Vertr.“ ersetzt, verändert auch „Vertr. Nord“. Meist ist das gewollt. Wenn nicht, aktiviere Gesamten Zellinhalt abgleichen.

Groß- und Kleinschreibung beim Suchen

Power Query unterscheidet beim Ersetzen und Filtern zwischen „berlin“ und „Berlin“. Gleich die Schreibweise deshalb an, bevor du filterst oder Duplikate entfernst.

Die Befehle auf Englisch

Deutsch	Englisch
Format: Kürzen, Bereinigen	Format: Trim, Clean
Kleinbuchstaben, Großbuchstaben	lowercase, UPPERCASE
Ersten Buchstaben im Wort großschreiben	Capitalize Each Word
Spalte teilen: Nach Trennzeichen	Split Column: By Delimiter
Werte ersetzen	Replace Values

In welchen Excel-Versionen geht das?

Alle Befehle dieser Seite gibt es im Power Query-Editor von Excel 2016 und neuer für Windows sowie in Microsoft 365. Auf dem Mac brauchst du Microsoft 365. Details stehen auf der Einstiegsseite zu Power Query.

Häufige Fragen