Einführung in Power-Query – Teil 1 5

Aller Anfang ist leicht: Erste Schritte mit dem mächtigen Instrument "Power Query"
 

Heute stelle ich dir ein mächtiges Instrument vor, mit dem die Möglichkeiten von Excel stark erweitert werden. Mit der Excel-Version 2013 wurden von Microsoft zusätzliche Werkzeuge bereitgestellt, um unter dem Schlagwort “Business Intelligence” den Umgang mit großen Datenmengen zu erleichtern.

Eines dieser Instrumente heißt Power Query für Excel.

Es handelt sich dabei um ein sogenanntes Add-In, welches für Excel 2010 und 2013 zur Verfügung steht und in die neue Excel-Version 2016 bereits integriert ist.

Die schlechte Nachricht: Für Anwender der Excel-Versionen 2007 und älter ist hier leider Schluss. Dort wird dieses mächtige Add-In leider nicht unterstützt. Vielleicht ein Grund mehr, auf eine aktuelle Excel-Version umzusteigen.

Das Power-Query Add-In installieren

Wenn du bereits Excel 2016 im Einsatz hast, dann kannst du diesen Abschnitt überspringen. Power-Query gehört hier bereits zum Standardfunktionsumfang und ist im Register “Daten” in der Werkzeuggruppe “Abrufen und Transformieren” integriert.

Anwender von Excel 2010 und 2013 können sich das Add-In kostenlos auf der Seite von Microsoft herunterladen:
Microsoft Power Query für Excel

Dort musst du nur noch die zu Deiner Excel-Version passende MSI-Datei auswählen (32 Bit oder 64 Bit). Ein Doppelklick auf die heruntergeladene Datei startet die Installation. Excel darf dabei nicht geöffnet sein:

Add-In installieren

Add-In installieren

Nach dem obligatorischen Akzeptieren der Lizenzrichtlinien und der Bestätigung des vorgeschlagenen Installationsortes geht die eigentliche Installation in wenigen Sekunden über die Bühne.

Wenn du nun Excel startest, sollte im Menüband eine neue Registerkarte “Power Query” sichtbar sein:

Die neue Registerkarte "Power Query"

Die neue Registerkarte “Power Query”

Sollte die Registerkarte fehlen, dann muss das Add-In erst aktiviert werden. Dazu öffnen wir die Excel-Optionen und wählen die Kategorie “Add-Ins”:

COM-Add-In aktivieren

COM-Add-In aktivieren

Unten im Fenster muss in der Auswahlliste “Verwalten” die Option “COM-Add-Ins” ausgewählt und mit Klick auf den Gehe zu-Knopf aufgerufen werden. Daraufhin öffnet sich ein Fenster, welches alle bereits installierten COM-Add-Ins auflistet. Hier muss nur noch der Haken vor das Power Query-Add-In gesetzt werden:

Power Query aktivieren

Power Query aktivieren

Was kann man jetzt damit machen?

Power Query ist im Grunde genommen ein sogenanntes ETL-Tool (Extract – Transform – Load). Es geht also darum, Daten aus unterschiedlichen Quellen zu extrahieren, nach gewissen Regeln umzuwandeln und anschließend in eine Excel-Tabelle zu laden.

Hier eine kleine Auswahl der möglichen Datenquellen und -formate, mit denen Power Query umgehen kann:

  • Excel-Dateien
  • CSV
  • XML
  • Text-Dateien
  • Access-Datenbanken
  • SQL-Datenbanken
  • Microsoft Azure Marketplace
  • Webseiten
  • Facebook (ja, du hast richtig gelesen!)

… und einiges mehr.

“Externe Daten in Excel laden kann ich doch auch ohne Power Query”

Das ist zum Teil richtig. Aber nicht annähernd so leistungsfähig, wie mit Power Query. Eine der vielen Stärken dieses Add-In’s liegt im Umgang mit großen Datenmengen, bei dem ein “nacktes” Excel in die Knie gehen würde.

Aber eins nach dem anderen. In diesem Artikel wollen wir uns den grundlegenden Umgang mit diesem mächtigen Instrument ansehen.

Power Query – das erste Mal

Ich habe dazu von Yahoo-Finance eine CSV-Datei mit historischen DAX-Werten heruntergeladen. Du kannst dir auf Yahoo selbst die entsprechenden Daten generieren (hier der Link dazu) oder die von mir schon vorbereitete CSV-Datei hier herunterladen.

Die Original-Tabelle auf Yahoo sieht so aus:

Yahoo: Historische Dax-Werte

Yahoo: Historische Dax-Werte

In der Registerkarte “Power Query” klicke ich die Schaltfläche “Aus Datei” und wähle dort die Option “Aus CSV laden”:

CSV-Datei als Datenquelle

CSV-Datei als Datenquelle

Nachdem ich die Datei ausgewählt habe, wird sie nach wenigen Augenblicken in einem neuen Fenster – dem Abfrage-Editor – angezeigt:

Der Abfrage-Editor in  Power Query

Der Abfrage-Editor in Power Query

Bevor wir uns näher mit dem Editor beschäftigen werden, springt eine Sache gleich ins Auge. Wenn wir die ursprüngliche Ansicht auf Yahoo mit der jetzt dargestellten Tabelle vergleichen, dann werden hier ganz offensichtlich die DAX-Werte nicht korrekt dargestellt:

Da passt etwas nicht ganz...

Da passt etwas nicht ganz…

Wenn man die importierte CSV-Datei in einem Texteditor öffnet, sieht man auch das Original-Format:

CSV-Datei im Texteditor

CSV-Datei im Texteditor

Das geht ja schon gut los! wirst du jetzt sagen.

Zum Glück, sage ich. Denn damit kann ich gleich eine Besonderheit in Power Query demonstrieren, die du bald sehr schätzen wirst. Rechts neben der Tabelle gibt es einen Bereich namens Abfrageeinstellungen. Im unteren Kasten befindet sich eine Liste der angewendeten Schritte:

Liste der angewendeten Schritte

Liste der angewendeten Schritte

Dabei handelt es sich um ein Protokoll sämtlicher Aktivitäten, die an der Tabelle bisher vorgenommen wurden (genau genommen ist es keine Tabelle, sondern das Ergebnis einer Abfrage). Der letzte angezeigte Schritt “Geänderter Typ” besagt, dass am Datentyp etwas verändert wurde. Wenn ich nun den vorhergehenden Schritt mit der Maus anklicke, wird das Protokoll sozusagen um einen Schritt zurückgedreht und die Tabelle wird so angezeigt, wie sie vor der Typenänderung aussah:

Den letzten Schritt zurücknehmen

Den letzten Schritt zurücknehmen

Wir befinden uns jetzt auf dem Schritt “Höher gestufte Header”. Was hier passiert ist wird deutlich, wenn wir noch einen Schritt zurückgehen, also auf “Quelle”:

... und noch ein Schritt zurück

… und noch ein Schritt zurück

Power Query hat hier korrekt angenommen, dass die erste Zeile der Quelldatei die Spaltenüberschriften enthält und diese entsprechend in den Tabellenkopf übernommen. Bei der Interpretation des Zahlenformats war es mit der Intelligenz jedoch nicht so weit her, wie wir vorhin gesehen haben. Daher entferne ich den letzten Schritt in der Liste einfach, indem ich auf das Kreuz vor der Bezeichnung “Geänderter Typ” klicke:

Einen Schritt komplett entfernen

Einen Schritt komplett entfernen

Erste eigene Transformationen

Jetzt wollen wir unsere Tabelle ein wenig anpassen. Die letzte Spalte interessiert mich gar nicht und soll daher entfernt werden. Dazu klicke ich irgendwo in die betroffene Spalte oder markiere sie komplett über den Spaltenkopf und klicke dann auf die Schaltfläche “Spalten entfernen”:

Erste Transformation: Spalte löschen

Erste Transformation: Spalte löschen

Damit verschwindet die Spalte und es erscheint gleichzeitig ein neuer Eintrag in der Liste der angewendeten Schritte:

Das Ergebnis der Transformation

Das Ergebnis der Transformation

Solltest du jetzt versehentlich die falsche Spalte(n) gelöscht haben dann reicht es, diesen letzten Schritt zu entfernen, indem du auf das rote Kreuz vor “Entfernte Spalten” klickst. Probiere es aus!

Als nächstes wollen wir die Dax-Werte in einem vernünftigen Zahlenformat angezeigt bekommen. Da zumindest in Deutschland und Österreich die Dezimalstellen üblicherweise durch ein Komma getrennt werden, ersetzen wir die in der Liste vorhandenen Punkte durch ein Komma. Dazu klicke ich in der ersten Wertespalte oben auf den Spaltenkopf (“Open”), halte die Umschalt-Taste gedrückt und klicke dann auf den Spaltenkopf der letzten Spalte (“Volume”), um alle Wertespalten zu markieren:

Wertespalten markieren

Wertespalten markieren

In der Registerkarte “Transformieren” klicke ich jetzt die Schaltfläche “Werte ersetzen”. Im folgenden Fenster trage ich unter “Zu suchender Wert” den Punkt ein und im Feld “Ersetzen durch” das Komma:

Suchen und Ersetzen

Suchen und Ersetzen

Kommen wir nun zur letzten Transformation für heute und ändern noch den Datentyp von Text in Dezimalzahl:

Den Datentyp ändern

Den Datentyp ändern

Das soll für’s erste reichen. Auch hier wieder der Hinweis auf die Liste der angewendeten Schritte und damit die Möglichkeit, jede einzelne Transformation wieder rückgängig zu machen:

Das Ergebnis am Ende aller Transformationen

Das Ergebnis am Ende aller Transformationen

Und zurück zu Excel

Nachdem jetzt alle Änderungen gemacht sind, wollen wir die Ergebnisse zurück an Excel übergeben. Dies erfolgt über die Schaltfläche “Schließen & Laden” im Register “Start”:

Abfrageergebnisse an Excel übergeben

Abfrageergebnisse an Excel übergeben

Damit wird in Excel eine formatierte Tabelle erzeugt, die nun mit allen in Excel zur Verfügung stehenden Mitteln weiterverarbeitet werden kann. Gleichzeitig wird rechts außen die zugrunde liegende Power Query Abfrage gezeigt:

Eine formatierte Tabelle mit der Abfrage

Eine formatierte Tabelle mit der Abfrage

Ein Doppelklick auf den Abfragenamen bringt dich wieder zurück in den Abfrage-Editor.

Was bringt’s?

Verglichen mit einem direkten Import der CSV-Datei in Excel ergeben sich beim Einsatz von Power Query vor allem dann erhebliche Vorteile, wenn die Quelldaten regelmäßig aktualisiert werden.

Um beim Beispiel unserer Dax-Werte zu bleiben:
Angenommen, es wird täglich eine neue CSV-Datei mit gleichem Aufbau und unter gleichem Namen im Quellverzeichnis bereitgestellt, dann ist mit zwei Klicks die Datei in Excel aktualisiert – einschließlich aller vorgenommenen Anpassungen. Denn im Hintergrund arbeitet Power Query alle protokollierten Transformations-Schritte komplett ab.

Es reicht ein Rechtsklick auf den Abfragenamen und die Auswahl der Option “Aktualisieren”, um die neuesten Werte in deiner Excel-Tabelle zu erhalten:

Daten auf Knopfdruck aktualisieren

Daten auf Knopfdruck aktualisieren


Der heutige Artikel sollte einen kleine Vorgeschmack auf das geben, was mit Power Query alles möglich ist. Nächste Woche werden wir uns ansehen, wie man mit mehreren Quelldaten arbeitet und diese miteinander verknüpft.

Wie sagt der Bayer: Stay tuned! (Schmarrn: Dro bleim!)

 

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Kommentar erstellen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

5 Gedanken zu “Einführung in Power-Query – Teil 1

  • Patrizia Klois

    Hallo Martin,

    ich verfolge seit Kurzem deinen Newsletter und ich bin total begeistert, was man alles von dir lernen kann. Spitze, weiter so.
    Sag Bescheid, wenn dein neues Buch erscheint, dass du heute angekündigt hast, ich werde eine treue Leserin sein 🙂

    Nun zu meiner Frage:
    Hast du hier auch schon das Thema “Power Pivot” behandelt? Wenn ja, könntest du mir bitte einen Link schicken?

    Vielen Dank und viele Grüße aus dem Schwarzwald.

    • Martin Weiß Beitragsautor

      Hallo Patrizia,

      vielen Dank für das nette Feedback, das freut mich sehr. Das Buch wird noch eine ganze Weile dauern, denn ich habe erst damit angefangen. Die Veröffentlichung ist angepeilt für den Sommer nächsten Jahres und ich hoffe, Du hast noch so lange Geduld

      Das Thema Power Pivot habe ich auf dem Blog noch nicht behandelt. Der Grund ist relativ pragmatisch: Power Pivot ist leider nur in ganz bestimmten Excel-Versionen überhaupt verfügbar und damit für viele Anwender nicht erreichbar. Auch mir war das erst bewußt, als ich irgendwann meine Standalone-Excel-Version 2013 (wo es noch verfügbar war) durch eine Excel-365-Home-Version ersetzt habe, in der es nicht mehr enthalten ist (Power-Query hingegen schon…)

      Daher wird es dazu auf absehbare Zeit auch keinen Artikel geben.

      Schöne Grüße aus Bayern,
      Martin

    • Martin Weiß Beitragsautor

      Hallo Walter,

      ich habe es gerade nochmal getestet und bei mir hat es einwandfrei funktioniert. Wie hat sich das Problem bei Dir dargestellt: Gab es irgendwo eine Fehlermeldung? Von welcher Seite aus hattest Du versucht, Dich anzumelden?

      Wenn Du möchtest, kann ich Dich auch gerne manuell in den Verteiler aufnehmen. Einfach eine kurze E-Mail an info@tabellenexperte.de schicken.

      Schöne Grüße,
      Martin