Office-Blog

Wer mit Daten arbeitet, stößt mitunter auf folgende Herausforderung: eine sich ändernde Anzahl von Spalten soll dynamisch verarbeitet werden. Beispielsweise kommen wöchentlich oder monatlich neue Spalten hinzu. Wie lässt sich da eine effiziente Abfrage finden, die ohne manuelle Anpassungen auskommt?
Power Query bietet auch dafür eine Lösung. In diesem Blogbeitrag zeige ich, wie sich Spalten gleichen Typs in Power Query automatisiert addieren lassen, und zwar unabhängig davon, wie viele Spalten es gerade sind.

Neu hinzukommende Kalenderwochen sollen automatisch in die Summe einbezogen werden

Die Aufgabe: Wöchentliche Werte dynamisch summieren

Die Abbildung oben zeigt, worum es geht:

• In eine Produktliste werden wöchentlich neue Verkaufszahlen eingetragen.
• Je Produkt soll in der Spalte Summe das Gesamtergebnis berechnet werden.
• Das Gesamtergebnis soll sich dynamisch anpassen, wenn neue (Wochen-)Spalten hinzukommen.

1) Die Summe der Verkaufszahlen für die bisherigen Kalenderwochen berechnen

Ich lade die Produktliste nach Power Query. Dort addiere ich die Werte der bisher vorliegenden Kalenderwochenspalten wie folgt:

• Über Daten > Daten abrufen > Aus Tabelle/Bereich lade ich die Tabelle nach Power Query und benenne die Abfrage um in Produkte.
• Ich markiere die Spalten KW01 bis KW04 [1] und wähle per Rechtsklick in einen der Spaltenköpfe Summe [2].
• Um einen Extraschritt einzusparen, ändere ich den automatisch erstellen Spaltennamen „Addition“ in der Bearbeitungsleiste um zu „Summe“ [3].

Mit List.Sum werden die aktuellen Kalenderwochenspalten summiert

2) Die Summenberechnung dynamisch machen: Immer alle Spalten, aber ohne »Produkt«

• Der eben erstellte List.Sum-Schritt ist noch nicht dynamisch, wenn neue Kalenderwochen hinzukommen.
• Anstelle der bisherigen Kalenderwochen müssen daher bei der Summenbildung alle Spalten angesprochen werden außer der Spalte Produkt.

So gehe ich vor:

• Um auf die Spalten zu jedem Produkt zugreifen zu können, erstelle ich aus den Daten zu jedem der Produkte eine Liste.
• Das erledige ich mit der Anweisung Record.ToList(_).

Mit Record.ToList wird eine Liste der Daten jeder Zeile erzeugt

• Die Spalte Summe weist nun Fehler auf, denn zu den Zahlen der Kalenderwochen wird auch der Name des Produkts addiert.
• Dies behebe ich, indem ich von der eben erzeugten Liste mit Hilfe der Funktion Record.RemoveFields die Spalte Produkt entferne.

Mit Record.RemoveFields bestimmte Spalten von der Summenbildung ausschließen

TIPP: Mit der Funktion Record.RemoveFields lassen sich auch mehr als eine Spalte ausschließen. Dazu werden alle betreffenden Spaltennamen kommagetrennt in die geschweiften Klammern gesetzt.

Wo werden solche dynamischen Abfragen benötigt?

• Umsatzanalyse mit monatlichen Werten
  Ein Unternehmen exportiert monatlich aus einem ERP-System Umsatzzahlen. Mit Power Query lassen sich Berechnungen so einrichten, dass sie automatisch auch die neuen Monate berücksichtigen.
• KPI-Tracking für verschiedene Produkte oder Filialen
  Ein Reporting erfasst Verkaufszahlen für verschiedene Filialen oder Produktgruppen. Neue Filialen oder Produkte werden als zusätzliche Spalten eingefügt. Statt die Berechnung nun jedes Mal manuell anzupassen, sorgt eine dynamische Abfrage dafür, dass neue Spalten automatisch in die Berechnungen integriert werden.
• Kostenverteilung über verschiedene Abteilungen
  In einem Unternehmen werden Budgets pro Abteilung verwaltet. Jede Abteilung hat eine eigene Spalte. Kommt eine neue Abteilung hinzu, wird sie automatisch in Berechnungen wie Durchschnitts- oder Summenbildung einbezogen.
• Portfolio-Performance-Analyse für verschiedene Anlageklassen
  Ein Investment-Reporting enthält die Wertentwicklung verschiedener Anlageklassen (z. B. Aktien, Anleihen, Fonds) als separate Spalten. Mit der Zeit kommen neue Anlageklassen oder Finanzprodukte hinzu. Dank der Dynamik werden Berechnungen wie Gesamtrendite, Durchschnittsrendite oder Risikokennzahlen automatisch angepasst.