Excel ist das Werkzeug für Zahlenjongleure und Power Query ist die perfekte Ergänzung, wenn es um Transformationen und Datenimporte in Excel geht. Häufig sind andere Exceldateien Datenquellen für weiterführende Analysen. Klar, dass Power Query den Import von Daten aus Exceldateien spielerisch beherrscht. Dabei greift Power Query auf drei Excel-Objekte zu und ich zeige Euch in diesem Beitrag, wie ihr optimal mit ihnen umgeht.

Die drei Excel-Objekte in Power Query

Power Query kann auf die folgenden drei Excel-Objekte zugreifen:

• Tabellenblätter,
• Tabellen und
• definierte Namen

Diese drei Objekte werde ich nun kurz erläutern.

Tabellenblätter

Jeder Nutzer von Excel arbeitet mit Tabellenblättern und organisiert in ihnen seine Daten. Die Möglichkeit unterschiedliche Tabellenblätter anzulegen, diese mit aussagefähigen Bezeichungen zu versehen und per Formeln zwischen ihnen zu referenzieren, ist eine der Kernfunktionalitäten von Excel als Tabellenkalkulation.

Tabellen

Bei Tabellen handelt es sich um das zweite Excel-Objekt, das von Power Query erkannt wird. Die Bezeichnung Tabelle (engl. table) ist im deutschen Excel leider etwas unglücklich verlaufen und führt häufig zu Verwechselungen mit Tabellenblättern (engl. sheets). Tabellen ermöglichen beispielsweise das Einfügen einer Ergebniszeile am unteren Ende der Tabelle, um einfach Summen, Durchschnittswerte etc. zu kalkulieren. Sie beinhalten automatisch einen Filter am Tabellenkopf. Ein weiteres Kennzeichen von Tabellen ist, dass eine Formel, die in eine Zelle eingetragen wird, automatisch für die gesamte Spalte gilt, in welcher sich die Zelle befindet. Aufgrund dieser kleinen technischen Helferlein werden diese Tabellen auch gern als »intelligente« Tabellen bezeichnet. Die folgende Animation zeigt, wie man »intelligente« Tabellen einfügt.

Definierte Namen

Bei definierten Namen handelt es sich um das dritte und letzte Objekt, welches Power Query in Excel identifizieren kann. Einer der wesentlichen Vorteile von definierten Namen ist, dass diese Bereiche (z. B unter Verwendung der Formel BEREICH.VERSCHIEBEN) dynamisch gestaltet werden können. So können diese Bereiche z. B. in Abhängigkeit der enthaltenen Datensätze mitwachsen und eine Summenformel verpasst nie eine Zeile. Die folgende Animation zeigt einen von mehreren Wegen, einen solchen definierten Namen zu erstellen.

Nachdem ich die importierbaren Excel-Objekte erläutert habe, muss die Frage beantwortet werden, in welcher Exceldatei sich die zu bearbeitenden Daten befinden.

Wo liegen die Daten: In der aktuellen Exceldatei, oder in einer externen?

Ob sich die Daten in der aktuellen Exceldatei befinden, stellt sich naturgemäß nur im Fall von Power Query für Excel. Bei der Verwendung von Power BI Desktop sind alle Exceldateien als externe Dateien zu betrachten.

Bei der Verwendung von Power Query in Excel kann es zwei Fälle geben:

1. Ich möchte auf Daten zugreifen, die sich in einer Exceldatei befinden, die verschieden ist von der, in der ich gerade die Power Query-Abfrage definiere.
2. Ich möchte auf Daten zugreifen, die sich in derselben Datei befinden, in der ich gerade meine Power Query-Abfrage erstelle.

In beiden Fällen bin ich mit Power Query in der Lage auf die oben genannten drei Excel-Objekte zuzugreifen. Lediglich der Weg ist in beiden Fällen unterschiedlich.

Ich beginne mit dem Zugriff auf eine externe Exceldatei, weil dies erfahrungsgemäß der häufigere Fall ist. Der Einfachheit halber, haben in meinem Beispiel alle Excel-Objekte dieselbe Datenstruktur. Bei unterschiedlichen Strukturen wären nachfolgende komplexere Verarbeitungsschritte notwendig, um die es hier jedoch nicht gehen soll.

Zugriff auf Daten einer externen Exceldatei

Die von mir erstellte Beispieldatei enthält alle drei Excel-Objekte, also Tabellenblätter, »intelligente« Tabellen und definierte Namen.

Nachdem Excel (4) als Datenquellentyp ausgewählt und die spezifische Exceldatei selektiert wurde, erscheint der Navigator von Power Query. Hier wird die Exceldatei in die drei Excel-Objekte gruppiert dargestellt. Beachte die unterschiedlichen Symbole vor dem Objektnamen:

1. »intelligente« Tabelle,
2. Tabellenblätter und
3. definierte Namen

Der Navigator gibt dem Nutzer 3 Möglichkeit, um die gewünschten Daten zu selektieren:

1. Die Auswahl eines einzelnen Elements der Datenquelle: Selektiere hierfür ein Element, z. B. den definerten Namen rngBereich.
2. Die Auswahl mehrerer Elemente der Datenquelle: Aktiviere zunächst die Checkbox Mehrere Elemente auswählen. Anschließend ist es möglich mehr als ein einziges Element gleichzeitig zu selektieren.
3. Die Auswahl aller Elemente der Datenquelle: Selektiere hierfür den gelben Ordner als  oberstes Element der Datenquelle. In diesem Beispiel ist die Datenquelle.xlsx.

Im Anschluss an die Selektion der gewünschten Daten hast Du die Möglichkeit die Daten über die Schaltfläche Laden direkt in ein entsprechendes Ziel zu laden (ein Excel-Tabellenblatt, das Excel Datenmodell, oder beides parallel), oder zuvor über die Schaltfläche Bearbeiten noch Modifikationen an den Daten vorzunehmen, bevor sie analysiert werden können.

Ich möchte mir an dieser Stelle kein einzelnes Elemt der Datenquelle betrachten, sondern die gesamte Exceldatei auf einmal. Zudem möchte ich vor dem Laden der Daten einen Einblick in die Datenquelle erhalten, um eventuelle Bearbeitungsschritte vorzunehmen. Aus diesem Grund selektiere ich im Navigator das oberste Element (Datenquelle.xlsx) und bestätige anschließend mit Bearbeiten. Es ergibt sich folgende Ansicht im Power Query-Fenster:

An dieser Stelle erläutere ich kurz die Spalten im Power Query-Fenster:

• Name – Dies ist die Bezeichnung des jeweiligen Elements (Items). Ich habe am Beispiel einer zu imporiterenden Exceldatei noch keinen Fall gefunden, in welchem sich der Wert in Spalte Name von dem in Spalte Item unterschieden hätte.
• Data – Diese Spalte beinhaltet die Daten des jeweiligen Elements (Items). Sie ist dadurch gekennzeichnet, dass die Werte grün formatiert sind und rechts neben der Spaltenbezeichnnung ein entgegengesetzt verlaufender Doppelpfeil erscheint. Es handelt sich hierbei also nicht um eine Information wie in den anderen Spalten, sondern um tabellarisch strukturierte Daten.
• Item – Hierbei handelt es sich um das jeweilige Element der zu importierenden Exceldatei. Hier werden die unterschiedlichen Tabellenblätter, »intelligenten« Tabellen und definierten Namen aufgelistet, die in der entsprechenden Datei existieren.
• Kind – Hat die Ausprägungen Sheet, Table und DefinedName und gibt damit an, ob die Datenquelle ein Tabellenblatt, eine »intelligente« Tabelle oder ein definierter Name ist.
• Hidden – Gibt an, ob die Datenquelle sichtbar ist. Wenn in der Quelldatei beispielweise das entsprechende Tabellenblatt intelligente Tabelle ausgeblendet ist, dann stünde in dieser Spalte der Wert auf TRUE. Für die VBA-Kenner unter Euch sei gesagt, dass Power Query nicht zwischen hidden und very hidden unterscheidet.

Legen wir jetzt unser Augenmerk auf die Spalte Kind.

In Spalte Kind (engl. Art) kannst Du die Differenzierung sehen, die Power Query hinsichtlich der Excel-Objekte vornimmt.

• Sheet = Tabellenblatt
• Table = »intelligente« Tabelle
• DefinedName = Definierter Name

Aus meiner Sicht ist es aus zwei Gründen sinnvoll zu wissen, um welche Art von Objekt es sich handelt:

1. Es ist durchaus möglich, dass zwei Elemente (Items) denselben, oder wenigstens sehr ähnliche Namen haben. Beispielsweise kann es sowohl ein Tabellenblatt, als auch eine »intelligente« Tabelle geben, die beide den Namen Datenquelle haben. Hier hilft das Wissen darüber, ob die Daten von einem Sheet, einer Table, oder einem DefinedName kommen.
2. Nur »intelligente« Tabellen haben definierte Überschriften. Klickt man in der Spalte Data neben (nicht auf) das Wort Table, erhält man im unteren Bereich eine Vorschau der Daten. Hierbei wird deutlich, dass Power Query bei »intelligenten« Tabellen die Überschrift bereits korrekt erkennt und diese in die Abfrage auch gleich einbaut. Bei Tabellenblättern und definierten Namen ist dies nicht so. In diesem Falle tauchen die Überschriften als erste Zeile des Datenbereichs auf.

Möchte ich  auf die eigentlichen Daten für die Weiterverarbeitung zugreifen, habe ich zwei Möglichkeiten:

1. Mich interessiert nur ein Element (Item), z.B. das Elemente Intelligente Tabelle.
2. Mich interessieren mehrere Elemente (Items), z. B. die Elemente Intelligente Tabelle und Tabellenblatt 1

Ich gehe zunächst auf den ersten Fall ein.

Zugriff auf ein einziges Element der externen Exceldatei

Der Zugriff auf ein einziges Element ist sehr unkompliziert. Hierfür ist lediglich ein Klick auf (diesmal nicht daneben) das Wort Table in der Spalte Data notwendig. Die folgende Animation zeigt noch einmal deutlich den Unterschied zwischen dem Klick neben das Wort Table und dem Klick auf das Wort Table.

Der Klick daneben zeigt eine kurze Vorschau der dahinterliegenden Daten. Der Klick auf Table löst die derzeitge Sicht in Power Query auf und erzeugt weitere Schritte in der Power Query-Abfrage im Fenster Angewendete Schritte. Die Folge ist eine neue Tabelle mit den dahinterliegenden Daten.

Der Zugriff auf mehrere Elemente ist unwesentlich komplexer und wird nachfolgend beschrieben.

Zugriff auf mehrere Elemente der externen Exceldatei

Reicht mir die Bearbeitung einer einzigen Datenquelle (Item) nicht aus, dann muss ich mehrere Datenquellen in einer Power Query-Abfrage kombinieren und anschließend, wie gewünscht bearbeiten. Hierfür reicht kein einfacher Klick auf Table, aber die notwendigen Bearbeitungsschritte sind einfach zu erlernen. Wie im folgenden Screenshot gezeigt, filtere ich die Datenquelle derart, dass diesmal zwei Elemente übrig bleiben: Die Elemente intelligente Tabelle und rngBereich.

Das Ergebnis nach dem Filtern sieht wie folgt aus:

Wichtig für mich ist hier wieder die Spalte Data. In ihr befinden sich die gewünschten Daten. Alle anderen Spalten beinhalten, wie vorher bereits erwähnt, lediglich Zusatzinformationen, die die Spalte Data ergänzen. In diesem Fall ist es nicht ausreichend, in einem der Datensätze auf das Wort Table zu klicken, um an die Daten heranzukommen. Wir müssen einen Weg finden, alle Tables gleichzeitig „anzuklicken“. Dies geschieht über die Benutzung des blau gekennzeichneten Doppelpfeils im Spaltenkopf der Spalte Data.

Das resultierende Ergebnis sieht dann wie folgt aus.

In dieser neuen Tabelle befinden sich neben den Metadaten – die für Daten-Analysen sicherlich unwichtig sind und entfernt werden können – die mit rot markierten Daten, inklusive Überschriften (Zeilen 1 und 5).

Zwei weitere Schritte sind notwendig, um diese Daten nutzbar zu machen. Zuerst definiere ich die Überschriften. Auf diesem wege sorge ich dafür, dass der erste Datensatz als Überschrift erkannt wird.

Im Anschluss erfolgt das Beseitigen weiterer Überschriften aus dem Datenbereich (Zeile 4).

Sicherlich kann der Datenbereich an dieser Stelle weiter optimiert werden, indem beispielsweise sämtliche Nullwerte und nicht benötigte Spalten entfernt würden. Dies nehme ich in diesem Beispiel jedoch nicht vor.

Hiermit wurden die Prinzipien für den Datenimport der verschiedenen Excel-Objekte aus externen Exceldateien dargestellt und wir können uns jetzt dem Datenimport aus der aktuellen Exceldatei zuwenden.

Zugriff auf Daten der aktuellen Exceldatei

Um Daten mit Power Query zu bearbeiten, die sich in derselben Exceldatei befinden wie die Power Query-Abfrage, ist eine andere Vorgehensweise notwendig, als im Fall der Verarbeitung von externen Exceldateien.

Zunächst zeige ich den bekannteren Weg, um Daten aus der aktuellen Exceldatei mit Power Query zu verarbeiten. Dieser Weg ist für den Import »intelligenter« Tabellen absolut empfehlenswert, hat jedoch Nachteile bezüglich des Imports der anderen oben genannten Excel-Objekte. Auf die Nachteile gehe ich weiter unten entsprechend ein.

Import über die Schaltfläche Aus Tabelle

Die Schaltfläche Aus Tabelle importiert die aktive »intelligente« Tabelle in Power Query. Die Schaltfläche könnte also auch Aus intelligenter Tabelle heißen. Aktiv meint hierbei, dass die aktuell selektierte Zelle (1) innerhalb der entsprechenden »intelligenten« Tabelle liegt. Sofern sich die aktive Zelle in einem Datenbereich befindet, der keine »intelligente« Tabelle ist, wird auf diesem Wege automatisch eine »intelligente« Tabelle erzeugt, bevor diese in Power Query importiert wird. Dies hat jedoch zur Folge, dass ich keine Möglichkeit habe, um im Reiter Entwurf den Tabellennamen selbst zu definieren. Excel vergibt in diesem Fall einen technischen Tabellennamen. Best practise ist es daher, die »intelligente« Tabelle zuvor manuell  zu erstellen, einen sprechenden Namen zu vergeben und anschließend über die Schaltfläche Aus Tabelle (2) in Power Query zu importieren.

Diese Vorgehensweise hat jedoch den Nachteil, dass auf diesem Weg ausschließlich »intelligente« Tabellen importiert werden können. Es steht mir an dieser Stelle natürlich frei auch Definierte Namen und komplette Tabellenblätter in eine »intelligente« Tabelle zu konvertieren, aber dies kann nicht die Lösung sein. Daher stelle ich Dir jetzt eine alternative Methode vor, über die Du an die Excel-Objekte der aktuellen Datei einfacher herankommst.

Die Importalternative: Datenquellentyp »Aus Arbeitsmappe«

Der Datenquellentyp »Aus Arbeitsmappe« schien mir lange Zeit nur für externe Exceldateien brauchbar zu sein. Doch es spricht nichts dagegen aus der aktuellen Exceldatei heraus, eben diese als zu importierende Exceldatei auszuwählen.

Nachdem der Datenquellentyp Aus Arbeitsmappe ausgewählt wurde, wähle ich nun die aktuelle Datei als Datenquelle aus.

Daraufhin öffnet sich der Navigator, der mich die zu importierenden Bestandteile der Arbeitsmappe auswählen läßt. Hier könnte ich mich bereits für den Import eines Tabellenblattes, einer intelligenten Tabelle oder eines definierten Namens entscheiden. Jedoch entscheide ich mich an dieser Stelle zunächst die komplette Datei zu importieren und meine Selektionen in späteren Schritten zu treffen. Daher wähle ich  die komplette Exceldatei aus (1) und klicke anschließend auf Bearbeiten (2).

Nachdem ich auf Bearbeiten geklickt habe, öffnet sich das Power Query-Fenster und der Anblick kommt mir sehr bekannt vor.

Von hier an sind die möglichen Arbeitsschritte exakt dieselben, wie im vorangegangenen Beispiel des Imports einer externen Exceldatei. Ich sehe die Spalten Name, Data, Item, Kind und Hidden der aktuellen Exceldatei. Von hieraus kannst Du genauso mit den einzelnen Excel-Objekten (Tabellenblatt, intelligente Tabelle und Definierten Name) umgehen, wie Du es beim Zugriff auf eine externe Exceldatei gesehen hast.

Besonderheiten beim Zugriff auf die aktuelle Exceldatei

Nachdem eine Power Query-Abfrage fertiggestellt ist, steht man vor der Entscheidung, wohin die Daten geladen werden sollen. Hierfür gibt es 3 Optionen:

• in eine Excel-Tabelle,
• in das Excel-Datenmodell,
• nicht laden und nur als Verbindung speichern.

Sofern Du die Abfrage in einem neuen Excel-Tabellenblatt speicherst, wird zusätzlich zum neuen Tabellenblatt auch eine neue »intelligente« Tabelle erzeugt, die die Ergebnisse dieser Abfrage beinhaltet.

Aktualisiere ich nun diese Abfrage, indem ich beispielsweise mit der rechten Maustaste auf die »intelligente« Tabelle klicke und Aktualisieren auswähle, erscheinen in dieser Abfrage zwei neue Objekte:

• das neue Tabellenblatt Tabelle1 und
• die intelligente Tabelle Datenquelle.xlsx selbst.

Da das Abfrage-Ergebnis in einer »intelligenten« Tabelle auf einem neuen Tabellenblatt ausgegeben wird, verfügt die Datei danach über zwei neue Elemente. In meinem Beispiel sind diese das Tabellenblatt Tabelle1 und die »intelligente« Tabelle Datenquelle_xlsx. Nach der Aktualisierung der Abfrage werden diese beiden neuen Elemente als Bestandteil der Datei im Abfrage-Ergebnis mit aufgelistet. Diesen Zirkelbezug sollte man immer im Hinterkopf 😉

Wenn Du den Newsletter abonniert hast, erhältst Du Zugriff auf die verwendeten Beispieldateien.

Bis zum nächsten Mal und denk dran: Sharing is caring. Wenn Dir der Beitrag gefallen hat, dann teile ihn gerne 🙂

Viele Grüße aus Hamburg,

Lars