Einführung in Power-Query – Teil 2 7

Teil 2 meiner kleinen Einführung in das mächtige Tool namens Power Query
 

Nach dem ersten Teil der Einführung in Power Query von letzter Woche geht es diese Woche gleich mit Teil 2 weiter.

Heute zeige ich dir unter anderem, wie man mehrere Quelltabellen ruckizucki miteinander verknüpft. Wenn dir in der Vergangenheit für diesen Zweck mit SVERWEIS bei wirklich großen Tabellen die Füße eingeschlafen sind, dann wirst du von den Möglichkeiten begeistert sein, die Power Query an dieser Stelle bietet.

Und so geht’s:

Für das heutige Beispiel habe ich mir ein paar Daten zum weltweiten Energieverbrauch vom Statistik-Portal UNdata der Vereinten Nationen heruntergeladen. Wenn du mit den gleichen Beispiel-Daten arbeiten möchtest, kannst du dir meine Extrakte auch direkt hier herunterladen.

Zwei Datenquellen nutzen

Laden wir also die erste Datei in Power Query, es handelt sich dabei um eine CSV-Datei mit den Energieverbrauchswerten der Privathaushalte:

Eine CSV-Datei als Datenquelle für Power Query

Eine CSV-Datei als Datenquelle für Power Query

Nach wenigen Momenten wird der Abfrage-Editor geöffnet sein und das Ergebnis der Abfrage wird angezeigt. Wie ich schon im ersten Teil dieser Einführung beschrieben habe, wendet Power Query häufig automatisch gewisse Transformationen an. So auch in diesem Fall, wie man rechts außen in der Liste der angewendeten Schritte erkennen kann:

Die automatisch angewendeten Schritte

Die automatisch angewendeten Schritte

Im letzten Schritt (“Geänderter Typ”) wurde das Datenformat der Spalte “Quantity” verändert, was aufgrund des Quelldatenformates zu falschen Werten führt. In der CSV-Datei wurden die Dezimalstellen durch einen Punkt getrennt und nicht durch das bei uns übliche Komma. Wenn man auf den vorletzten Schritt klickt, sieht man die Werte vor der Typänderung:

Der Punkt als Dezimaltrenner

Der Punkt als Dezimaltrenner

Daher entferne ich den letzten angewendeten Schritt durch einen Klick auf das rote X-Symbol und erhalte damit folgendes Abfrageergebnis:

Der letzte Schritt wurde entfernt

Der letzte Schritt wurde entfernt

Als nächstes fragen wir eine zweite Datenquelle ab. Auch hier handelt es sich um eine CSV-Datei, diesmal mit den Gesamt-Energieverbrauchswerten. Diese Datenquelle können wir direkt in Power Query ansprechen:

Eine weitere CSV-Datei als Quelle angeben

Eine weitere CSV-Datei als Quelle angeben

Der Aufbau der Quelldatei ist identisch mit dem der ersten Datei, daher entferne ich auch hier wieder den letzten angewendeten Schritt. Was man jetzt im linken Teil des Abfrage-Editors sehen kann: Es sind zwei Abfragen geöffnet, zwischen denen ich durch einfaches Anklicken umschalten kann:

Zwei geöffnete Abfragen

Zwei geöffnete Abfragen

Im nächsten Schritt möchte ich die Inhalte der beiden Abfragen zusammenfassen, so dass die Werte für den Privatverbrauch und den Gesamtverbrauch in einer einzigen Tabelle nebeneinander erscheinen. In einer reinen Excel-Lösung (ohne Power Query) würde man das z.B. per SVERWEIS-Funktion erledigen. Wenn es sich jedoch um sehr große Tabellen handelt, kann das durchaus zu einem Geduldsspiel werden oder im Extremfall Excel komplett in die Knie zwingen.

Und genau hier liegt eine der großen Stärken von Power Query!

Abfragen miteinander verknüpfen

Ich werde jetzt die beiden Abfragen zu einer einzigen zusammenzuführen. Dabei gibt es grundsätzlich zwei Möglichkeiten:

  1. Abfragen anfügen. Damit werden die Ergebnisse einer Abfrage einfach um Ende einer anderen Abfrage angehängt
  2. Abfragen zusammenführen. Vereinfacht gesprochen werden damit die Spalten einer Abfrage neben die vorhandenen Spalten einer anderen Abfrage gestellt
  3. Mehrere Abfragen kombinieren

    Mehrere Abfragen kombinieren

    Letzteres werde ich nutzen.

    Wer sich ein wenig mit Datenbanken auskennt, kennt den Begriff des “Join”. Damit können mehrere Datenbanktabellen mit Hilfe eines gemeinsamen Feldes miteinander verknüpft werden. Auch der bekannte SVERWEIS macht nichts anderes. Und in Power Query setzen wir die gleiche Technik ein:

    v

    Zwei Abfragen zusammenführen

    In meinem Beispiel habe ich auf der linken Seite die Abfrage mit den privaten Verbrauchsdaten markiert. Daher wird diese Tabelle auch im oberen Bereich des Fensters “Zusammenführen” angezeigt.

    In dem darunterliegenden Auswahlfeld markiere ich nun die zweite Tabelle mit den Gesamtverbrauchswerten und bekomme dann eine kleine Vorschau im unteren Bereich des Fensters angezeigt:

    Auswahl der zweiten Abfrage

    Auswahl der zweiten Abfrage

    Nun muss ich noch angeben, über welches Feld bzw. welche Felder ich die beiden Tabellen “joinen” möchte. In vielen Fällen wirst du hier mit einem einzigen Feld auskommen. Klassische Felder sind Artikel- oder Kundennummern oder sonstige eindeutige Schlüsselfelder.

    In unserem Fall verwenden wir das Land als gemeinsames Feld. Dies reicht jedoch noch nicht aus, da es für jedes Land mehrere Datensätze gibt, nämlich einen pro Jahr. Für eine eindeutige Zuordnung brauchen wir also die Jahresspalte als zweites Verknüpfungsfeld. Für die eigentliche Verknüpfung muss ich nur diese Spalten in den beiden Vorschautabellen anklicken. Dabei muss die STRG-Taste gedrückt und gehalten werden:

    Übereinstimmende Spalten wählen

    Übereinstimmende Spalten wählen

    Ein Klick auf den OK-Button führt die Abfrage aus (auf die unterschiedlichen Join-Arten gehe ich hier nicht ein). Außer einem weiteren Eintrag in der Liste der angewendeten Schritte sowie einer unscheinbaren zusätzlichen Spalte namens “NewColumn” sieht man jedoch noch wenig:

    Die zusammengeführte Abfrage

    Die zusammengeführte Abfrage

    Hinter dem unscheinbaren Symbol neben der neuen Spalte verbirgt sich jedoch das, was wir suchen, nämlich alle Spalten aus der zweiten Tabelle:

    Gewünschte Spalten wählen

    Gewünschte Spalten wählen

    Da uns nur die Verbrauchsmengen interessieren, wähle ich alle Spalten bis auf “Quantity” ab und klicke dann auf “OK”. Damit habe ich in meiner ersten Abfrage mit dem Privatverbrauch eine neue Spalte, die den dazu passenden Gesamtverbrauch an Energie enthält:

    Die neue Mengenspalte

    Die neue Mengenspalte

    Bevor wir die Daten zurück an Excel liefern, wollen wir aber die Ergebnisse mit Hilfe einiger Transformationen noch etwas “aufhübschen”.

    Verschiedene Transformationen durchführen

    Zuerst entfernen wir die überflüssige Spalte “Quantity Footnotes”. Dazu markiere ich die betreffende Spalte über Spaltenkopf und klicke dann die Schaltfläche “Spalten entfernen”:

    Überflüssige Spalte entfernen

    Überflüssige Spalte entfernen

    Als nächstes knöpfen wir uns das Format der beiden Quantity-Spalten vor. Ich markiere beide Spalten und rufe aus dem Menüband die Option “Werte ersetzen auf:

    Werte ersetzen

    Werte ersetzen

    Dann wird der Punkt durch ein Komma ersetzt. In einem zweiten Schritt kann ich den Datentyp jetzt von Text in Dezimalzahlen umwandeln:

    Den Datentyp umwandeln

    Den Datentyp umwandeln

    Über einen Doppelklick auf die Spaltenüberschrift kann ich die beiden Spalten noch umbenennen und einen aussagekräftigeren Namen vergeben:

    Spaltenbezeichnungen ändern

    Spaltenbezeichnungen ändern

    Aus eins mach zwei

    Hier nicht zwingend notwendig, aber gut zu wissen, wie es geht: Ein Spalte in zwei Spalten aufteilen. Dies möchte ich noch kurz am Beispiel der Spalte “Commodity – Transaction” demonstrieren. Dazu markieren wir die Spalte und wählen im Menüband die Schaltfläche “Spalte teilen” und dort die Option “Nach Trennzeichen” aus:

    Eine Spalte aufteilen

    Eine Spalte aufteilen

    In dem folgenden Fenster kann ich aus der Auswahlliste ein vordefiniertes Trennzeichen, wie Komma oder Semikolon wählen oder ein benutzerdefiniertes Trennzeichen angeben. Der Clou dabei: es lassen sich auch ganze Wörter als Trenner angeben:

    Eigene Trennzeichen definieren

    Eigene Trennzeichen definieren

    Nach einem Klick auf OK wird die Spalte aufgeteilt, wobei das als Trennzeichen angegebene “by” verschwindet:

    Die aufgeteilte Spalte

    Die aufgeteilte Spalte

    Die Schaltfläche “Schließen&Laden” in der Registerkarte “Start” liefert das fertige Endergebnis zurück nach Excel, wo es nach Belieben weiterverarbeitet werden kann:

    Die formatierte Tabelle in Excel

    Die formatierte Tabelle in Excel

    Und damit bin ich am Ende meiner kleinen Einführung in Power Query. Das war sicherlich nur ein kleiner Ausschnitt der Möglichkeiten, aber ich hoffe, du bist ein wenig auf den Geschmack gekommen und experimentierst demnächst selbst mit diesem mächtigen Werkzeug herum.

    Oder gehörst du bereits zu denjenigen, die Power Query bereits nutzen? Dann teile deine Erfahrungen unten in den Kommentaren mit uns.

     

    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.

7 Gedanken zu “Einführung in Power-Query – Teil 2

  • Helmut Schuster

    Hallo Herr Weiß, ein sehr schöner und informativer Artikel. Es stimmt, PowerQuery ist ein mächtiges Tool. In meinen Excel/PowerPivot-Projekten ist es das Mittel der Wahl um Daten vor dem Import zu bearbeiten und komfortabel zu importieren und das auch permanent ohne zusätzlichen Aufwand zu wiederholen. Ich kann den Einsatz nur empfehlen!

    • Martin Weiß Beitragsautor

      Hallo Herr Schuster,

      vielen Dank für das nette Feedback. Power Query wurde auch von mir lange Zeit ignoriert und fristet vermutlich bei vielen Anwendern ein Schattendasein. Und gerade den letzten von Ihnen genannten Punkt kann ich nur unterstreichen: Man muss den Aufwand eben nur ein einziges Mal betreiben und kann immer wieder davon profitieren.

      Schöne Grüße,
      Martin Weiß

  • Wiebke

    Hallo Herr Weiß,
    vielen Dank an der Stelle für die Einführung in Power-Query. Das Tool ist echt toll und importiert im Handumdrehen!
    Eine Sache habe ich aber noch nicht gefunden. Vielleicht suche ich aber auch an den falschen Stellen.
    Überschriften anpassen: Ich habe ein txt-file mit den Überschriften Column1, Column2 etc. Nicht sehr aussagekräftig.
    Stattdessen müsste hier stehen: Monat, Jahr, Bereich etc.
    Wo kann ich Excel das beibringen? Die Überschriften im Nachgang anpassen ist zwar möglich, aber nicht wirklich schick und hält auch nur bis zur nächsten Aktualisierung. 🙁

    • Martin Weiß Beitragsautor

      Hallo Wiebke,

      jede Änderung, also auch die manuelle Anpassung der Überschriften, wird als eigener Schritt aufgezeichnet und erscheint dann auch rechts in der Liste de angewendeten Schritte. Und bei der nächsten Aktualisierung (z.B. über die Schaltfläche “Vorschau aktualisieren”) werden alle diese Schritte wieder durchgeführt. Somit müssen Sie sich die Arbeit auch nur beim ersten Mal machen, danach geht’s automatisch.

      Schöne Grüße,
      Martin

  • Tim

    Hallo,
    ich habe ein Problem mit PowerQuery, aber ich bin mir sicher, dass es das richtige Tool ist.
    Ich bekomme regelmäßig Daten einer Umfrage zur Auswertung. Um diese auszuwerten, sollen die Daten nach dem Umfrageort gruppiert werden. Also z.B. habe ich 100 Umfragen gemacht, davon waren 10 in Hamburg, 30 in Berlin und 60 in Bottrop. Ich möchte also gar keine gesamte Auswertung, sondern, dass mir PowerQuery das in alle unterschiedlichen Orte unterteilt und ich dann damit weiter arbeiten kann. So etwas, als habe man dann verschiedene Tabellenblätter mit den unterschiedlichen Orten.
    Mein Ursprungsgedanke war natürlich der SVERWEIS, aber Powerquery sollte das doch hinbekommen?!
    Danke schon mal und schöne Grüße

    • Martin Weiß Beitragsautor

      Hallo Tim,

      ich weiß leider auch nicht, wie sich eine einzelne Abfrage in PowerQuery auf mehrere Tabellen aufteilen lässt. Aber vielleicht hilft ja der folgende Weg:
      – Quelldaten per Datenverbindung in Excel importieren und dort als Tabelle formatieren. Nennen wir sie mal “Quelltabelle”
      – aus dieser Tabelle eine neue Abfrage in PowerQuery erstellen, die Daten nach dem gewünschten Ort filtern und die Ergebnisse nach Excel zurückliefern. Die Daten landen dort in einem eigenen Arbeitsblatt
      – Aus dem Blatt “Quelltabelle” eine neue Abfrage in PowerQuery erstellen, nach dem nächsten Ort filtern usw.

      Damit gibt es für jeden Ort ein eigenes Arbeitsblatt, welches bei Änderung der Quelldaten auch automatisch aktualisiert wird. Allerdings gebe ich zu, dass das keine sehr elegante Methode ist und bei sehr vielen verschiedenen Orten auch mit etwas Aufwand verbunden ist (zumindest einmalig).

      Aber vielleicht kennt ein anderer Leser eine bessere und einfachere Methode.

      Schöne Grüße,
      Martin

      • Tim

        Hallo Martin,

        vielen Dank für die Antwort, klappt wunderbar. Und das ist zwar, wie du geschrieben hast, mit etwas Aufwand verbunden, aber wenn Excel alles von alleine machen würde, wäre es ja auch langweilig. 🙂

        Schöne Grüße,
        -Tim