An mich wurde vor kurzem eine Frage herangetragen, bei der ich mir sicher bin, dass dessen Lösung für viele Power Query-Nutzer einen relevanten Praxistipp darstellt. Daher möchte ich Dir in diesem Beitrag helfen, NULL-Werte in Tabellen mit variablen Spalten zu finden.

Als Abonnent meines Newsletters erhältst Du die Beispieldateien zu den Beiträgen dazu. Hier geht’s zum Abonnement des Newsletters!

Die Ausgangssituation

Gegeben ist die folgende Tabelle, die Kosten je Abteilung, Kostenart und Monat ausweist:

Die Vertikale

In der Vertikalen sind sowohl die Abteilungen vermerkt (fett gedruckt), als auch die Kostenarten je Abteilung. Sowohl die Kostenarten, als auch die Abteilungen können mit jeder neuen Veröffentlichung dieser Tabelle vollkommen anders aussehen.

Die Horizontale

In der Horizontalen sind unterschiedliche Formen von zeitlichen Angaben zu finden. In den meisten Fällen handelt es sich um Monate, jedoch können es auch Quartale („Q1 17“), Halbjahre, oder andere zeitliche Aggregate sein. Auch hier steckt viel Flexibilität drin und der Spaltenaufbau kann mit jeder neuen Veröffentlichung variieren.

Das Zielbild

Das Zielbild besteht darin, aus der Spalte Datum zwei Spalten Abteilung und Kostenart abzuleiten. Die folgende Abbildung visualisiert den Übergang von der Ausgangssituation zur Zielstellung.

Die Problemstellung

Ich hatte bereits erwähnt, dass sämtliche Kostenarten und Abteilungsbezeichnungen exemplarischer Natur sind und sich jederzeit ändern können. Daher kann ich für meine Lösung nicht auf die fixe Tabellenstruktur setzen. Was beim Auftreten einer Abteilungsbezeichnung in der Urpsrungstabelle besonders ist, ist die Tatsache, dass alle weiteren Zellen in der entsprechenden Zeile keine Werte (also NULL-Werte) ausweisen (in der vorherigen Abbildung rot markiert). Um eine Möglichkeit zu haben, die Abteilungsbezeichnungen zu identifieren, ist es sinnvoll diejenigen Zeilen zu identifizieren, die in allen Spalten außer der Spalte Datum (hier stehen ja die Kostenarten und Abteilungsbezeichnungen drin) NULL-Werte ausweisen. Wichtig hierbei ist es sich in Erinnerung zu rufen, dass auch die Spalten jederzeit ihren Aufbau ändern können. Meine Lösung muss also Spalten-flexibel sein.

Der Lösungsansatz

Die Idee ist, eine neue Spalte zu kalkulieren, die dynamisch prüft, ob alle existierenden Spalten, außer der Spalte Datum (in ihr befinden sich die Kostenarten und Abteilungsbezeichnungen) leer sind, also einen null-Wert ausweisen. Das Zwischenergebnis könnte demnach wie folgt aussehen:

Auf Basis dieser Prüfspalte, kann ich leicht die Spalten Abteilung und Kostenart erzeugen, doch wie erzeuge ich diese Prüfspalte?

Die Erstellung der Prüfspalte

Die Erstellung der Prüfspalte untergliedert sich in 3 inhaltlich voneinander trennbare Schritte:

1. Ermittlung der zu prüfenden Spalten
2. Aufbau der Prüffunktion
3. Evaluierung der erstellten Prüffunktion

Diese drei Schritte werde ich nun erläutern.

Ermittlung der zu prüfenden Spalten

Was ich weiß ist, dass die Spalte Datum die einzig fixe Spalte ist und, dass ich alle anderen Spalten auf NULL-Werte untersuchen möchte. Ich kann die zu prüfenden Spalten demnach wie folgt dynamisch per Formel ermitteln:

List.Difference(Table.ColumnNames(MeineTabelle), {"Datum"})

Diese Formel arbeitet wie folgt:

1. Table.ColumnNames(MeineTabelle) gibt eine Liste aller Spaltenbezeichnungen von Tabelle MeineTabelle zurück. MeineTabelle ist hierbei der Schritt in meinem Power Query Skript, der diejenige Tabelle beinhaltet, auf der ich die entsprechende Analyse betreiben will.
2. {"Datum"} ist eine Liste, die einzig und allein die Bezeichnung der fixen Spalte Datum beinhaltet.
3. List.Difference() ist eine Funktion, die den Unterschied zwischen zwei Listen als Liste zurückgibt. Dieser Funktion übergebe ich die Listen {"Datum"} (also die einzige Spalte, die nicht auf NULL-Werte geprüft werden soll) und Table.ColumnNames(MeineTabelle),  welche alle Spaltenbezeichnungen der Tabelle MeineTabelle beinhaltet. Als Ergebnis wird eine Liste zurückgegeben, die alle Spaltenbezeichnungen beinhaltet, außer Datum.

Auf diese Weise erhalte ich eine Liste der zu prüfenden Spaltenbezeichnungen, unabhängig davon, ob die Tabelle breiter oder schmaler wird. Jetzt, da ich weiß, welche Spalten zu prüfen sind, gilt es eine dynamische Formel zu entwerfen, die die Basis der Prüfung darstellt.

Aufbau der Prüffunktion

Für die Erstellung und Evaluierung der Prüffunktion, habe ich mich bei einem Artikel meiner guten Freundin Imke bedient. Zunächst konvertiere ich die Liste der zu prüfenden Spalten in eine Tabelle.

Diese Konvertierung ist notwendig, da ich ein paar berechnete Spalten hinzufügen möchte und berechnete Spalten nur zu Tabellen, nicht aber zu Listen hinzugefügt werden können. Nun erzeuge ich eine neue Spalte, die um den jeweiligen Spaltennamen eckige Klammern setzt und „= null“ ergänzt. Dies ist der erste Bestandteil des Formeltextes.

Nun kommt der zweite Schritt, der den Formeltext vervollständigt. Ich füge eine weitere berechnete Spalte hinzu und ermittle dessen Inhalt, wie in folgendem Screenshot dargestellt:

Die Funktion Text.Combine() benötigt als ersten Parameter eine Liste von Texten, die miteinander verknüpft werden sollen. Als zweiten, optionalen Parameter wird ein Text benötigt, der als Separator der zu verknüpfenden Texte dient. Ich möchte in diesem Beispiel erreichen, dass alle in Spalte FormelTeil1 befindlichen Einträge mit einander verknüpft werden und dabei durch den Text “ and „ getrennt werden. Der Funktion Text.Combine() die Spalte FormelTeil1 als ersten Parameter zu übergeben, ist nicht ausreichend, weil es sich hierbei nicht um eine Liste handeln würde, die von der Funktion jedoch verlangt wird. Ich wandle die Spalte in eine Liste um, indem ich nicht allein auf die Spalte verweise (mit [FormelTeil1]), sondern vor den Spaltennamen noch den Namen des vorangegangenen Schrittes setze: SpalteFormelTeil1[FormelTeil1]. Da der Schritt SpalteFormelTeil1 eine Tabelle zurückgibt, fungiert der Name des Schrittes als Tabellenname. Nutze ich die Syntax Tabelle[Spalte] interpretiert Power Query dies als Liste.

Auf diese Weise erhalte ich nun eine Spalte, die dieselbe Formel in jeder Zelle ausweist. Um auf eine dieser Formeln als Text zugreifen zu können, ergänze ich die in der Formelleiste befindliche Formel lediglich um den rot markierten Bestandteil:

Damit greife ich in der Spalte FormelKomplett auf den Inhalt der ersten Zelle zu. Wie Du siehst, ist Power Query beim Adressieren seiner Zeilen Null-basiert. Will ich also auf die erste Zelle zugreifen, so nutze ich die Syntax {0} und nicht {1}.

An dieser Stelle habe ich die Prüffunktion als Text in einem Schritt gespeichert. Nun gilt es, diese Formel auch zu evaluieren, d. h. auszuführen und mit dessen Ergebnis weiterzuarbeiten.

Evaluierung der erstellten Prüffunktion

Nachdem die Prüfformel als Text vorliegt, will ich diese nun für meine Prüfung nutzen. Ich füge eine benutzerdefinierte Spalte hinzu und nutze die Formel          Expression.Evaluate(FormelAlsText, [_=_]):

Dieser Formel übergebe ich zwei Parameter:

• FormelAlsText: FormelAlsText ist der Name des Schrittes, in welchem ich die dynamisch erzeugte Prüfformel als Text gespeichert habe. Diese möchte ich nun evaluieren lassen.
• [_=_]: Dies ist der zweite Parameter der Funktion Expression.Evaluate(), der die Environment der Funktion bestimmt. Die Environment ist ein fortgeschrittenes Konzept der Sprache M, das ich in der Beitragsserie The Environment concept in M for Power Query and Power BI Desktop behandle. An dieser Stelle soll es ausreichen zu wissen, dass über [_=_] der Bezug zwischen der in Expression.Evaluate() zu evaluierenden Formel und der jeweiligen Zeile der Tabelle, in welche ich gerade die Spalte IsNULL? einbaue, hergestellt wird. Ohne diesen Parameter kann die Formel nicht evaluiert werden.

Expression.Evaluate() nimmt nun diese beiden Parameter und führt die als Text übergebene Formel aus. Die Evaluierung dieser Formel führt zur Prüfspalte IsNULL?. Für den Fall, dass die untersuchten Spalten in der entsprechenden Zeile alle NULL sind, führt dies in Spalte IsNULL? zu TRUE, sonst zu FALSE. Auf Basis der TRUE- und FALSE-Werte kann ich nun relativ einfach Abteilungen und Kostenarten voneinander getrennt in separaten Spalten auszuweisen.

Die fertige Lösung

Der Grund für die Erzeugung der Prüfspalte, ist die Tatsache, dass ich die beiden Spalten Abteilung und Kostenart auf Basis eines Kriteriums aus der Spalte Datum ableiten muss. Mit einem Klick auf Spalten hinzufügen → Bedingte Spalte kann ich die Kalkulation des folgenden Screenshots nachbauen:

Für den Fall, dass der Wert in Spalte IsNULL? TRUE ist, handelt es sich um eine Abteilung, die ich dann in die neue Spalte übernehmen möchte. Wichtig ist hierbei, dass im Sonst-Fall (im Screenshot Anderfalls benannt), ein NULL-Wert zurückgebeben wird. Auf diese Weise kann man über Transformieren → Ausfüllen → nach unten die NULL-Werte durch die darüberliegende Abteilungsbezeichnung überschreiben, so dass in jeder Zeile dieser Spalte die korrekte Abteilung vermerkt ist. Im Anschluss kann ich auf eine sehr ähnliche Weise die Kostenart in eine separate Spalte schreiben:

An dieser Stelle finde ich die gewünschte Kostenart vor, wenn der Wert in Spalte IsNULL? FALSE ist. Nachdem ich anschließend die NULL-Werte aus Spalte Kostenart ausgefiltert habe und die Sortierung der Spalten nach meinem Bedarf angepasst habe, kann ich nun auch die Prüfspalte entfernen und das finale Ergebnis sieht wie folgt aus:

Bis zum nächsten Mal und denk dran: Sharing is caring. Wenn Dir der Beitrag gefallen hat, dann teile ihn gerne. Falls Du Anmerkungen hast, schreibe gerne einen Kommentar, oder schicke mir eine Mail an lars@ssbi-blog.de 

Viele Grüße aus Hamburg,

Lars

Ich schreibe meine Beiträge für Dich, den Leser. Bitte schenke mir eine Minute Deiner Zeit und bewerte die folgenden Kategorien, um mir zu helfen meine Beiträge so gut wie möglich zu schreiben. Danke 🙂