Power Query: Falsche Ergebnisse bei leeren Zellen vermeiden
Neulich bei einem bekannten Getränkehersteller: Im Kurs überrascht mich jemand mit der Frage, warum Power Query nicht fehlerfrei subtrahieren könne. Allerdings stellte sich schnell heraus, was die tatsächliche Ursache für falsche Ergebnisse war: Zellen ohne Inhalt, die in Power Query mit null angezeigt werden.
Was ist an null so besonders? Wird beispielsweise von einem Ausgangswert eine null-Zelle subtrahiert, gibt Power Query null zurück statt des Ausgangswertes. Das führt zu Fehlern, wenn der Rückgabewert null in weiteren Berechnungen verarbeitet wird. Mit den folgenden Schritten lassen sich solche Fehler vermeiden.
Bei null in einer Zelle rechnen die Operatoren Plus (+), Minus (-), Mal (*) oder Geteilt durch (/) nicht, sondern geben null zurück
Das Problem beim Subtrahieren von leeren Zellen
Sollen die Werte einer Spalte von einer anderen subtrahiert werden, liegt es nahe, dies
a) über eine benutzerdefinierte Spalte und das Verwenden des Minus-Zeichens oder
b) über die Schnellvariante Start > Neue Spalte > Standard > Subtrahieren zu lösen.
Links: Subtrahieren mit benutzerdefinierter Spalte, rechts: Subtrahieren über die Schnellvariante
Gut zu wissen: Was bedeutet »null« in Power Query?
Ein null in einer Zelle zeigt an, dass die Zelle komplett leer ist. Die Zahl 0 hingegen ist ein tatsächlicher Wert, der zum Berechnen genutzt werden kann.
Bei den oben gezeigten zwei Varianten wird zur Subtraktion das Minus-Zeichen verwendet. Die Operatoren +, -, *, / rechnen in Power Query jedoch nur mit tatsächlichen Werten. Steht hingegen null in einer Zelle, geben sie als Gesamtergebnis null zurück, ungeachtet der anderen Zellen mit tatsächlichen Werten. Dieses Verhalten ist anders als in Excel!
Excel hat dieses Problem nicht, denn leere Zellen werden beim Subtrahieren ignoriert und wie 0-Werte behandelt
Excel hat dieses Problem nicht, denn leere Zellen werden beim Subtrahieren ignoriert und wie 0-Werte behandelt. Eine mögliche Lösung für dieses Problem wäre, in allen an der Berechnung beteiligten Spalten null-Zellen mit dem Wert 0 zu ersetzen. Dieser Schritt würde allerdings immer ausgeführt, auch wenn gar es gar keine null-Zellen gibt.
Die Lösung: Mit der Funktion List.Sum zu korrekten Ergebnissen
Der Ersetzungsschritt von null-Zellen mit dem Wert 0 lässt sich mit der M-Funktion List.Sum vermeiden. Sie gibt die Summe der Werte ungleich null in einer Liste zurück. Sie liefert nur dann null, wenn die Liste keine Werte ungleich null enthält.
So gehe ich vor, um die Spalten mit List.Sum zu subtrahieren:
- Ich markiere die Spalten Einnahme und Ausgabe [1], und zwar in der genannten Reihenfolge.
- Per Rechtsklick im Spaltenkopf der Spalte Ausgabe wähle ich Summe [2].
- Um nicht zu addieren, sondern zu subtrahieren, füge ich in der Bearbeitungsleiste vor dem Feld Ausgabe ein Minus-Zeichen hinzu [3].
Per Rechtsklick die Funktion List.Sum auswählen und oben in der Bearbeitungsleiste das Minus-Zeichen ergänzen
- Um einen Umbenennungsschritt einzusparen, ändere ich in der Bearbeitungsleiste auch gleich noch den Namen der Spalte von Addition zu Saldo [4]. Das Ergebnis sieht perfekt aus.
Den Spaltennamen gleich in der Bearbeitungszeile ändern, um einen weiteren Umbenennungsschritt einzusparen
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.
Hildegard Hügemann
- Dipl.-Informatikerin
- Trainerin für Excel und Power BI
- Als erfahrene Datenanalystin Coach für BI-Projekte
- Anwendungsentwicklerin für BI-Tools
- Fachbuchautorin und Bloggerin zu Excel und Power BI
- Video-Trainerin zu Power BI für LinkedIn Learning
- Microsoft Certified Excel Expert
- Microsoft Certified Power BI Data Analyst Associate
Über den Autor