Power Query Funktionen

    Eine Einführung in Power Query Funktionen am Beispiel einer Datenabfrage aus dem Web über mehrere Seiten.

    Power Query in Kürze

    Power Query ist ein AddIn für Excel 2013 (in Excel 2016 taucht dieser Begriff gar nicht mehr auf, die Funktionen wurden sind aber alle enthalten, sie wurden im Daten-Ribbon unter “Abrufen und transformieren” als reguläre Excel-Funtkionen aufgenommen), mit dem Daten aus verschiedensten Datenquellen nicht nur geladen, sondern quasi auf dem Weg auch noch transformiert, gefiltert, zusammengefasst und verbunden werden können. Auf Deutsch: eine eierlegende Wollmilchsau 😉

    Power Query & Power Pivot

    Inzwischen ist Power Query ein ausgereiftes Framework. Deshalb empfehlen wir Daten in Excel (egal ob für Power Pivot oder direkt in Excel-Tabellen) generell über die Power Query Funktionalität zu laden.

    Power Query kann sehr komfortabel über eine Benutzeroberfläche verwendet werden. Innen drin steckt aber immer die Power Query Formelsprache, auch genannt M.

    Jede Transformation (sog. Schritt) die an der Oberfläche konfiguriert wird, wird intern als Ausdruck in der M-Sprache gespeichert.

    Jede Abfrage in Power Query muss eine Tabelle zurückgeben. Dafür kann sie auf die vorgegebenen Funktionen zurückgreifen. Zusätzlich können jedoch auch eigene Funktionen definiert werden, die auch in unterschiedlichen Abfragen verwendet werden können. Diese Funktionen wiederum können auf die komplette Funktionalität der Sprache M zurückgreifen, also ebenfalls auf Dateien, Datenbanken und die Daten in Excel.  Damit wird Power Query zu einem mächtigen Werkzeug, mit dem komplexe Anwendungsfälle abdeckbar sind. Dazu gehören Abfragen, die den aktuellen Zeitpunkt berücksichtigen oder über Werte in Excel-Zellen jederzeit einfach parametrisiert werden können.

    Für unsere Beispiel wollen wir Daten von einer Website herunterladen, auf der die Daten über mehrere Seiten verteilt sind (sog. Pagination).

    Die Daten für unser Beispiel sind Spielerdaten der NBA (amerikanische Basketballliga). http://www.draftexpress.com/stats/nba

    Los geht’s

    Eine neue Abfrage Aus dem Web wählen:

    image

    Wir wählen erweitert und geben die URL gleich in mehreren Teilen ein, sodass die aktuelle Seite ein eigener URL-Teil ist (durch einfaches Ausprobieren auf der Website haben wir herausbekommen, wo die aktuelle Seite in der URL dargestellt wird, oft findet sich das auch am Ende der URL in sog. URL Parametern z.B. www.example.com/someData?page=2)

    image

    Im Navigator wählen wir die von Power Query erkannte Tabelle aus und gehen weiter mit Bearbeiten:

    image

    Der Power Query Abfrage-Editor mit Vorschau und den bereits definierten Schritten:

    image

    Um aus der Abfrage eine wiederverwendbare Funktion zu machen, wechseln wir im Ribbon Ansicht in den Erweiteren Editor:

    image

    Unter Eigenschaften vergebe ich noch einen sinnvollen Namen und kann die Funktion auch gleich testen und dann Schließen:

    image

    Jetzt erstellen wir eine neue Abfrage, die unsere Funktion verwendet:

    image

    Wir vergeben einen Namen und bauen dann Schritt für Schritt die Abfrage auf.

    Den ersten Schritt geben wir über die Formelleiste ein, da er nicht über Oberfläche machbar ist und leider auch etwas kompliziert (die Erklärung folgt am Ende des Artikels):

    image

    Als Ergebnis erhalten wir eine Liste von Tabellen (für jede zurückgegebene Seite an Ergebnissen eine).

    Als nächstes betätigen wir den Button In Tabelle und belassen die voreingestellten Optionen:

    image

    Leider fügt uns diese Funktion eine Extra-Zeile zu Beginn ein, die wir im nächsten Schritt gleich wieder entfernen werden. Vorher benennen wir den Schritt noch um (ich nenne es KonvertierteTabelle), da wir ihn in selbst geschriebenen Formeln weiterverwenden wollen und die autoamtisch vergebenen Namen (wegen der Leerzeichen) mit Hochkommas umschlossen und führendem “Gartenzaun” (#) versehen werden müssen.

    image

    Über den fx Button bekommen wir einen neuen Schritt in den wir folgende Formel eingeben:

    image

    und auch gleich wieder umbenennen (ich verwende ZeileUebersprungen).

    Über den kleinen Button auf Column1 kann die Tabelle nun “erweitert” werden, d.h. der Inhalt der “inneren” Tabellen wird zusammengeführt in die Tabelle. Bevor ich auf OK drücke, habe ich den Haken in der erste Spalte entfernt (diese ist leer, das weiß ich vom Ausprobieren) und außerdem die Auswahl Ursprünglichen Spaltennamen als Präfix verwenden entfernt, damit nicht alle Spalten mit “Column1” anfangen:

    image

    Nach Schließen & laden habe ich nun meine Daten in einer Excel-Tabelle zur weiteren Analyse.

    Erklärung von List.Generate()

    Die List.Generate() Funktion hat 4 Parameter

    1. Die Initialwertfunktion: Ein initialer Wert (meist ein Record, kann aber auch nur eine Zahl sein), mit dem die Funktion gestartet wird.
    2. Die Abbruchbedingung: Eine Regel, die den aktuellen Wert prüft und sofern diese Funktion true zurückgibt wird die Funktion beendet.
    3. Die Transformationsfunktion: Hier wird der Wert aus der letzten Runde (beim ersten Mal der Initialwert) übergeben. Die Funktion sollte einen Wert vom gleichen Typ zurückgeben.
    4. Die Zeilenwertfunktion: Diese Funktion bekommt den aktuellen Wert (bei ersten Mal den Initialwert) und gibt den Wert zurück, der in der Ergebnisliste erscheinen soll.

    Triviales Beispiel um eine List von absteigenden Zahlen zu erzeugen:

    Man beachte die each-Abkürzung für Funktionen in den Parametern 2, 3 und 4.

    Ohne diese Abkürzung würde der Aufruf so aussehen:

    each kann nur bei Funktionen mit einem Parameter verwendet werden und ist also eigentlich einfach eine andere schreibweise für (_) =>

    Das gleiche Beispiel noch mit einem Record:

    und noch eine Abkürzung mit each bei Verwendung mit Records kann statt _[feld] auch direkt [feld] geschrieben werden.

    Und jetzt nochmal die spezielle Erläuterung für unsere Verwendung von List.Generate()

    Initialwert

    Ein Record mit zwei Feldern. Die PageNumber startet mit 1, Func wird als Platzhalter (Variable) für die Ergebnisse mit null initialisiert.

    Abbruch bei

    Wenn der Rückgabewert der Function einen Fehler enthält, dann sind wir fertig.

    Transformation

    Wir erhöhen den Wert für die Pagenumber und befüllen Func mit dem Funktionsaufruf, der die Tabelle mit Daten für die gegebene Seite zurückgibt.

    Listenwert

    Als Ergebnis übernehmen wir nur die Tabelle in unsere Liste.

    Links

    Offizielle Sprachreferenz bei Microsoft (bisher nur auf Englisch)
    https://msdn.microsoft.com/en-us/library/mt211003.aspx

    Eine Übersicht der wichtigsten Sprachbestandteile (leider auch nur auf Englisch)
    https://bondarenkoivan.wordpress.com/2017/04/03/powerquery-cheat-sheet/

    Kategorien: Artikel, Datenverbindungen, Power Query
    Andreas Moosbrugger

    Andreas Moosbrugger ist Experte für Power BI, Power Pivot und Power Query. Als Softwareentwickler und Berater bei der mogular GmbH analysiert er Softwaresysteme, konzipiert sie neu und integriert dabei Geschäftsprozesse, Organisation und IT. Bevor er 2011 mit Holger Gubbels die mogular GmbH gegründet hat, war er bei Daimler und AEB tätig. Dem Diplom-Ingenieur liegt es am Herzen, das Wissen zur kostenlosen BI-Software verfügbar zu machen, damit jeder auch ohne teure Programme seine Daten effektiv analysieren kann. Deshalb bloggt er hier auf PowerPivotInsights. Sie erreichen ihn unter am@mogular.com

    Antwort schreiben

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