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-Funktionen 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/team-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

http://www.draftexpress.com/team-stats/nba/all/2017/basic/standard/0/astpg/desc

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.

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/