Power Query ist ein neuartiges Add-In für Excel 2013 und Excel 2010 zur Extraktion, Transformation und zum Laden von Daten aus heterogenen Datenquellen direkt nach Excel (also eine ETL-Komponente).

Vereinfacht gesagt, Power Query ersetzt das in die Jahre gekommene Microsoft Query, bietet bisher in Excel nicht machbare Möglichkeiten der Datenanbindung und Datentransformation und bildet eine ideale ETL-Vorstufe für das Excel 2013 Data Model (= PowerPivot in Excel 2010).

Erstmalig released im Februar 2013 unter dem Codenamen Data Explorer, liegt nun seit kurzem die neueste Version von Power Query zum kostenlosen Download vor.

Nach der Installation des Add-Ins steht in Excel 2010/2013 das Power Query Ribbon zur Verfügung:

1. Daten aus verschiedensten Quellen nach Excel holen - oder einfach im Web suchen

Diese Funktionen sind grundsätzlich bereits aus der Datenanbindung eines (PowerPivot) Data Models bekannt, neben der Anbindung von (relationalen) Datenbanken, SharePoint Listen, OData-Feeds, normalen Dateien und Excel Tabellen selbst sind folgende neuen Funktionen verfügbar:

• Suche nach Daten im Web - viele nützliche statistische Informationen werden hier in Wikipedia gefunden ...
• Einlesen des Active Directory und Windows Ordnerstrukturen (Metadaten)
• Big Data aus Hadoop Distributed File System (HDFS), Windows Azure HDInsight (BLOB Storage) und Facebook (!)

Die Suche im Web ist eine tolle neue Funktion, auf diese Weise können mittels Rollover perfekte Vorschauen auf die zu erwartenden Daten erzeugt werden ...

... und bei Gefallen natürlich ausgewählt werden und so in die Excel Mappe eingebunden werden.

2. Daten transformieren und kombinieren - ETL direkt in Excel

Die Funktionen zur Transformation sind zwar an der Zahl überschaubar, aber sehr leistungsfähig, hier ein Beispiel zur Extraktion aus einer relationalen SQL Server Instanz:

Folgende Funktionen zur Transformation der Daten stehen zur Verfügung:

• Einträge bereinigen (Trim, Uppercase, ...)
• Benutzerdefinierte Spalten (Berechnungen, neue Key-Felder, ...)
• Unpivot-Funktion (bspw. aus 1 Datensatz mit 12 Monatswertspalten werden 12 Datensätze ... hervorragend!)
• Ersetzen (bspw. leere Zellen durch "0", ... hervorragend!)
• Datentypen definieren (Zahl, Text, ...)
• Spalten splitten
• Duplikate entfernen

Das geniale dabei ist, daß sämtliche Transformationsbefehle - also auch das "manuelle Ersetzen" - als "Jobliste" verspeichert werden und so bei jedem Aktualisierungsvorgang aufs Neue ausgeführt werden.

Folgende Funktionen zur Kombination der Daten stehen zur Verfügung:

• Merge - Tabellen werden nach dem relationalen Tabellenprinzip über Keys verknüpft ("Join")
• Append - gleichartige Tabellen aus verschiedenen Datenquellen werden addiert ("Union")

Die Append-Funktion liefert insbesondere für (Power Pivot) Excel Data Models eine langersehnte Funktion, die bisher nur mit Einschränkungen und recht kompliziert gelöst werden konnte.

3. Daten aktualisieren - jederzeit auf Knopfdruck

Die Aktualisierung der Daten - auch aus zahlreichen Datenquellen - ist damit mit einem einzigen Knopfdruck erledigt, dazu werden natürlich sämtliche hinterlegten Transformations- und Kombinationsregeln auf die neuen Daten angewendet:

4. Optionale Übernahme ins Excel 2010/2013 Data Model (Power Pivot, Power View, Power Map)

Die derart extrahierten, transformierten und geladenen Daten können jetzt entweder direkt in einer Excel Tabelle bearbeitet werden, oder als Bestandteil eines Excel 2013 Data Models (= Power Pivot in Excel 2010) verwendet werden:

Ein wirklich sehr anschauliches Beispiel  zur Visualisierung mit Power View und Power Map finden Sie hier.

Kritische Würdigung

Power Query ist ein hervorragendes Tool und das "Missing Link" in der bisherigen Self-Service-BI Strategie von Microsoft.

Schade ist, daß keine SQL Server Analysis Services Cubes angebunden werden können und auch kein Konnektor für Google Analytics (bzw. RSS-Feeds) geliefert wird.

Quellennachweis:

http://blogs.msdn.com/b/dataexplorer/

http://blogs.msdn.com/b/microsoft_business_intelligence1/archive/2013/05/15/5-things-you-need-to-know-about-microsoft-data-explorer-preview-for-excel.aspx