Grohrock.data

Excel, Datenbanken und mehr

Monat: März 2015

Power Query: Eine Einführung (Teil 2)

Quellen zusammenführen

Neben dem Import von Daten kann Power Query aber noch einiges mehr. In der Theorie haben wir natürlich das perfekte Data Warehouse, welches alle Daten in aufbereiteter Form zur Verfügung stellt, aber die Praxis zeigt doch immer wieder, dass Ad-Hoc-Berichte aus Bestandsdaten gemischt mit anderen Quellen erstellt werden müssen.

Wir erinnern uns an den externen Parter aus Teil 1, der uns die CSV-Dateien zugesandt hat. Wir wollen nun die Austausch-Kunden-ID durch die echte ersetzen. Letztere wird in unserem DWH zusammen mit anderen Informationen zum Kunden gespeichert und könnte nach einem ersten Import mit Power Query etwa folgendermaßen aussehen:

Power Query-Fenster 3

Verwenden wir nun die Funktion „zusammenführen“ um zwei Abfragen zu kombinieren. Im ersten Schritt müssen die Spalten ausgewählt werden, auf deren Basis ein Bezug hergestellt wird. In unserem Fall ist das die Übergangs-ID. Dann können wir uns entscheiden, ob alle Daten verwendet werden sollen oder nur Übereinstimmungen. Wir entscheiden uns hier für Option 2:

Power Query-Fenster 4

Was wir nun erhalten ist eine neue Abfrage, in der wir die zu übernehmenden Spalten aus der angefügten Tabelle selektieren können; im Beispiel hier waren das Name und Vorname. Das Ergebnis sieht dann so aus:

Power Query-Fenster 5

Mit Power Query haben wir also eine großartige Möglichkeit, Daten aus verschiedenen Quellen zusammenzuführen. Im Gegensatz zum SVERWEIS ist diese Möglichkeit auch wesentlich stabiler und flexibler.

Fazit

Power Query ist zwar nicht die perfekte Basis für eine unternehmensweite Strategie im Berichtswesen, aber es ist ein gutes Werkzeug, wenn schnell und auf unkomplizierte Weise Datenquellen importiert und verknüpft werden müssen.

In weiteren Beiträgen werde ich Schritt für Schritt zeigen, wie man Daten aus verschiedenen Quellen importieren, ändern und zusammenführen kann.

Fragen, Anregungen, Kritik, Wünsche? Hinterlasst mir einfach einen Kommentar und ich schaue, was ich tun kann.

Power Query: Eine Einführung (Teil 1)

Was ist Power Query?

Seit einiger Zeit bietet Microsoft für alle Besitzer von Office Professional Plus das Excel-Addon Power Query an. Beworben wird es als DIE Lösung für den Import und das Verschmelzen von Daten aus verschiedenen Quellen.

Die werksmäßig mitgelieferte Unterstützung von Datenimporten beschränkt sich bei Excel hauptsächlich auf Microsoft-Quellen und Textdateien. Power Query hingegen bietet die Möglichkeit, Daten aus verschiedenen Quellen zu importieren:

  • MySQL
  • Oracle
  • Sybase
  • Salesforce
  • Hadoop
  • etc.

Quellen bearbeiten

Einige Formate, wie z. B. CSV, können schon jetzt von Excel importiert werden. Die Anpassung muss dann aber in einem zweiten Schritt erfolgen. Mit Power Query können Anpassungen schon während des Imports vorgenommen werden, wodurch das Endprodukt schon sauber zur Verfügung steht.

Nehmen wir dazu ein einfaches Beispiel. Ein externer Partner verkauft Wartungsverträge an Bestandskunden. Jeden Tag erhalten wir eine CSV-Datei mit den folgenden Informationen:

  • Kunden-ID: ######## (int)
  • Verkaufsdatum: yyyymmdd (int)
  • Agenten-ID: ### (int)
  • ID des verkauften Vertrags: ###### (int)

Ein normaler Import würde den folgenden Datensatz produzieren:

Customer ID Date Agent ID Contract ID
52689425 20150228 23 653392
23485621 20150228 12 653392
13894651 20150228 15 653393
12346925 20150228 23 653392
96423678 20150228 16 653393
32649821 20150228 16 653392
36485196 20150229 23 653392
73915864 20150229 12 653392
67895126 20150301 15 653393

Auffällig ist zunächst das rein ID-basierte Datenformat. Zusätzlich nehmen wir jetzt an, dass wir unserem Partner aus Datenschutzgründen lediglich eine Austausch-Kunden-ID übermitteln. Glücklicherweise bietet PowerQuery hier genau die Möglichkeit, die wir brauchen. Ich möchte an dieser Stelle nur einen kurzen Überblick verschaffen, über die genauen Schritte werde ich in weiteren Artikeln eingehen. So in etwa könnten unsere Daten nach dem Import aussehen:

Power Query-Fenster 1

Ein paar Änderungen später bekommen wir dann folgendes heraus:

Power Query-Fenster 2

Im Prinzip ist das nichts, was man nicht auch mit Bordmitteln von Excel erreichen könnte, aber mit einem sehr klaren Vorteil. Nach einem Klick auf „Schließen & Laden“ werden die Daten direkt in das Datenmodell von Excel importiert und falls später neue Datensätze in der CSV-Datei hinzukommen, genügt eine einfache Aktualisierung, damit diese ebenfalls geladen werden; inklusive aller Veränderungen.

In Teil 2: Quellen zusammenführen werde ich auf die Möglichkeit eingehen, Daten mit Hilfe von PowerQuery zu verknüpfen.

Läuft mit WordPress & Theme erstellt von Anders Norén