POWER QUERY 

1) Was ist Power Query - Überblick und Einsatzmöglichkeiten

Abonniere, Daumen hoch und deine Fragen in die Comments 😉

Das Excel Tool Power-Query bzw. "Daten abrufen und transformieren" hilft dir dabei, ETL-Prozesse durchzuführen. Die Abkürzung „ETL“ steht dabei für „Extract, Transform & Load“, womit ein Prozess beschrieben wird, bei dem Daten aus mehreren – gegebenenfalls unterschiedlich strukturierten – Datenquellen in einer einzigen Zieldatenbank oder Tabelle zusammengeführt, dort umgewandelt und dir dann in Form einer Tabelle ausgegeben werden. 

ETL beinhaltet also

  • Extraktion der relevanten Daten aus verschiedenen Quellen, 
  • Transformation der Daten in das Schema und Format der Zieldatenbank bzw. Tabelle,
  • Laden der Daten in die Zieldatenbank bzw. Tabelle. 

Mithilfe von Power-Query kannst du also Daten aus unterschiedlichen Datenquellen abrufen: 

  • Dateien 
    Mit Power-Query kannst du nicht nur Daten in einzelnen ArbeitsmappenCSV-Dateien, Textdateien usw. abrufen, sondern auch ganze Ordner abfragen. Das bedeutet, du kannst eine Abfrage auf einen bestimmten Ordner durchführen, und sobald in diesem Ordner neue Dateien hinzukommen, werden sämtliche Daten aus diesen neuen Dateien ebenfalls von Power-Query automatisch abgefragt und umgewandelt. Auf diese Weise erhältst du immer einen aktuellen Datensatz. Das ist beispielsweise sehr nützlich bei einem regelmäßigen Reporting. 
  • Datenbanken 
    Access, SAP, salesforce, SQL-Server … Der Vorteil hierbei besteht darin, dass du nicht – wie bei einer Excel-Datei – auf 1 Mio. Datensätze beschränkt bist, sondern unbegrenzt viele Datensätze abrufen kannst. Darüber hinaus läuft die Abfrage mit Power-Query wesentlich schneller. 
  • Web 
    Tabellen von Wikipedia, Wechselkurse, Aktienkurse von Webseiten aus dem Internet … alle diese Abfragen können in regelmäßigen Abständen aktualisiert werden. Hierfür gibt es bei Power-Query die Möglichkeit, den exakten Zeitpunkt für die Aktualisierung (bspw. immer beim Öffnen der Datei oder des Ordners) bzw. das gewünschte Intervall für die laufenden Aktualisierungen (bspw. alle 10 Minuten) einzustellen. 

Auf diese Art ist es ganz einfach, Daten aus unterschiedlichen Quellen abzurufen.

1.1) Webscrapping mit Power-Query

Ein weiterer Vorteil besteht darin, dass du mit Power-Query mithilfe von sogenannten „Parametern“ auch mehrere Seiten abrufen kannst. Das heißt, dass du theoretisch Hunderte von Seiten abrufen und Daten ziehen kannst, wenn die URLs gleichmäßig strukturiert sind. Wenn du beispielsweise Wirtschaftsdaten von verschiedenen Ländern ziehen möchtest und sich die Webseiten nur über das Länderkürzel in der URL unterscheiden, dann kannst du diese Länderkürzel mithilfe von Parametern in die Abfrage hineingeben und somit Daten von einer Vielzahl von Webseiten abrufen und zu einem Datensatz zusammenführen. 

1.2) Data-Clean-Up mit Power-Query

Weiters lässt sich mithilfe von Power-Query auch der Daten-Clean-up vereinfachen. Oft ist es ja so, dass man verschiedene Daten wie Adressdatensätze u. dgl., die noch nicht in einer passenden Form vorhanden sind, aufbereiten muss. Hierfür gibt es mit Power-Query viele neue Möglichkeiten, um das Ganze zu vereinfachen, indem dir Power-Query den Einsatz von Funktionen und Makros erspart. 

Wenn du dir das Ganze in einem größeren Kontext ansiehst [Video ab 4:06 min], dann gibt es zunächst einmal die Datenquellen und dann vier Arbeitsschritte: 

  • Input 
  • Analyse 
  • Visualisierung 
  • Output 

Im Gegensatz zu einem manuellen Daten-Clean-up, bei dem du im Falle von neuen und/oder aktualisierten Daten diesen immer wieder aufs Neue ausführen und die Daten vielleicht auch noch mithilfe von Funktionen zusammenfügen muss, kannst du, wie oben angesprochenmit Power-Query durch Klicken auf die Abfragen hinzugekommene und/oder geänderte Daten ganz einfach aktualisieren. Die neuen Daten werden also von Power-Query gezogen und aufbereitet und können danach dafür verwendet werden, um beispielsweise Pivot-Tabellen und Pivot-Charts zu erstellen, die du dann zu Dashboards zusammenfügen kannst. Diese Dashboards und Grafiken können anschließend in Word- oder in PPT-Berichte überführt, per E-Mail verschickt oder auf Datenbanken hochgeladen werden usw. 

Power-Query hilft dir also dabei, Daten von verschiedenen Quellen abzurufen, diese in einem ersten Schritt in das richtige Format zu bringen und anschließend zusammenzuführen sowie bei Bedarf die gezogenen Datensätze auch zu vergleichen (z. B. Vergleich von Ist- und Soll-Daten). Kurz gesagt kannst du durch Power Query ganz einfach Daten aus verschiedenen Quellen abrufen und transformieren.

Im Video [ab 5:23 min] kannst du dir das Ganze nochmals als Tabelle mit verschiedenen Anwendungsbeispielen aufbereitet ansehen. 

2) Daten ent-pivotieren 

Abonniere, Daumen hoch und deine Fragen in die Comments 😉

Bei Excel 2016 und der Abo-Version Excel 365 findest du Power-Query bereits integriert, und zwar im Menü unter dem Reiter im Block „Daten abrufen und transformieren“. Hier findest du auch die verschiedenen Möglichkeiten, um Daten aus unterschiedlichen Quellen wie Textdateien, CSV-Dateien, Web etc. abzurufen, und unter „Daten abrufen“ stehen dir noch einmal die vielen verschiedenen Quellen zur Auswahl. Zudem kannst du dir einen Überblick über die bestehenden Abfragen und die Quellen verschaffen, die du quasi „angezapft“ hast. 

Wer Excel 2010 und Excel 2013 verwendet, findet Power-Query als separaten Reiter im Menü (siehe Video: 0:33–0:49 min]. 

In unserem Beispiel [ab 0:49 min] findest du eine Datenliste (noch keine Tabelle), in der die Informationen in einzelnen Spalten dargestellt sind. 

Power Query Beispiel

Eine solche Datenliste ist nicht sehr praktisch, und zwar vor allem dann, wenn du daraus eine Pivot-Tabelle erstellen, das Ganze also in ein Datenbank-Format bringen und damit entsprechend weiterarbeiten möchtest. Mithilfe von Power-Query hast du jetzt also die Möglichkeit, diese Daten in diesem Bereich abzufragen. Wenn du auf den Button „Aus Tabelle/Bereich“ klickst, markiert Excel automatisch den gesamten Abfragebereich – denn Power-Query benötigt eine Tabelle – und öffnet das Dialogfenster „Tabelle erstellen“. 

Intelligente Tabelle

Durch Klicken auf „OK“ wird die Liste in eine Tabelle umgewandelt, und es öffnet sich der Abfrage-Editor, wo du eine Abfrage auf diesen Bereich ausführen kannst. Wie dieser Editor im Einzelnen aufgebaut ist, siehst du im Video [1:50–2:48 min]. 

Im nächsten Schritt wirst du die Tabelle entpivotieren, d. h. die Spalten in Zeilen umändern. Dazu markierst du zunächst die Spalten, gehst danach auf den Reiter „Transformieren“ und klickst danach auf den Button „Spalten entpivotieren“. 

Power Query Editor

Dadurch werden die einzelnen Spalten zu einzelnen Zeilen umgewandelt. 

Entpivotieren

Würdest du diesen Schritt in Excel ohne Power-Query machen, wäre das mit sehr viel Copy & Paste oder mit einer zeitaufwendigen Erstellung von Makros verbunden. Alleine diese zwei Klicks haben dir also bereits unglaublich viel Zeit erspart. 

Sobald du mit deiner Abfrage fertig bist, klickst du links oben im Reiter „Start“ auf „Schließen & laden“. 

Schließen und laden

Danach wird die Abfrage gespeichert und als eigenes Arbeitsblatt in das Excel-File hineingeladen und es öffnet sich rechts ein separates Fenster, wo das Ergebnis deiner Abfrage angezeigt wird. 

Abfrage Vorschau

Die letzten Schritte werden dir auch im Video [2:48– min] ganz genau gezeigt. Darüber hinaus wird dir erklärt, wie die neu entstandene Tabelle im Einzelnen aufgebaut ist und wie du diesen Datenblock in eine Pivot-Tabelle (siehe unten) umwandeln kannst, mit der sich im Weiteren sehr gut arbeiten lässt, um beispielsweise Pivot-Charts zu erstellen und die Daten bequem auswerten zu können. 

Abfrageergebniss als Pivot

3) Daten aus verschiedenen Tabellen zusammenführen 

Abonniere, Daumen hoch und deine Fragen in die Comments 😉

Ein großer Teil der Arbeit bei Excel besteht häufig darin, zuerst Daten zusammenzuführen. Du hast also verschiedene Quellen, Datensätze, Tabellen usw. als Basis, die du in einem ersten Schritt zusammenbekommen musstum sie in einem weiteren Schritt bearbeiten und auswerten zu können. 

In unserem Beispiel gibt es also die beiden Datensätze A und B, die du entweder „anfügen“ oder „zusammenführen“ kannst. 

„Anfügen“ bedeutet, dass von den beiden Datensätzen jeweils eine Kopie erstellt wird, die aneinandergefügt bzw. untereinander gepackt werden, wodurch sich ein großer neuer Datensatz ergibt, der in weiterer Folge ausgewertet werden kann. 

Abfragen anfügen

Im Gegensatz dazu bedeutet „Zusammenführen“, dass die beiden Datensätze nicht (wie oben beschrieben) komplett „angefügt“ werden, weil von den beiden Datensätzen nicht der gesamte Inhalt, sondern nur eine bestimmte Information benötigt wird, sodass also beispielsweise vom Datensatz B nur eine einzelne Spalte genommen wird. Das macht man normalerweise mit einer Verweis-Funktion wie der SVERWEIS-Funktion oder der INDEX-Funktion usw. oder mit einfachen Verknüpfungen bzw. Verbindungen, wie du sie in Datenbanken findest. 

Abfragen zusammenführen

Diese beiden Arten des „Anfügens“ bzw. „Zusammenführens“ lassen sich mithilfe von Power-Query auf ganz einfache Art und Weise erledigen. Den dazugehörigen Befehl findest du im Reiter „Daten“ unter dem Button „Daten abrufen / Abfragen kombinieren“. 

Zusammenführen

Gerade wenn es sich um sehr viele Daten handelt, kannst du dir hiermit jede Menge Zeit sparen. 

Im Video findest du ab 1:50 min ein Beispiel mit den Tabellen „Warenbestand IST“ und „Warenbestand SOLL“ mit jeweils Artikelnummern, Referenznummern und Bestands-Einheiten. Diese beiden Tabellen sollen nun miteinander verglichen werden, um herauszufinden, wie viele Einheiten nachbestellt werden müssen, um die Lagerbestände wieder bis auf die gewünschten SOLL-Bestandsmengen aufzufüllen. 

Zu diesem Zweck könnte man theoretisch mit der SVERWEIS-Funktion arbeiten oder die beiden Datenblöcke untereinander kopieren. Das Problem dabei ist, dass in der Praxis häufig mit vielen Tabellen gearbeitet wird, die sehr groß sind. Ein Vergleich derselben würde zum einen sehr viel Zeit in Anspruch nehmen, und zum anderen müsste, sobald neue Daten in die einzelnen Tabellen eingespielt werden, für den Fall, dass die Daten angefügt werden, der jeweilige Vorgang (SVERWEIS-Funktion, Kopieren etc.) wiederholt werden. 

Mit Power-Query werden die Quelldateien hingegen nicht mehr angefasst, d. h. die Tabellen bleiben in ihrer Ursprungsform bestehen und das Zusammenführen sowie der Vergleich der Datensätze wird nur ein einziges Mal aufgesetzt. Power-Query ermöglicht also ein nachhaltiges Arbeiten und erspart dir damit jede Menge Zeit. 

Im ersten Schritt [siehe Video ab 3:02 min] muss eine Abfrage auf die erste Tabelle ausgeführt werden. Durch Klicken auf „Aus Tabelle/Bereich“ im Reiter „Daten“ wird der betreffende Bereich von Excel automatisch erkannt, es wird eine Abfrage ausgeführt und der Abfrage-Editor öffnet sich. Wenn du auf der linken Seite das Abfrage-Fenster aufklappstsiehst du, dass es sich dabei um die Tabelle tbl_Ist handelt. 

Abfrage im Power Query Editor

Nun muss die Tabelle geschlossen und geladen werden – aber diesmal verwendest du nicht wieder direkt den Button „Schließen & laden“, sondern klickst auf das kleine schwarze Dreieck rechts unten im Button, um das Auswahlmenü zu öffnen, und wählst den Punkt „Schließen & laden in…“ aus. 

Schließend und laden in

Der Grund dafür liegt darin, dass sich daraufhin das Dialogfenster „Daten importieren“ öffnet und du somit sofort auswählen kannst, ob du das Ganze als Tabelle, als Pivot-Tabelle oder als Pivot-Chart ausgegeben haben möchtest oder ob du nur eine Verbindung erstellen willst. 

In unserem Fall wählst du den Punkt „Nur Verbindung erstellen“ an … 

Nur Verbindung erstellen

… denn du willst ja die andere Abfrage erst noch dazuholen und nicht das Ganze hier direkt in Excel ausgegeben bekommen. 

Auf der rechten Seite öffnet sich nun das Fenster „Abfragen und Verbindungen“, und du siehst, dass die erste Abfrage erstellt ist. Wenn du mit der Maus drübergehst, öffnet sich auch das entsprechende Vorschaufenster und du kannst die Daten einsehen. 

Abfrage Vorschau

Die oben beschriebenen Schritte werden nun für die Tabelle „Warenbestand SOLL“ wiederholt. 

Soll Bestand

Nun hast du also zwei Abfragen erstellt. Im nächsten Schritt geht es darum, wie du diese beiden verbindest. Als Erstes wirst du die beiden Abfragen wie oben beschrieben „anfügen“. 

Soll Bestand

Du packst also die beiden Datensätze untereinander. Daraufhin öffnet sich erneut ein Dialogfenster, und du wirst gefragt, ob es sich um zwei oder um mehr Tabellen handelt. Als Nächstes wählst du die primäre Tabelle aus und danach die sekundäre, also die Tabelle zum Anfügen an die primäre Tabelle. 

Anfügen

Nachdem du mit „OK“ bestätigt hast, öffnet sich erneut der Abfrage-Editor und du dir wird eine Vorschau gezeigt, wie das Ganze aussehen wird. 

Append

Du siehst, dass die Artikel aus der ersten Tabelle und die Artikel aus der zweiten Tabelle blockweise untereinander „angefügt“ wurden. Allerdings ist hier auf den ersten Blick nicht erkennbar, welche Daten dem IST- und welche dem SOLL-Bestand zugeordnet werden können. Um das zu erreichen, könnte man nun theoretisch die Ursprungstabellen anpassen, aber der dafür erforderliche Aufwand wäre nicht „nachhaltig“. 

Aus diesem Grund gehst du nun in deine Ursprungsabfrage hinein, indem du im linken Abfragefenster auf die Abfrage „tbl_Ist klickst. 

tbl_ist

Hier kannst du nämlich auch einzelne Spalten hinzufügen, die allerdings ausschließlich in diesem Abfrage-Editor hinzugefügt werden. Sie scheinen in den Ursprungstabellen nicht auf, sondern erst wieder im Endergebnis. 

Dazu gehst du im Menü auf „Spalte hinzufügen“ und klickst auf „Benutzerdefinierte Spalte“, die du mit „Zustand“ betitelst. In das Feld „Benutzerdefinierte Spaltenformel“ kannst du entweder eine Formel einsetzen oder einen Wert definieren – in unserem Fall "IST". 

Benutzerdefinierter Spaltentitel

Und das Gleiche machst du anschließend mit der Abfrage „tbl_Soll. 

Zustand Soll

Wenn du nun auf die zuvor erstellte Abfragetabelle „Append1“ klickst, siehst du, dass die neue Spalte mitsamt den hinzugefügten Werten „IST“ und „SOLL“ automatisch übernommen wurde. 

Append Ergebnis

Wie du diese Abfrage beispielsweise in eine Pivot-Tabelle umwandeln kannst, die dir die Differenz zwischen dem IST- und dem SOLL-Zustand – also die Anzahl an erforderlichen Einheiten, für die jeweiligen Artikel nachbestellt werden müssen – anzeigt, siehst du im Video [7:28–8:55 min]. 

Pivot-Tabelle

Diese Art des Kombinierens von Daten bzw. Datensätzen kannst du aber nicht nur über „Daten/Daten abrufen/Abfragen kombinieren“ ausführen, sondern auch im Power-Query Abfrage-Editor, in dem es Buttons gibt, mit denen du ein Kombinieren der Abfragen initiieren kannst – aber dazu kommen wir etwas später. 

Im Video [9:18–12:52 min] wird dir noch einmal ganz genau der Unterschied zwischen Anfügen und Zusammenführen erklärt. 

Zusammenführen

Durch Klicken auf „Zusammenführen“ öffnet sich wiederum ein Dialogfenster, bei dem du zunächst die Ausgangstabelle auswählen musst, also in unserem Fall die SOLL-Tabelle, da hier mehr Einträge enthalten sind. 

erste Abfrage

Als Nächstes wählst du die IST-Tabelle aus.

zweite Abfrage

Danach musst du – genau wie bei einer SVERWEIS-Funktion – eine Verbindung herstellen und daher auswählen, welche Spalte in den beiden Tabellen gleich ist. In unserem Fall wäre das entweder die Artikelbezeichnung oder auch die Referenz-ID. 

Du nimmst als Beispiel die Referenz-ID, die du nun in beiden Tabellen anwählst, damit eine Beziehung/Verbindung erstellt wird, die dem Suchkriterium beim SVERWEIS entspricht.

Schlüsselspalte auswählen

Ganz unten findest du unter „Join-Art“ eine Auswahl an verschiedenen Möglichkeiten, um diese beiden Tabellen zusammenzuführen, wobei dir standardmäßig der Punkt „Linker äußerer Join (alle aus erster, übereinstimmende aus zweiter)“ angeboten wird.

Join

Du nimmst für unser Beispiel zunächst einmal das erste Auswahlkriterium, also den Standard-Join, der dem SVERWEIS entspricht. Auf die übrigen werden wir später noch im Detail eingehen. Nach dem Klick auf „OK“ bekommst du eine neue Abfrage, die jetzt allerdings nicht mehr mit „Append“ (= anfügen) bezeichnet wird, sondern mit „Merge“ (= zusammenführen).

Merge

Auf der linken Seite dieser Tabelle (weißer Hintergrund) siehst du nun unsere Ursprungsabfrage, also unsere SOLL-Tabelle, und auf der rechten Seite (grüner Hintergrund) in der Spalte tbl_Ist Informationen aus der IST-Abfrage, die allerdings fürs Erste noch in komprimierter Form angezeigt werden.

Tabelle anfügen

In den einzelnen Zeilen steht „Table“, was bedeutet, dass sich hier noch mehrere Daten befinden und dass du im nächsten Schritt zunächst einmal auswählen musst, welche Daten du von der IST-Abfrage an die andere Abfrage, also an die SOLL-Abfrage, anspielen willst. 

Zu diesem Zweck klickst du zunächst auf das Knöpfchen rechts oben (mit den beiden kleinen Pfeilen nach links und rechts).

Expand

Daraufhin öffnet sich ein Dialogfenster, in dem du die einzelnen Spalten aus der anderen Tabelle bzw. Abfrage findest. Als Nächstes musst du auswählen, was du dazu gespielt bekommen möchtest.

Spalten auswählen

In unserem Fall kannst du die Spalten „Artikel“ und „Referenz“ wegklicken, weil beides bereits vorhanden ist. Das Häkchen bei „Ursprünglichen Spaltennamen als Präfix verwenden“ kannst du ebenfalls entfernen. Danach klickst du wiederum auf „OK“ …

OK

… und bekommst jetzt rechts (grüner Hintergrund) zwei neue Spalten mit den Bezeichnungen „Einheiten.1“ bzw. „Zustand.1“ angezeigt.

Neue Spalten

Somit hast du nun alle benötigten Daten zusammen. Als Nächstes klickst du wieder links oben im Menü auf „Schließen & laden/Schließen & laden in…“. Diesmal möchtest du allerdings, dass das Ganze in Form einer Tabelle ausgeworfen wird.

Tabelle

Schließlich kannst du rechts neben der Tabelle eine neue Spalte („Differenz“) sowie die Formel „=[@Einheiten]-@[Einheiten.1]]“ für die Berechnung der Differenz einfügen.

Intelligente Tabelle

Als Ergebnis bekommst du nun wiederum die benötigte Information für die Nachbestellung, also jene Mengenangaben ausgeworfen, die für das Auffüllen der Lagerbestände zum Erreichen des SOLL-Zustands erforderlich sind.

Differenz

Auf diese beiden Möglichkeiten, Daten zusammenzuführen, wird in weiteren Lektionen noch ein bisschen genauer eingegangen, darüber hinaus auch darauf, was man mit den einzelnen Dialogen machen kann. Auch die verschiedenen Join-Arten werden noch ausführlich besprochen. 

Das Besondere an Power-Query ist, dass diese Abfragen, wie bereits des Öfteren erwähnt, nachhaltig gestaltet sind, das heißt, dass die einmal erstellten Abfragen ganz einfach aktualisiert werden können, um neue Zahlen zu laden, die dann in den Tabellen, Pivot-Charts usw. aufscheinen. Ein Beispiel dafür kannst du dir im Video [ab 13:23 min] ansehen.

Merge Abfrage

4) Daten aus Arbeitsmappen zusammenführen

Abonniere, Daumen hoch und deine Fragen in die Comments 😉

Mit Power-Query kannst du nicht nur Daten aus Tabellen oder Bereichen abfragen, sondern auch aus ganzen Dateien. Zu diesem Zweck gehst du im Menü „Daten“ auf „Daten abrufen“ / „Aus Datei“ und wählst aus den verschiedenen angebotenen Optionen „Aus Arbeitsmappe“ aus.

Aus Arbeitsmappe

Als Nächstes öffnet sich das Dialogfeld „Daten importieren“, wo du für unser Beispiel die Datei „Warenbestand.xlsx“ markierst und anschließend auf den Button „Importieren“ klickst. Das entspricht dem gleichen Beispiel wie aus der vorherigen Lektion, das heißt, wir haben wieder mit den Warenbeständen für IST- und SOLL-Zustand zu tun.

Im Dialogfenster, das sich nun öffnet, siehst du zum einen die Datei, die du ausgewählt hast, also „Warenbestand.xlsx“, und zum anderen eine Auflistung all dessen, was du aus dieser Datei abfragen kannst. Du hast zum einen die verschiedenen Arbeitsblätter „Warenbestand IST“ und „Warenbestand SOLL“, aber auch die Tabellen „tbl_Ist“ und „tbl_Soll“. Bei welcher Zeile es sich um eine Tabelle und bei welcher es sich um ein Arbeitsblatt handelt, ist aus den jeweiligen Icons links daneben ersichtlich. Auf der rechten Seite bekommst du, sobald du einen Eintrag markierst, eine Vorschau dessen ausgeworfen, was du gerade abrufst.

Navigator

Wenn du das Kästchen links neben „Mehrere Elemente auswählen“ anklickst, hast du die Möglichkeit, nicht nur eine einzige, sondern mehrere Tabellen abzurufen. Dabei ist es wichtig, dass du dir überlegst, in welcher Reihenfolge du die Elemente anklicken möchtest, denn in ebendieser Abfolge erscheinen die Abfragen später im Power-Query Editor.

Tabellen auswählen

Rechts unten hast du die Möglichkeit, das Ganze direkt zu laden.

Hast du nur eine einzige Abfrage ausgewählt, dann erscheint diese unmittelbar nach dem „Laden“ direkt in deinem geöffneten Arbeitsblatt, wird also direkt abgerufen.

Wenn du hingegen mehrere Elemente ausgewählt hast und auf „Laden“ klickst, wird nur eine Verbindung erstellt. Rechts öffnet sich der Bereich „Abfragen und Verbindungen“ mit den beiden Abfragen. Du hast also dann hier die Verbindung und kannst mit beiden weiterarbeiten.

Die einzelnen Schritte bis hierher kannst du dir noch einmal im Video ansehen [0:00–2:09 min].

Mithilfe des Rechtsklicks werden in unserem Beispiel die beiden Abfragen nacheinander markiert und können danach gelöscht werden. Zum endgültigen Löschen musst du deine Absicht in dem Dialogfenster „Abfrage löschen“, das sich nach deinem Rechtsklick öffnet, noch einmal bestätigen.

Löschen

Als Nächstes führst du noch einmal deine Abfrage aus („Daten abrufen“ / „Aus Datei“ / „Aus Arbeitsmappe“), indem du auf „Warenbestand.xlsx“ und danach auf „Importieren“ klickst.

Importieren

Danach setzt du im „Navigator“ ein Häkchen bei „Mehrere Elemente auswählen“, wählst die beiden Tabellen (IST und SOLL) aus und klickst diesmal nicht nur auf den Button „Laden“, sondern öffnest das Auswahlmenü und wählst „Laden in…“.

Laden in

Danach kannst du entscheiden, ob diese Abfragen direkt als Verbindung geladen werden sollen, als Tabelle, als Pivot-Tabelle oder als Pivot-Chart, wobei „Nur Verbindung erstellen“ dem Laden entspricht, das wir vorhin in unserem Beispiel ausgeführt haben.

Nur Verbingung

Unten hast du zudem noch die Möglichkeit, diese Daten dem Datenmodell hinzuzufügen. Das Datenmodell befindet sich im Hintergrund von Excel und kommt dann zum Einsatz, wenn du ein anderes Tool – Powerpivot – nutzt. Der Vorteil bei diesem Datenmodell besteht darin, dass deine Daten im Speicher liegen und du dadurch Berechnungen viel schneller durchführen kannst.

Wenn du „Tabelle“ oder „Pivot-Tabellen-Bericht“ auswählst, hast du die Möglichkeit, das Ganze entweder in einem neuen Arbeitsblatt zu platzieren oder in ein bereits bestehendes Blatt zu integrieren; bei Letzterem musst du dann nur noch die gewünschte Zelle auswählen.

Die oben beschriebenen Schritte kannst du dir wiederum im Video ansehen [2:09–4:15 min].

Das waren die unterschiedlichen Möglichkeiten, die Daten mithilfe von Power-Query zu laden.

Der wirkliche Mehrwert von Power-Query ist allerdings nicht die Möglichkeit, damit Daten einfach nur abzufragen, sondern er besteht darin, dass du das, was du abgefragt hast, auch entsprechend nachbearbeiten kannst. Das passiert durch Anklicken des Buttons „Bearbeiten“ rechts unten.

Bearbeiten

Daraufhin öffnet sich wieder der Power-Query Abfrage-Editor mit den beiden Abfragen, und du kannst nun damit weiterarbeiten.

Bearbeiten2

Genau wie vorhin kannst du jetzt deine beiden Abfragen zusammenführen. Während du vorher auf den Button „Daten abrufen / …“ geklickt und danach die Abfragen kombiniert hast, musst du nun innerhalb des Abfrage-Editors im Menüband „Start“ beim Block „Kombinieren“ den entsprechenden Punkt auswählen.  Auch hier kannst du dich wieder zwischen den Auswahloptionen „Abfragen zusammenführen“ und „Abfragen anfügen“ entscheiden.

Abfragen anfügen

Wenn du auf das kleine Dreieck bei „Abfragen zusammenführen“ klickst, kannst du zwischen „Abfragen zusammenführen“ und „Abfragen als neue Abfrage zusammenführen“ auswählen.

Abfrage als neue Abfrage zusammenführen

Wenn du auf „Abfrage zusammenführen“ klickst, werden die geladenen Abfragen entsprechend bearbeitet und erweitert. Wir wollen in unserem Beispiel jedoch eine neue Abfrage erstellen und die Ursprungsabfragen unberührt lassen und wählen daher den zweiten Punkt aus.

Daraufhin öffnet sich wiederum das Dialogfenster „Zusammenführen“, wo du die beiden Tabellen auswählst und danach einen Schlüssel wählst (in unserem Fall markierst du oben und unten jeweils die Spalte „Artikel“). Danach hast du die Möglichkeit, zwischen den verschiedenen Join-Arten zu wählen, wobei wir in unserem Beispiel die erste Option „Linker äußerer Join …“ nehmen.

Left outer Join

Wenn du anschließend auf „OK“ klickst, erscheint links im Abfragefenster die Datei „Merge1“. Das bedeutet, dass nicht eine bereits bestehende Abfrage erweitert, sondern dass eine ganz neue Abfrage erstellt wurde.

Wie das Kombinieren der beiden Tabellen mit dem Befehl „Abfragen anfügen“ abläuft, siehst du im Video [6:20–6:41 min]. Der Name der neuen Abfrage lautet in diesem Fall „Append1“.

Append

Für das Zusammenführen von Abfragen musst du nicht immer neue Abfragen erstellen, sondern du kannst natürlich auch bereits bestehende Abfragen erweitern [siehe Video ab 6:42 min].

Für unser Beispiel wählst du dazu die Abfrage „tbl_Ist“ aus und klickst anschließend auf „Abfragen zusammenführen“. Danach wählst du wieder eine zweite Tabelle aus (in unserem Fall „tbl_Soll“) und stellst eine Beziehung zwischen diesen beiden Abfragen her. Nach dem Klick auf „OK“ wird keine neue Abfrage erstellt, sondern es wird die bereits bestehende Abfrage um die zuvor ausgewählten Daten (in unserem Fall die Spalte „Artikel“) erweitert.

Spalte erweitern

Möchtest du diesen letzten Kombinierungsschritt (oder auch einen anderen Kombinierungsschritt davor) aus irgendeinem Grund rückgängig machen, so hast du die Möglichkeit, auf der rechten Seite im Fenster „Angewendete Schritte“ den betreffenden Schritt auszuwählen und durch einen Klick auf das Löschen-Zeichen (X) zurückzusetzen.

Du kannst dabei durch die einzelnen Schritte durchklicken und bekommst je nachdem, welchen Anwendungsschritt du gerade ausgewählt bzw. markiert hast, die jeweilige Vorschau darauf, was sich in der jeweiligen Abfrage befindet. Auf diese Weise kannst du also Abfragen nachträglich bearbeiten und verändern bzw. Schritte zur Gänze rückgängig machen.

Power-Query bietet dir darüber hinaus auch die Möglichkeit, bereits bestehende Abfragen zu duplizieren oder Verweise darauf zu erstellen [siehe Video ab 8:00 min].

Duplizieren

Das Duplizieren bietet dir die Gelegenheit, mit einer Datei Verschiedenes auszuprobieren, ohne dass du dabei befürchten musst, in der ursprünglichen Abfrage versehentlich etwas zu zerstören.

Wenn du hingegen mittels Rechtsklick einen sogenannten „Verweis“ erstellst, dann ist die auf diese Weise erzeugte neue Datei keine unabhängige Kopie (wie im Fall des Duplizierens), sondern – wie der Name bereits sagt – ein Verweis auf die Originalabfrage, also eine Verknüpfung. Das bedeutet, dass sämtliche Änderungen in der Ursprungsabfrage automatisch an den Verweis weitergegeben werden. Das ist insofern ganz praktisch, als bei Änderungen oder Erweiterungen im Original keine neuerliche Abfrage erstellt werden muss, sondern dass die jeweiligen Adaptionen entsprechend weitergegeben bzw. übernommen werden.

Das waren also die verschiedenen Möglichkeiten, um Daten aus Arbeitsmappen zu ziehen und anschließend bequem im Power-Query-Editor weiterzuverarbeiten.

5) Daten aus gesamten Ordnern abfragen

Abonniere, Daumen hoch und deine Fragen in die Comments 😉

Das absolut Beste, was dir Power-Query bietet, ist die Möglichkeit, Daten nicht nur aus einzelnen Dateien, sondern sogar aus gesamten Ordnern abzufragen. Wenn du also einen Ordner mit mehreren Dateien hast, dann kannst du mit nur einer einzigen Abfrage die Daten aus sämtlichen Dateien in dem betreffenden Ordner abrufen und in Excel zur Verfügung stellen.

Das Tolle daran ist aber der Umstand, dass für den Fall, dass in dem abgefragten Ordner neue Dateien abgelegt werden, die einmal erstellte Abfrage automatisch erweitert wird, sodass dir bei jedem Öffnen der Abfrage auch sämtliche neuen Daten zur Verfügung stehen.

Das ist beispielsweise sehr praktisch bei monatlichen Reportings, also immer, wenn aus einem anderen System wie z. B. SAP neue Daten eingespielt werden. Diese Daten werden genommen und in dem betreffenden Ordner abgelegt, und bei Aktualisierung der Abfrage werden diese neu hinzugekommenen Daten eingespielt. Auf Basis dieser Daten können dann Pivot-Tabellen oder Pivot-Charts erstellt werden, um die neuen Informationen auszuwerten und als Grafiken darzustellen. Dadurch lassen sich z. B. die bereits zuvor erwähnten monatlichen Reportings mit nur einem einzigen Klick aktualisieren.

Eine solche Abfrage erstellst du über „Daten“ / „Daten abrufen“ / „Aus Datei“ / „Aus Ordner“.

Aus Ordner

Über den folgenden Dialog „Ordner“ kannst du entweder auf „Durchsuchen“ gehen, um zum File-Explorer zu gelangen und den gewünschten Ordner auszuwählen, …

Durchsuchen

… oder du gibst den Pfad gleich direkt ein und klickst anschließend auf „OK“.

Pfadangabe

Daraufhin öffnet sich ein Vorschaufenster, in dem du die verschiedenen Dateien vorfindest, die in dem angewählten Ordner abgelegt wurden. Neben den Dateinamen und den File-Endungen findest du auch noch Informationen, wann die jeweiligen Dateien erstellt oder geändert wurden und wann zuletzt auf diese Daten zugegriffen wurde. Darüber hinaus ist auch der Dateipfad angeführt.

Kombinieren

Hier stehen dir nun verschiedene Möglichkeiten zur Verfügung. Wenn du das Ganze bearbeiten möchtest, öffnet sich wiederum das Power-Query-Fenster. Würdest du auf „Laden“ klicken, so würdest du nur eine Ansicht dieser Dateien bekommen, das heißt, die einzelnen Dateien würden noch nicht miteinander kombiniert werden.

Du kannst aber auch auf „Kombinieren und bearbeiten“ klicken. Dann wird Power-Query versuchen, die Dateien, die sich innerhalb des betreffenden Ordners befinden, miteinander zu verbinden und zu kombinieren.

Kombinieren und bearbeiten

Hier kannst du dann u. a. entscheiden, welche Dateien des Ordners und auch welche Datenblätter innerhalb der einzelnen Dateien selbst für die gewünschte Abfrage verwendet werden sollen. Wie du Power-Query dazu bringst, die Abfrage auf die einzelnen Dateien auszuführen und das Ganze automatisch zu kombinieren, kannst du dir im Video [2:16–3:02 min] ansehen.

Abfrage in Power Query Editor

Sobald Power-Query den Ordner geladen hat, siehst du auf der linken Seite im Fenster „Abfragen“ ganz unten die neue Abfrage „Monatsdaten“ und auch einen Bereich mit einer Beispielabfrage, der automatisch generiert wird. Diesen kannst du aber fürs Erste einfach ignorieren und zuklappen.

Wenn du die Abfrage „Monatsdaten“ anklickst, siehst du auf der linken Seite die Spalte „Source.Name“, also die Namen jener Dateien, die du zuvor für die Abfrage ausgewählt hast. Daneben findest du mehrere Spaltenköpfe, wo Power-Query erkennt, dass es sich um wiederkehrende Spalten handelt, und die Namen entsprechend einpflegt. Darunter siehst du dann die einzelnen Datensätze.

Abfrage in Power Query Editor

Innerhalb der aufgelisteten Datensätze siehst du aber auch noch jede Menge leere Zeilen, die mit „null“ gekennzeichnet sind, die in unserem Ergebnis jedoch nicht aufscheinen sollen und die wir daher noch ausfiltern wollen.

Wenn du beispielsweise die Spalte „Jahr“ anwählst und danach das Filterfeld öffnest, indem du auf das kleine Dreieck klickst, …

Sortieren

… kannst du anschließend das Häkchen in dem kleinen Kästchen links neben „(NULL)“ entfernen.

Die Zeilen mit den leeren Einträgen werden daraufhin ausgefiltert und der Vorgang selbst wird auf der rechten Seite im Fenster „Angewendete Schritte“ angezeigt, in unserem Fall mit der Beschreibung „Gefilterte Zeilen“.

Filtern

Über die Vielzahl an Möglichkeiten, wie du die Daten transformieren und abändern kannst, erfährst du später im Kurs noch mehr.

Sobald du den fertigen Datensatz vor dir hast, kannst du das Ganze mittels „Schließen & laden“ (Button links oben) in eine Excel-Tabelle übertragen. Rechts findest du wiederum die bereits oben erwähnte Beispielabfrage, die du allerdings fürs Erste ebenfalls wegklappen kannst.

Zuklappen

Sobald du auf eine Abfrage rechts im Fenster „Abfragen und Verbindungen“ klickst, wird auch der betreffende Bereich in der Exceltabelle links markiert.

Reduziert

Wie du nun diese Daten auswertest, um daraus beispielsweise eine Pivot-Tabelle und ein Pivot-Chart zu erstellen, und wie du deine Abfrage aktualisierst, sobald neue Dateien in deinem Ordner abgelegt wurden, kannst du dir im Video ansehen [5:05–6:50 min].

Auf diese Weise hilft dir also Power-Query dabei, beispielsweise die oben angesprochenen monatlichen Reportings nachhaltig zu gestalten und relativ schnell mit wenigen Klicks zu aktualisieren.

Die so erstellten Grafiken können auch als Verknüpfung in eine PowerPoint-Präsentationen eingefügt (also nicht hineinkopiert) werden. Sobald sich die Abfragedaten geändert haben, musst du dann nur noch die Verknüpfung mit Rechtsklick auf die betreffende Grafik aktualisieren bzw. beim Öffnen der PowerPoint-Datei alle Datenverbindungen aktualisieren.

Mit dieser Zugriffsmöglichkeit auf einen Ordner bzw. auf sämtliche in dem betreffenden Ordner enthaltenen Dateien kannst du neue Daten relativ schnell auswerten, weiterverarbeiten und diese zu einem gewünschten Output überführen. Bei regelmäßigen und wiederkehrenden Arbeitsschritten hast du also mithilfe dieser mächtigen Funktionalität von Power-Query die Möglichkeit, nicht nur einzelne Dateien, sondern ganze Ordner abzufragen, und kannst dir damit jede Menge Zeit einsparen.

6) Power Query und Power BI

Daten abrufen und transformieren ist nicht nur eine Funktion von Power Query. Wenn du Power Query beherrschst, dann wird dir der Einstieg bei Power BI leicht fallen, denn bei Power BI besteht der erste Arbeitsschritt auch daraus Daten abzurufen und dann zu transformieren. Das Tool Power Query ist in Power BI aufgegangen und daher funktioniert das Daten abrufen und transformieren bei beiden Tools gleich. Eine kleine Einführung zum Thema Power BI findest Du hier.

7) Anwendungsbeispiele für Power Query