Bedingtes Ersetzen von Werten mit PowerQuery / M

Oft steht man vor der Situation, dass man Werte in einer Spalte durch andere Werte ersetzen möchte.

So lange man Werte 1:1 ersetzen will, ist das auch sehr einfach. Haben wir beispielsweise eine Spalte mit Automarken und wir wollen überall „VW“ durch „Volkswagen“ ersetzen, so geht das am besten im PowerQuery Editor, den wir über „Daten transformieren“ aufrufen. Dann klickt man mit der rechten Maustaste auf die Spaltenüberschrift und wählt im Kontextmenü „Werte ersetzen“ aus. Dann gibt man im sich öffnenden Dialog den zu suchenden Wert (VW) ein und den Wert für „Ersetzen durch“ (Vokswagen). Fertig.

Etwas komplexer ist es, wenn man den Wert abhängig vom Wert einer anderen Spalte ersetzen möchte. Hier gibt es auch (bisher) keinen Dialog im PowerQuery-Editor, der einem hier hilft. Man kann sich natürlich behelfen, indem man eine Hilfsspalte anlegt, Werte mit DAX transformiert und die Hilfsspalte wieder löscht, aber zum einen kostet das Ressourcen (Speicher und Rechenzeit) und zum anderen ist es auch nicht sehr elegant.

Hier möchte ich Euch nun zeigen, wie ich diese Aufgabe mit Hilfe einer kleinen Formel in M, der Sprache von PowerQuery umsetze.

Die Ausgangslage

Ein kleines Beispiel soll helfen.
Nehmen wir an, wir haben eine Datentabelle mit den Spalten Datum, Abteilung, Bereich und Kostenstelle.

Jede Abteilung hat eine eindeutige Kostenstelle sowie einen Bereich, dem sie angehört. Es gibt hier nur zwei Bereiche: Verwaltung und Fertigung. Die Verwaltung besteht hier aus den Abteilungen Einkauf und Verkauf, alle anderen Abteilungen gehören zum Bereich Fertigung.
Die Verwaltungs-Kostenstellen beginnen mit 1, die der Fertigung mit 2.

Die Anforderung ist nun, dass im Bereich Verwaltung nicht 1001 oder 1002 angezeigt werden soll, sondern eine „übergeordnete“ Kostenstelle 1000, egal, ob es sich um die Abteilung Einkauf oder Verkauf handelt.
Wir müssen also prüfen, ob die Kostenstelle zum Bereich Verwaltung gehört oder nicht. Falls ja, soll die Kostenstelle 1000 sein. Oder anders ausgedrückt: wir wollen den Wert der Spalte Kostenstelle ggf. ändern in Abhängigkeit vom Wert der Spalte Bereich. (Ich behaupte nicht, dass das Beispiel sinnvoll ist. Aber auch das hat man in der Praxis ja hin und wieder, dass die an einen angetragenen Anforderungen nicht unbedingt Sinn ergeben 😉 ).

Zunächst ein kleiner Trick

Zunächst wende ich einen kleinen Trick an, um einen in M formulierten Ausdruck zu erzeugen, der den Job erledigt. So haben wir zunächst eine Vorlage, die wir dann noch anpassen müssen.

Dafür klicke ich mit der rechten Maustaste auf die Spalte „Kostenstelle“ und wähle „Werte ersetzen“. Dann gebe ich für den zu suchenden Wert und für den Wert, durch den ersetzt werden soll, Nonsens-Werte ein, die nicht in meinen Daten vorkommen. Ich habe hier gesucht nach dem Wert „888“ und ersetze mit „999“. Die tatsächlichen Werte spielen hier aber keine Rolle, sie sollten nur nicht wirklich vorkommen.

Auf diese Weise hat mir PowerQuery folgende Formel erzeugt:

= Table.ReplaceValue(#"Geänderter Typ",
888,
999,
Replacer.ReplaceValue,{"Kostenstelle"})

Der erste Parameter ist die Referenz auf den vorangegangenen Schritt.

Der zweite Parameter ist der Wert, der ersetzt werden soll (hier 888)

Der dritte Parameter ist der Wert, durch den ersetzt werden soll (hier 999)

Der vierte Parameter ist dann die eigentlich Anweisung „ersetze den Text in der Spalte Kostenstelle“.

Anpassen der Formel

Die Formel passe ich nun so an, dass sie letztlich so aussieht:

= Table.ReplaceValue(#"Geänderter Typ",
each [Kostenstelle],
each if [Bereich] = "Verwaltung" then "1000" else [Kostenstelle],
Replacer.ReplaceValue,{"Kostenstelle"})

Erläuterungen:

Der erste Parameter bleibt unverändert.

Den zweiten Parameter (888) ersetzen wir durch „each [Kostenstelle]“, was zunächst bedeutet „ersetze jede Zeile in der Spalte Kostenstelle“.

Der eigentliche Zaubertrick passiert beim dritten Parameter:

each if [Bereich] = „Verwaltung“ then „1000“ else [Kostenstelle]

Sprich: für jede Zeile, Wenn der Wert in der Spalte Bereich = „Verwaltung“ ist, dann ersetze den Wert (in der Spalte Kostenstelle) durch „1000“, sonst (else) belasse den Wert so, wie er in der Spalte Kostenstelle schon steht.

Den vierten Parameter lassen wir wieder unverändert – dies ist die Anweisung, dass die bedingte Ersetzung in der Spalte Kostenstelle vorgenommen werden soll.

Ergebnis

Wenn wir diese Formel nun anwenden, erhalten wir folgendes Ergebnis:

Das ist es!

Habt Ihr womöglich noch eine Idee, wie man es noch schneller und eleganter machen kann?
Schreibt mir das gerne in die Kommentare.