Grohrock.consulting

Excel, Datenbanken und mehr

Schlagwort: PowerQuery

Power Query: Zusammengesetzte Schlüssel

Wie Tabellen miteinander verknüpft werden können habe ich in einem anderen Eintrag schon angesprochen, aber was ist, wenn eine Spalte nicht mehr ausreicht, um zwei Tabellen zu verknüpfen. Zu theoretisch? Ok ein praktisches Beispiel.

Beispieltabellen

Als Ausgangslage haben wir zwei Tabellen, die zu einer Web-basierten Software gehören.In einer Tabelle haben wir aggregierte Nutzerstatistiken, in der anderen haben wir Daten zu Micropayments aus unserer Vertriebssoftware. Wie immer gilt: in der optimalen Welt hätten wir das direkt mit einer Datenbankabfrage so zusammenbekommen, nämlich folgendermaßen:

SELECT m.date_key, m.feature, m.sales, m.sales_eure, n.users
 FROM Micropayments m
 INNER JOIN Nutzung n
 ON m.date_key = n.date_key AND m.feature = n.feature

Zusammengesetzten Schlüssel erstellen

Wo sich bei einer SQL-Abfrage mehrere Bedingungen einbringen lassen, bietet PowerQuery diese Möglichkeit leider nicht. Der Grund hierfür ist einfach, dass als Argumente für den Join lediglich Spaltennamen übergeben werden können. Das bedeutet, dass wir zunächst einen neuen Schlüssel erstellen müssen. Dazu erstellen wir in beiden Abfragen die benutzerdefinierte Spalte „date_keyFeature“ mit folgender Formel:

= Text.From([date_key]) & [feature]

Mit diesem Schritt bekommen wir eine neue Spalte mit eindeutigen Schlüsseln für die Kombination aus Datum und Feature.

Tabellen zusammenfügen

Jetzt steht dem Zusammenfügen nichts mehr im Wege, indem wir die neu erstellte Spalte „date_keyFeature“ auswählen und dann die neue Tabelle z.B. um „sales“ oder „sales_euro“ erweitern. Aussehen sollte es dann ungefähr folgendermaßen:

Die Hilfsspalte kann auch noch entfernt werden, da sie bei der weiteren Analyse keinen wirklichen Mehrwert bringt und fertig ist unsere Kombination mittels zusammengesetztem Schlüssel.

Fragen? Einfach kommentieren.

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:

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

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