Heute möchte ich Dir erneut eine Datentransformation aus der Praxis zeigen, die ich vor kurzem bei einem Kundenprojekt einsetzt habe. Mit Power Query war die Aufgabenstellung schnell umsetzbar und ich möchte Dir daher heute zeigen, wie man horizontale Datenstrukturen ins Datensatzformat überführen kann.

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

Die Problematik

Innerhalb des Kundenprojektes ergab sich die Aufgabenstellung, eine Tabelle zu transformieren, die paarweise eine Spalte für die Kostenartennummer und eine weitere Spalte für die Kostenartenbezeichnung beinhaltete. Von diesen Spaltenpaaren konnte es unbegrenzt viele geben und die Anzahl sollte flexibel durch den Kunden bestimmbar sein. Ich konnte mich also nicht auf einen festen Aufbau verlassen. Die gewünschte Transformation sah vor, aus dem horizontalen Aufbau eine vertikale Datensatzstruktur herzustellen. Die folgende Abbildung visualisiert die Ausgangssituation und den Zielzustand.

Um eine entsprechende Lösung zu erzeugen, ging es zunächst darum Prämissen zu definieren, auf denen ich meine Lösung aufbauen konnte.

Prämissen

Wie bereits erwähnt, konnte ich mich nicht darauf verlassen, dass die vor mir liegende Tabelle die Anzahl an Spalten behalten würde, die diese gerade hatte. Jedoch waren folgende Sachverhalte fix:

• Dass die Spalten immer paarweise auftreten würden (Kostenartennummer und Kostenartenbezeichnung),
• die Reihenfolge immer eingehalten würde (erst Kostenartennummer, dann Kostenartenbezeichnung) und
• dass in der zweiten Spalte des entsprechenden Spaltenpaares immer die Kostenartengruppenbezeichnung im Spaltenkopf enthalten sein würde.

Mit diesen Voraussetzungen im Hinterkopf, konnte  ich mich an folgende Lösung heranarbeiten.

Die Lösung

Damit ich die bestehende Struktur in das gewünschte Format überführen kann, möchte ich zunächst in einem Zwischenschritt eine Struktur erzeugen, in welcher jeweils die Spaltenbezeichnungen der Kostenartennummern und der Kostenartenbezeichnungen untereinander stehen:

Wie ich dahin komme, zeige ich Dir jetzt.

Das Zwischenziel

Basierend auf der importierten Datenbasis, hole ich mir über Table.ColumnNames() zunächst alle Spaltenbezeichnungen in eine Liste, die ich im darauffolgenden Schritt sofort mittels Table.FromList() in eine Tabelle umwandle. Das Ergebnis sieht dann wie folgt aus:

Zu dieser Tabelle füge ich nun wie folgt eine nullbasierte Indexspalte als Hilfsspalte ein:

Basierend auf dieser Indexspalte (diese muss dafür markiert sein) erzeuge ich nun eine Modulo-Spalte, die mir für weitere Berechnungen hilfreich sein wird. In die aufkommende Inputbox der Modulo-Funktion trage ich den Wert 2 ein:

Die Modulo-Funktion ist Dir vielleicht noch aus dem Mathematikunterricht bekannt. Falls nicht, hier kurz die Funktionsweise: Die Funktion nimmt einen Parameter (in unserem Falle den Wert aus der Spalte Index) und teilt diesen durch den Wert, den ich der Funktion in die Inputbox übergeben habe (in meinem Fall der Wert 2). Zurückgegeben wird jedoch nicht der Quotient dieser ganzzahligen Division, sondern der Rest, der sich nach der ganzzahligen Division ergibt. Zu kompliziert?! Am Beispiel wird es deutlicher:

• Index 0 –> 0/2 = 0 Rest 0
• Index 1 –> 1/2 = 0 Rest 1
• Index 2 –> 2/2 = 1 Rest 0
• Index 3 –> 3/2 = 1 Rest 1
• usw…

Sinn der Sache ist es, hier die zusammengehörigen Zeilen zu identifizieren, denn ich möchte ja alle Kostenartennummern untereinander bekommen und in einer weiteren Spalte alle Kostenartenbezeichnungen. Gleich wird alles klarer, Du wirst sehen.

Als nächstes füge ich eine neue Spalte hinzu und verwende dafür die folgende Formel: if [Modulo] = 0 then [Column1] else null. Achte hierbei in der else-Bedingung darauf, dass dort wirklich null steht und nicht etwa „“. Dies ist für das Ausfüllen nach unten nötig, das ich gleich in einem weiteren Schritt nutzen werde. Das Ergebnis sieht dann wie folgt aus:

Überall dort, wo die Modulo-Spalte eine Null beinhaltet, befindet sich in Spalte Column1 die Bezeichnung der Kostenartennummer. Die NULL-Werte in der neuen Spalte, fülle ich mit den darüberliegenden Werten auf. Logischerweise befindet sich in jeder Zeile, in welcher die Modulo-Spalte eine 1 beinhaltet, in Spalte Column1 die Kostenartenbezeichnung. Diese lasse ich mir über die folgende Formel in einer neuen Spalte ausgeben: if [Modulo] = 1 then [Column1] else null.

In den Zeilen 2 und 4 habe ich nun die gewünschte Konstellation: Kostenartennummer und Kostenartenbezeichnung in einer Zeile. Ich filtere nun die Spalte Nummer auf nicht NULL und entferne anschließend alle Spalten außer Nummer und Bezeichnung, um den gewünschten Aufbau zu erzielen. Damit ist das Zwischenziel erreicht:

Die aktuell vorliegende Tabelle beinhaltet leider noch nicht die Daten, die mich interessieren, aber sie bildet einen guten Zwischenstand, den ich wie folgt weiterverarbeiten kann.

Weiteres Vorgehen

Als nächstes erzeuge ich eine neue Spalte, in welcher ich mir zeilenweise über die Table.SelectColumns()-Funktion die jeweilige Kostenartennummern-Spalte und Kostenartenbezeichnungs-Spalte aus der Datenbasis (Schritt Quelle) selektiere.

Die Datenvorschau zu betrachten, erleichtert das Verständnis:

An dieser Stelle angekommen, könnte man einfach auf den Expandieren-Pfeil (der Doppelpfeil, oben rechts in der Ecke) klicken und man hätte die gewünschte Struktur, oder?! Leider nicht ganz, denn die beiden Tabellen in Spalte Tabellen mit verschiedenen Ueberschriften haben, wie der Name schon vermuten läßt, verschiedene Überschriften. Zu welchen Problemen dies führt, kannst Du in meinem folgenden Artikel nachlesen, oder Dir gleich meine ganze Serie über Anfügeprozesse in Power Query ansehen.

Damit die beiden Tabellen also korrekt aneinander angefügt werden können, müssen diese dieselben Spaltenüberschriften haben. Dies bewerkstellige ich wie folgt:

Ich erzeuge eine neue Spalte, in welcher ich die Funktion Table.RenameColumns() nutze. Diese Funktion ändert bei einer bestehenden Tabelle (in meinem Beispiel die Tabelle „Tabelle mit verschiedenen Ueberschriften„) die Spaltenbezeichnung. Jetzt wird es gedanklich kurz knifflig… Ich möchte, dass …

• Die Spalte Reisekosten-Nummer in der Tabelle Tabellen mit verschiedenen Ueberschriften (in Zeile 1) in 1 umbenannt wird und
• die Spalte Reisekosten in Tabelle Tabellen mit verschiedenen Ueberschriften (in Zeile 1) in 2 umbenannt wird

Das gleiche gilt für die Tabelle Tabellen mit verschiedenen Ueberschriften in Zeile 2. Hier möchte ich, dass …

• die Spalte B-Nummer in 1 und
• die Spalte Bonus in 2 umbenannt wird.

Ob die Spalten nun 1 und 2 genannt werden ist vollkommen egal, Hauptsache sie sind identisch benannt. Doch wie gebe ich der Funktion Table.RenameColumns() nun im zweiten Parameter mit, wie die umzubenennenden Spalten heißen? Dies will die Funktion nämlich ganz genau wissen. Ganz einfach: Diese Information ist ja zeilenweise in den Spalten Nummer und Bezeichnung vermerkt, auf die ich in meiner Formel Bezug nehme. Indem ich also innerhalb der Table.RenameColumns()-Funktion {[Nummer], 1} schreibe, weiß die Funktion beispielsweise für Zeile 1, dass die Spalte Reisekosten-Nummer in 1 umbenannt werden soll, usw.  Damit habe ich alles, was ich mir gewünscht habe.

Nun lösche ich alle Spalte der bestehenden Tabelle, mit Ausnahme der Spalten Bezeichnung und Tabelle mit homogenen Ueberschriften:

In der Spalte Bezeichnung steht nun die Kostenartengruppenbezeichnung und in der zweiten Spalte befindet sich zeilenweise eine Tabelle mit den Spalten 1 und 2, in denen (in diesem Beispiel) Reisekosten und Bonus enthalten sind. Mit einem Klick auf den Expandieren-Pfeil in der oberen rechten Ecke erhalte ich folgendes Ergebnis:

An dieser Stelle ist die wesentliche Arbeit getan. Es fehlen noch zwei inhaltliche Schritte:

• Die Spaltenbezeichnungen anzupassen (Bezeichnung → Kostenartengruppe, 1 → Kostenartennummer, 2 → Kostenartenbezeichnung) und
• die Zeilen auszufiltern, bei denen in Spalte 1 NULL-Werte enthalten sind.

Danach ist das gewünschte Ergebnis erzielt und dies auch dann, wenn die Datenbasis (unter Einhaltung der oben genannten Prämissen) erweitert wird:

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