Office-Blog

Will ich Daten aus verschiedenen Tabellen einer anderen Arbeitsmappe abrufen, geht das recht leicht, denn Power Query lässt mich gleich mehrere Tabellen einer anderen Datei zum Einlesen markieren. Deutlich weniger komfortabel und keineswegs intuitiv ist es, wenn mehrere Tabellen der aktuellen Mappe zusammenzuführen sind. Diese Aufgabe stellt sich immer dann, wenn Daten in einer Mappe auf mehrere Arbeitsblätter verteilt sind, z. B. ein Blatt pro Monat, ein Blatt pro Standort oder ein Blatt pro Abteilung.
Wie sich solche verteilten Daten durch einen kleinen Eingriff in den M-Code in einer einzigen Abfrage zusammenführen lassen, zeige ich in der folgenden Anleitung.

Das GELB markierte ist die zentrale Anweisung, um auf die gesamte aktuelle Arbeitsmappe zuzugreifen

Das ist die Aufgabe: Eine stets aktuelle Pivot-Auswertung inkl. Diagramm

In meinem Beispiel sind – wie unten gezeigt – die Personallisten für jeden Monat jeweils in einem eigenen Arbeitsblatt hinterlegt. Ziel ist eine Pivot-Auswertung, die Auskunft gibt über den Personalbestand in den verschiedenen Abteilungen nach Monaten. Dazu müssen die »intelligenten Tabellen« aus verschiedenen Arbeitsblättern für die Auswertung zusammenführt werden.

Pro Blatt gibt es eine Tabelle und alle müssen zu einer einzigen Datenquelle zusammengefasst werden

Normalerweise klicke ich in eine Tabelle und wähle unter Daten > Daten abrufen > Aus Tabelle/Bereich, um die Daten dieser Tabelle in Power Query einzulesen. Bei mehreren Tabellen müsste ich diesen Schritt entsprechend oft wiederholen. Erst anschließend könnte ich alle eingelesenen Daten für die Auswertung mit Pivot untereinander zu einer Liste anfügen.
Es wäre ziemlich umständlich, wenn ich für den Jahresüberblick zwölfmal Daten einlesen und jedes Mal die Abfrage in Power Query erweitern müsste, wenn ein neuer Monat dazu kommt.

Mehrere oder alle vorhandenen Tabellen der aktuellen Mappe einlesen

Mit einem kleinen Trick – mit einem Eingriff in den M-Code – löse ich das Problem:

• Zuerst setze ich die Markierung in die erste einzulesende Tabelle – hier die für Januar – und wähle Daten abrufen > Aus Tabelle/Bereich. Im Editor von Power Query erscheint nun im Schritt Quelle in der Bearbeitungsleiste der folgende M-Code:
  = Excel.CurrentWorkbook(){[Name=“Januar“]}[Content]
• Da ich nicht nur eine Tabelle einlesen will, lösche ich den hier rot dargestellten Code und greife damit auf den Inhalt der kompletten Mappe zu.

Von hier aus kann auf den gesamten Inhalt der aktuellen Mappe zugegriffen werden

• Per Klick auf den Doppelpfeil in der Spalte Content entpacke ich den Inhalt aller Tabellen zu einer langen Liste. Dabei kann ich die Spalten auswählen, die für meine Auswertung relevant sind.
  Hinweis: Über den kleinen Pfeil in der Spalte Name könnte ich auch konkrete Tabellen für die Konsolidierung auswählen. Da ich dies nicht mache, werden auch neu hinzukommende Tabellen stets gleich mit konsolidiert.

Der Inhalt der intelligenten Tabellen kann über den Doppelpfeil entpackt und die gewünschten Spalten ausgewählt werden

• Die Spalte Name enthält die Bezeichnungen der »intelligenten Tabellen«. Hier im Beispiel wurden sie nach den Monaten benannt. Ich ändere den Spaltenamen um in Monat, denn damit habe ich später bei der Auswahl in der Pivot-Tabelle eine eindeutige Spaltenbezeichnung.
• Den Namen der Abfrage rechts ändere ich in AlleMonate.

Hier sind bereits alle intelligenten Tabellen konsolidiert in einer Liste mit hinzugefügter Spalte für den Monat

• Nun lade ich die Daten in eine Pivot-Tabelle oder in ein Pivot-Diagramm.

Fertig! Die Pivot-Tabelle kann jederzeit per Rechtsklick um weitere Monate erweitert werden

Fazit: Kleine Eingriffe in den M-Code machen Abfragen nachhaltiger

Kommt ein weiteres Blatt mit einem neuen Monat dazu, genügt ein Rechtsklick in die Pivot-Tabelle und ein Klick auf Aktualisieren.

Dieses Beispiel zeigt, wie nützlich es ist, den automatisch generierten M-Code bewusst wahrzunehmen und wo nötig anzupassen.

• Es lassen sich Aufgaben mit Power Query lösen, für die es standardmäßig keine Befehle gibt.
• Durch bewusstes Verfolgen des M-Codes lassen sich auch Schwachstellen – wie hart codierte Ausdrücke – ausfindig machen und Abfrageschritte für künftige Auswertungen optimieren.

In meinem Aufbauworkshop zu Power Query im März geht es genau darum: mit gezielten Eingriffen und pragmatischen Techniken mehr aus dem Power Query-Editor herauszuholen und Abfragen damit flexibler und robuster zu machen ?.

Den Flyer per Klick auf das Bild herunterladen