Power Query: Spaltenwerte flexibel und effizient verändern

Soll in Power Query der Inhalt einer Spalte verändert werden, sind dafür auf der Registerkarte Transformieren verschiedene Befehle verfügbar. Allerdings gibt es dort keine Option, die unterschiedliche Werte einer vorhandenen Spalte durch einen bestimmten Wert oder durch nichts ersetzt. Viele behelfen sich dann, indem sie zuerst die gewünschten Werte in einer zusätzlich Hilfsspalte generieren und anschließend die Originalspalte löschen.
Doch solche Bereinigungsprozesse lassen sich mit Hilfe der Funktion Table.TransformColumns einfach erledigen – ohne jede Hilfsspalte.

Szenario 1: Komplette Spalte leeren oder mit festem Text belegen, unabhängig von den bisherigen Werten

Angenommen, eine Liste enthält eine Spalte Status, deren Inhalte aus verschiedenen Vorsystemen stammen. Diese sind leider uneinheitlich gepflegt wie z. B. „offen“, „neu“, „in Bearbeitung“, „in Prüfung“ usw. und so nicht brauchbar. Die Spalte soll deshalb vollständig geleert oder mit „tbd.“ überschrieben werden, um den Status in einem späteren Verarbeitungsschritt neu zu belegen.

So geht’s …
Ich markiere die Spalte Status und wähle im Register Transformieren [1] einen typischen Transformationsschritt aus, beispielsweise unter Format [2] > Präfix hinzufügen [3]. Anschließend gebe ich beispielsweise den Präfix „X“ ein [4].

 

 

Auf diese Weise erhalte ich den nötigen M-Code zur Transformation der Spalte. In dieser Anweisung wird mit Hilfe des Wörtchens each für jede Zeile die danach folgende Anweisung ausgeführt. Mit dem Unterstrich _ wird auf den jeweiligen Wert zugegriffen und hier mit dem Buchstaben „X“ am Anfang verkettet.

 

Möchte ich die Spalte leeren oder mit einem bestimmten Text belegen, ersetze ich den Ausdruck „X“ & _ , der dem each folgt, mit dem Schlüsselwort null oder mit einem festen Text, beispielsweise „tbd.“ .

 

Tipp: Um den Schritt nachvollziehen zu können, benenne ich den Schritt Präfix hinzugefügt rechts unter Angewendete Schritte beispielsweise um in Status bereinigt.

Szenario 2: Nur bestimmte Werte in der Spalte ändern

In diesem Fall sollen in der Spalte Status nicht alle Werte geändert werden, sondern nur bestimmte. Beispielsweise soll nur der Wert „neu“ in „tbd.“ geändert werden.

So gehe ich vor …
Wie in Szenario 1 benutze ich den erstellten M-Code und ersetze den Ausdruck „X“ & _ durch eine if-Anweisung: each if _ = „neu“ then „tbd.“ else _. Diese prüft, ob der aktuelle Wert (dargestellt durch Unterstrich _) gleich „neu“ ist und ersetzt in diesem Fall den Text durch „tbd.“. Andernfalls wird der aktuelle Wert (_) beibehalten.

 

Tipp: Diese Anweisung kann natürlich beliebig erweitert werden, beispielsweise durch Hinzufügen einer or-Anweisung: each if _ = „neu“ or _ = „offen“ then „tbd.“ else _

Szenario 3: Nicht korrekte Werte ersetzen

Die Spalte Datum wurde nicht immer korrekt gepflegt. An manchen Stellen befinden sich Texte in der Spalte. Bevor der Datentyp für diese Spalte geändert wird und dies zu Fehlern führen würde, wird sie mit einer Art wenn-Fehler-Funktion geprüft (try … otherwise).

 

So gehe ich vor …
Wie in Szenario 1 benutze ich den erstellten M-Code. Diesmal ersetze ich den Spaltennamen von Status in Datum und den Ausdruck „X“ & _ durch each try Date.From(_) otherwise null. Mit Hilfe der try-Anweisung wird in diesem Fall geprüft, ob eine Umwandlung in ein Datum möglich ist. Falls nicht, wird der Eintrag geleert.

Zusätzlich kann mit der Transformationsanweisung auch gleich der passende Datentyp mitgegeben werden, in diesem Fall date.

Szenario 4: Gleich mehrere Spalten in einem Schritt bereinigen

Sollen auf mehreren Spalten Transformationen vorgenommen werden, so ist auch dies in einem Schritt möglich. Die Funktion Table.TransformColumns erlaubt eine Liste mit Transformationsschritten, die jeweils in geschweiften Klammern und kommagetrennt gelistet werden. So können beispielsweise sowohl die Spalte Status als auch die Spalte Datum in einem Rutsch bereinigt werden.

 

Alle Szenarien lassen sich mit wenigen Handgriffen in einem Schritt mit Hilfe der Funktion Table.TransformColumns umsetzen. Dabei wird der bestehende Spalteninhalt gezielt ersetzt, ohne jegliche Hilfsspalte.

Zusatz-Tipp: Natürlich kann ich auch jederzeit einen benutzerdefinierten Schritt einfügen und mit folgendem Code jeden beliebigen Ersetzungsprozess ausführen.

 

Interessiert an weiteren Tipps zu Power Query?
Wer sein Wissen zu Power Query systematisch auf- und ausbauen will, kann dazu einen meiner Online-Kurse nutzen. Auf dieser Seite ist das aktuelle Angebot zu finden.