Spaltensummen bei flexibler Tabellenstruktur

In den meisten Fällen kalkuliere ich Summen in Power Query zeilenbasiert. Doch neulich bekam ich die Aufgabe, diese über Spalten zu kalkulieren und die Lösung so aufzubauen, dass sich die Anzahl der Spalten verändern kann. In dieser Lösung stecken ein paar – meiner Meinung nach – interessante Ansätze und daher zeige ich Dir in diesem Beitrag, wie Du Spaltensummen bei flexibler Tabellenstruktur kalkulieren kannst.

Achja, bei diesem Beitrag handelt es sich um meinen 100. Blogbeitrag. Echt Wahsinn, wie viel da über die Zeit zusammenkommt 😛

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

Die Zielstellung

Das Ziel ist eine Datenbasis, die Du Dir exemplarisch wie die blaue Tabelle im Screenshot vorstellen kannst, in das Format der grünen Tabelle zu überführen und dabei die Summen aus Punkt (2) zu erzeugen.

Wenn Dich interessiert, wie Du eine zeilenbasierte Summe über eine dynamische Anzahl von Spalten erzeugst (1), dann kannst Du Dir in meinem Video zur Einführung in lists, records und tables in M, ab Zeitpunkt 42:02 ansehen, wie ich diese Problemstellung löse. Dies ist aber nicht Gegenstand des vorliegenden Beitrags.

Wie Du eine spaltenbasierte Summe über eine dynamische Anzahl von Spalten erzeugst (2), zeige ich Dir im aktuellen Beitrag. Das Ziel ist also eine Summe über alle Kostenstellen in Power Query zu berechnen, dabei jedoch zu berücksichtigen, dass sich die Anzahl an Spalten (in diesem Fall die Monate) verändern können.

Wann sind kalkulierte Summen sinnvoll?

Mit dieser Aufgabenstellung hat mich vor kurzem ein Kollege konfrontiert, der mit der Lösung ausschließlich in Excel, ohne die Verwendung von Pivottabellen arbeitet. In diesem Rahmen ist das Berechnen der Summen okay. Sofern ich eine solche Abfrage jedoch in einer Pivot auswerten möchte (oder im Falle von Power BI Desktop in einer beliebigen Visualisierung), übernimmt diese die Ermittlung von Zeilen- und Spaltensummen und eine in Power Query vorkalkulierte Summe, würde hier zu einem falschen Ergebnis führen.

Lösung

Ich unterteile die Gesamtlösung in thematische Kategorien und hoffe, dass die Lösung damit verständlicher wird.

Drei Listen als Vorbereitung

Zu Beginn der Lösung ist es sinnvoll, eine Liste zu erzeugen, die die Monatsspalten der Quelltabelle beinhaltet. Hierfür erstelle ich insgesamt drei Listen, wobei die ersten beiden dafür da sind, die dritte zu erzeugen.

Liste #1: Alle Spaltenbezeichnungen der Quelltabelle auslesen

Über die Funktion Table.ColumnNames() lasse ich mir sämtliche Spaltenbezeichnungen der Quelltabelle in Listen-Form zurückgeben.

Liste #2: Liste aller existierenden Kalendermonate

Aus der Quelltabelle sollen nur diejenigen Spalten berücksichtigt werden, die einen Kalendermonat abbilden. Daher erstelle ich hier (händisch in M) eine Liste aller existierenden Kalendermonate, um in der dritten Liste gegen diese abzugleichen.

Liste #3: Ermittlung aller Monatsspalten in der Quelltabelle

Über die Schnittmenge der eben erstellten beiden Listen ermittle ich die Monatsspalten in der Quelltabelle. Auf diese Weise kann ich sichergehen, dass meine Lösung auch dann weiterhin funktioniert, wenn in der Quelltabelle weitere Spalten hinzukommen, von denen ich zum jetzigen Zeitpunkt noch nichts weiß. Die Funktion List.Intersect() ermittelt hierbei diejenigen Elemente, die in beiden zuvor ermittelten Listen vorhanden sind. Dies können nur Spalten sein, die den Namen eines Monats tragen.

Somit konnte ich in einer dynamischen Liste die Namen der für die spätere Summenbildung relevanten Spalten speichern, um sie später weiter zu verwenden.

Warum nicht einfach Gruppieren?

Was ich bei der vorliegenden Problemstellung immer im Hinterkopf behalten muss, ist die Tatsache, dass sich die Anzahl der Monatsspalten verändern werden. Auf den ersten Blick liegt als Lösung wahrscheinlich nahe – wie im folgenden Screenshot gezeigt – eine Gruppierung über die Kostenstellen zu erzeugen und die Monatsspalten zu summieren…:

Die Problematik dieser Lösung bei sich ändernden Spalten ist, dass ich das im obigen Screenshot abgebildete M-Statement dynamisch aufbauen muss, d. h. es muss die sich ändernde Anzahl von Monatsspalten automatisch berücksichtigen. Dies ist durchaus machbar, aber nicht trivial. Hierauf gehe ich am Ende dieses Beitrags unter Anmerkungen noch einmal ein.

Mein Lösungsvorschlag für den vorliegenden Beitrag funktioniert weitgehend über die Nutzeroberfläche und nutzt die Funktionalität des Transponierens.

Der Trick: Die transponierte Tabelle

Transponieren meint in diesem Falle das Vertauschen von Zeilen und Spalten einer Tabelle. Dies ist eine Technik, die es selbst im „normalen“ Excel (also ohne Power Query) bereits seit Jahrzehnten gibt. Wie das Transponieren funktioniert und wofür es überhaupt gut ist, zeige ich Dir jetzt.

Vorbereitung auf das Transponieren

Zunächst bereite ich meine Tabelle auf das Transponieren vor, indem ich dafür sorge, dass meine Spaltenüberschriften als erste Zeile verwendet werden:

Nachdem sich die Spaltenbezeichnungen nun in der ersten Zeile der Tabelle befinden, kann ich die Tabelle transponieren, oder „vertauschen“, wie es die Schaltfläche in Power Query beschreibt:

Durch diese Transformation befinden sich die Spaltenbeschriftungen nun in den Zeilen der ersten Spalte und die Kostenstellen (A, B, C…) befinden sich in der ersten Zeile. Dieses Vertauschen von Zeilen und Spalten ist deswegen sinnvoll, weil ich nun die Summe eines Monats – so wie ich es in Power Query in 99% der Fälle tue – durch eine berechnete Spalte kalkulieren kann, die automatisch über alle vorhandenen Zeilen (also die sich ändernde Anzahl von Monaten) rechnet.

Eingrenzen auf zu summierende Spalten

Aufsummieren möchte ich hierbei alle Spalten, die nicht Column1 heißen, denn dort stehen nur Textwerte. Dies geschieht in zwei Schritten:

In Schritt 1 nutze ich erneut Table.ColumnName() um eine Auflistung aller Spaltenbezeichnungen zu erhalten. In Schritt 2 nehme ich die erste Spalte Column1 aus dieser Liste aus, weil dies die einzige Spalte ist, über die ich keine Summe bilden möchte.

Bevor ich jedoch die Summe bilde, muss ich noch dafür sorgen, dass auch nur Datensätze übrig bleiben, in denen in Spalte Column1 auch ein Monat steht. Ich filtere hier jedoch nicht einfach „Kostenstelle“ heraus, sondern gleiche mit meiner Liste der zuvor ermittelten Monatsspalten ab. Dies macht meine Lösung wieder robust gegen Änderungen in der Tabellenstruktur. Diese Technik habe ich in einem anderen Beitrag beschrieben und zeige daher hier nur kurz, wie ich dynamisch nach den Monaten filtere:

Dynamische Summenbildung

Nun kann ich über eine berechnete Spalte alle Spalten außer Spalte Column1 summieren:

Wie bereits zu Beginn dieses Beitrags beschrieben, handelt es sich hierbei um eine Technik, die ich in meinem Video zur Einführung in lists, records und tables in M, ab Zeitpunkt 42:02 erläutere.

Zwischenergebnis

Ich reduziere die Tabelle auf die zwei relevanten Spalten Column1 (in der sich die Monatsnamen befinden) und Spalte Monatssummen in welcher ich gerade die Summen über die Monate kalkuliert habe:

Wichtig ist hierbei, dass ich die Spalten Column1 und Monatssummen selektiert (unter Verwendung der Strg-Taste + Mausklicks) und dann  „Andere Spalten entfernen“ gewählt habe. Damit bleibt diese Lösung auch bei sich ändernder Zahl von Kostenstellen flexibel.

Erneutes Transponieren in die ursprüngliche Struktur

Jetzt transponiere ich erneut und sorge so dafür, dass die Monate – die sich gegenwärtig in Zeilen befinden – wieder in Spalten dargestellt werden… Dies ist notwendig, damit ich das nun kalkulierte Gesamtergebnis je Monat am Ende unter der Quelltabelle anfügen kann, um die Gesamtsummen unter den Einzelwerten darzustellen. Das ganze geschieht in zwei Schritten:

• Schritt 1: Das erneute Transponieren befördert die Monate erneut in die Spalten
• Schritt 2: Die Monatsnamen werden zur Spaltenbeschriftung und in Zeile 1 befindet sich das kalkulierte Ergebnis je Monat.

Das finale Ergebnis: Tabellen zusammenfügen

Das Endergebnis ist beinahe erreicht. Bevor ich an die Quelltabelle die Tabelle anfüge, die die Summen beinhaltet, muss ich der Summen-Tabelle zunächst eine Spalte Kostenstellen mit dem Wert Summe hinzufügen. Dies ist notwendig, damit in meiner finalen Tabelle für die Summen auch in der ersten Spalte „Summe“ als Zeilenbeschriftung ausgewiesen wird.

Wenn Du dazu mehr wissen willst, habe ich hier eine Serie geschrieben, die sich im Detail mit Anfügeprozessen in Power Query beschäftigt.

Fast fertig. Nur die richtigen Datentypen für die Spalten fehlen noch. Den Datentyp für die Kostenstelle vergebe ich fix, aber die Datentypendeklaration für die Monatsspalten muss ich dynamisieren, denn meine Grundannahme für dieses Beispiel ist ja, dass die Anzahl von Monaten variieren kann. Diese Dynamisierung muss ich manuell im M-Code vornehmen:

Diese dynamische Bestimmung der Datentypen ist eine sehr charmante Sache, weil sie meine Lösung robust macht. Leider muss ich dies selbst in M schreiben und kann es mir nicht einfach zusammenklicken. Die Art und Weise dies zu tun, habe ich mir bei Imke abgeschaut und ich kann ihren Beitrag Dynamic & bulk type transformation in Power Query, Power BI and M dazu nur empfehlen. Damit ist meine Lösung vollständig 🙂

Anmerkungen

Meine Lösung soll Dich zum Nachdenken anregen und die Operation Transponieren mehr in Dein Bewußtsein rücken. Dennoch sei an dieser Stelle gesagt, dass dies natürlich sehr schnell sehr inperformant werden kann. Power Query operiert nicht sonderlich schnell über breite Tabellen. Hätte mein Beispiel also 1.000 Kostenstellen, wäre Transponieren keine gute Idee, weil meine transponierte Tabelle dann mehr als 1.000 Spalten hätte. In einem solchen Fall müßte ich mich an die bereits weiter oben beschriebene Lösung machen, die Gruppierungsfunktion zu dynamisieren. Diese ist jedoch nur mit reinem M-Code erzielbar ist… und dies ist eine andere Geschichte und die soll ein andermal erzählt werden 😉

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