Office-Blog

Fehlende Werte – sog. null-Werte – sind oft der Grund, dass Berechnungen gar nicht oder nicht korrekt erfolgen können. In meinem Blogbeitrag Power Query: Falsche Ergebnisse bei leeren Zellen vermeiden habe ich erklärt, wie beim Addieren und Subtrahieren von Zahlen sichergestellt wird, dass auch Zellen mit null korrekt berechnet werden – in dem Fall mit Hilfe der Funktion List.Sum.
Im heutigen Blogbeitrag zeige ich, wie mit Hilfe des COALESCE-Operators ?? störende null-Werte auf einfache Art behandelt werden. Die vielfältige Verwendbarkeit des COALESCE-Operators demonstriere ich anhand von drei Beispielen: 1) Ermitteln des Saldos von Einnahmen und Ausgaben, 2) Berechnen eines rabattierten Betrags und 3) Auswerten von Messreihen.

Was ist der COALESCE-Operator in Power Query?

• Manche kennen vielleicht die COALESCE-Funktion aus SQL. In Power Query gibt es den COALESCE-Operator ??.
• Mit Hilfe von ?? können fehlende Werte – null-Werte – durch etwas anderes ersetzt werden.
• Der Operator wird dazu zwischen zwei Werten platziert.

Hier ein Beispiel: [Betrag] ?? 0.
Ist der Wert links von ?? null, wird der Wert rechts von ?? zurückgegeben – hier im Beispiel also 0.

Verhindern, dass eine Berechnung fehl läuft

Im Beispiel aus meinem eingangs erwähnten Blogbeitrag Falsche Ergebnisse bei leeren Zellen vermeiden führt eine Addition oder Subtraktion mit einem null-Wert zu einem Gesamtergebnis von null.

Ist einer der Werte null, so ist das Gesamtergebnis automatisch null

Um eine aufwendige IF-Anweisung zu umgehen, setze ich den COALESCE-Operator wie folgt ein.

In der oben gezeigten Tabelle mit den Spalten Einnahme und Ausgabe gehe ich zur Berechnung der Spalte Saldo wie folgt vor:

• Ich wähle Spalte hinzufügen > Benutzerdefinierte Spalte: Saldo.
• Jeder angesprochenen Spalte, die möglicherweise einen null-Wert enthält, wird ??0 angefügt:
  =[Einnahme]??0 – [Ausgabe]??0

Mögliche null-Werte werden durch den COALESCE-Operator ?? mit 0 belegt

Fehlende Werte durch einen Standardwert ersetzen

Manchmal kommt es vor, dass ein Wert nur eingetragen wird, wenn er vom Standard abweicht. So auch hier im zweiten Beispiel, einer Rabattrechnung.

• Standardmäßig wird ein Rabatt von 3 % gewährt.
• Falls jedoch ein anderer Rabatt vereinbart ist, wird der abweichende Wert hinterlegt.

Ein nicht angegebener Rabattwert müsste mit einer IF-Anweisung abgefangen werden

Auch diese Berechnung lässt sich ohne IF-Anweisung einfach lösen:

• Ich wähle Spalte hinzufügen > Benutzerdefinierte Spalte: Rabattierter Betrag.
• Der Spalte Rabattwert, die möglicherweise nicht eingetragene Rabatte beinhaltet, füge ich ??0.03 an:
  =[Betrag] – [Betrag]*([Rabattwert]??0.03)

HINWEIS: Da auch beim Arbeiten mit dem COALESCE-Operator Punkt-Rechnung vor Strich-Rechnung gilt, muss [Rabattwert]??0.03 in Klammern gesetzt werden.

Fehlende Werte durch einen Standardwert ersetzen mittels COALESCE-Operator

Den letzten relevanten Wert trotz leerer Zellen ermitteln – ohne mühsame Mehrfachverschachtelung

Im dritten Beispiel geht es um die Auswertung von Messreihen. Hier erweist sich der COALESCE-Operator beim Ermitteln eines letzten relevanten Wertes ungleich null als besonders nützlich. Die Alternative wäre eine mehrfach geschachtelte IF-Anweisung. Doch die braucht es nicht dank COALESCE-Operator.

Mit COALESCE den jeweils letzten Messwert herausfinden

• Ich wähle Spalte hinzufügen > Benutzerdefinierte Spalte: Letzter Messwert.
• Nun liste ich die Messwertspalten von rechts nach links auf, jeweils mit dem ??-Operator dazwischen:
  =[Messwert4] ?? [Messwert3] ?? [Messwert2] ?? [Messwert1]
• Die Logik: Falls Messwert4 gleich null ist, wird Messwert3 genommen; falls Messwert3 gleich null ist, dann Messwert2 und so fort.

Der letzte Messwert wird verwendet, wenn nicht null; sonst der vorletzte usw.

FAZIT

• Beim Ausführen von Berechnungen muss häufig ein Alternativwert anstelle von null angegeben werden. Dies lässt sich mit dem COALESCE-Operator einfach lösen.
• Aufwendige IF-Anweisungen zum Prüfen auf null-Werte sind nicht mehr erforderlich.
• Der Code mit dem COALESCE-Operator ist kürzer und besser lesbar.

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.

TIPP: Wer hier im Blog alle Rezepte zu Power Query finden möchte, gibt einfach oben rechts in das Suchfeld Power-Query-Rezept ein.