Das Datum hat das falsche Format? Trennzeichen im amerikanischen Format? Sie brauchen nur einen Teil der Artikelnummer? Lernen Sie Power Query kennen.

Power Query für Eilige

Wenn Sie Daten auswerten wollen müssen Sie diese aufbereiten. Sie müssen einen Punkt gegen ein Komma tauschen, ein Datum aus Text umwandeln, eine Spalte hinzufügen mit einem Teil der Kostenstelle oder leere Kostenarten ersetzen. Und das jeden Monat. Lästige Arbeit! Fehleranfällig!

Ihre Daten erhalten Sie in einer Datei im CSV-Format (comma separated values. Oder in einer Excel-Datei. Oder Sie kopieren sich die Daten über die Zwischenablage. Und dann wandelt Excel Ihre Werte um. Ohne Rückfrage.

Fun Fact: Wussten Sie, dass deswegen sogar der Name eines Gens offiziell umbenannt wurde? Genforscher nutzen also auch Excel zur Datenanalyse.

Daten Laden, Transformieren und das wiederholbar muss einfache werden. Dafür wurde Power Query entwickelt: Power Query ist ein Werkzeug, mit dem Sie das Laden und Transformieren von Daten automatisieren können. Mit wenigen Mausklicks bestimmen Sie, welche Spalten geladen werden, welche Datenformate oder welche Spaltentitel. Sie bestimmen, ob Werte ersetzt werden sollen oder welche Zeilen Sie filtern möchten. Alles automatisierbar - im nächsten Monat einfach per Mausklick auf Aktualisieren.

Power Query finden Sie in Excel seit der Version 2013. Seit 2016 ist es fester Bestandteil. Power Query ist außerdem Teil von Power BI. Und in den Power BI Dataflows und in den Power App Dataflows. Es ist allgegenwärtig

Soweit für die Eiligen.

ETL – oder: Wie komme ich an meine Daten?

In Excel Power Pivot kann der Nutzer Daten direkt in das Datenmodell laden. Je nach Datenquelle konnte er sie beim Laden filtern, um beispielsweise nur drei Jahre Daten zu analysieren. Da es Power Query noch nicht gab, war das die einzige Möglichkeit, Daten in das Excel Power Pivot Datenmodell zu laden. Eine Änderung der Daten beim Laden oder gar eine Anreicherung hat Microsoft nicht vorgesehen. Fehlende Datumsinformationen, falsch eingegebene Kostenstellen, Artikelnummern kürzen, Dubletten löschen: All das musste dann doch wieder jemand aus der IT machen oder der Kollege, der sich gut in SQL auskennt.

Im BI-Fachchinesisch hört man häufig die drei Buchstaben ETL. ETL steht für Extract Transform Load und bezeichnet drei Prozessschritte, die bei der Datenbeschaffung durchlaufen werden:

  • Extract: Das Extrahieren der Daten aus einem Quellsystem (zum Beispiel einem ERP-System).
  • Transform: Häufig passt die Struktur der Daten im Quellsystem nicht mit der Struktur zusammen, die bei Auswertungen benötigt wird (siehe auch Star-/Snowflake-Schema). Die Daten müssen daher transformiert, ergänzt oder auch gelöscht werden.
  • Load: Die Daten werden in eine Zieldatenbank geladen und dort für Auswertungen abgelegt.

Der Begriff ist nicht scharf abgegrenzt. In großen BI-Projekten finden mehrere Prozessschritte statt und mehrere Datenbanken kommen zum Einsatz.

Power Query

In Excel 2013 (wenig später auch für Excel 2010) fügt Microsoft ein neues AddIn hinzu: Power Query. Während Excel Power Pivot in Excel 2013 bereits zum festen Inventar gehört, muss Power Query noch gesondert installiert werden. Seit Excel 2016 entfällt das. Power Query ist Teil von Excel geworden.

Power Query kennt eine Vielzahl Datenquellen, die Nutzer abgefragen können. Und ein Nutzer kann endlich in die Abfragen eingreifen: Er kann Formate umstellen, Dubletten löschen, Artikelnummern kürzen, Kundengruppen filtern - um nur ein paar wenige der angebotenen Transformationen aufzuzählen. Die Transformationen werden als einzelne Schritte dargestellt, die immer wieder rückgängig gemacht werden können oder zwischen die weitere Schritte eingefügt werden können. Das Ergebnis der Abfrage kann dann wahlweise in eine Excel-Arbeitsmappe oder in das Datenmodell geladen werden, so dass via Excel Power Pivot darauf zugegriffen werden kann. Alle Transformationen in Power Query werden automatisch bei jeder Aktualisierung erneut ausgeführt. Und die Performance ist herausragend!

Get&Transform

So wie Excel Power Pivot beim Wechsel von Excel 2010 zu Excel 2013 vom AddIn zum festen Bestandteil von Excel wurde, wurde Power Query mit dem Wechsel zu Excel 2016 fester Bestandteil. Nur empfand die Marketing-Abteilung von Microsoft den Namen "Power Query" als nicht mehr so vorteilhaft. Daher heißt Power Query seitdem "Get&Transform" und ist nur mit geübtem Auge im Excel-Menü von den alten Funktionen zum Abrufen externer Daten zu unterscheiden:

image

Power Query und Power BI

Wenn Sie noch nicht wissen, was Power BI ist, lesen Sie unseren Artikel dazu. Power Query und Excel Power Pivot sind die technologische Grundlage von Power BI. Daher findet man Power Query auch in Power BI. Dort ist es so, dass man Power BI nutzen muss, um Daten importieren zu können. Dort heißt es übrigens weiterhin "Power Query" und nicht "Get&Transform"...

Im Power BI Ökosystem geht man allerdings weiter. Power Query Abfragen ähneln sich oft zwischen verschiedenen Auswertungen. Oft benötigt man in verschiedenen Auswertungen Zugriff auf gleiche Daten, wie beispielsweise den Kundenstamm. Damit nicht jeder Nutzer identische Power Query Abfragen anlegen muss, bietet Power BI mit Dataflows Power Query quasi Online an. Auch andere Werkzeuge bekommen die Idee von Power Query eingebaut (beispielsweise die SQL Server Integration Services).

Es lohnt sich aus vielerlei Gründen, Power Query zu kennen und noch besser: zu können.

Die Sprache M

Wenn heute Daten zur Verfügung gestellt werden, muss ein Programmierer oder Administrator eine Art Batch-Skript schreiben, um Daten periodisch in einem vereinbarten Format zur Verfügung zu stellen. Je nach Datenquelle muss er sich dann in SQL, ABAP, VBA etc. auskennen. Damit ist der Fachbereich, der die Auswertungen umsetzt, immer auf diesen einen Entwickler oder Administrator angewiesen - und der hat selten Zeit. Und fast wichtiger: Anforderungen an einen Report festigen sich erst beim Machen, also während man Reports erstellt. Damit rennt man ständig zu seinem Ansprechpartner. Und ist ständig in seiner Arbeit blockiert.

In Power Query stellt man seine Transformationen abstrakt dar. Das bedeutet, man muss beispielsweise kein SQL können, um Daten aus einer SQL-Datenbank abzufragen. Es kommt noch besser: Über ein Konzept namens Query Folding wird intern sogar SQL generiert. Hat man das Konzept verstanden und wendet es richtig an, wird es sogar gut optimiert.

Alle Transformationsschritte, die ein Benutzer mit einfachen Mausklicks zusammenstellen kann, werden in eine interne Skriptsprache übersetzt, die Power Query Formular Language. Informal nennt Microsoft die Sprache M - vermutlich für mash-up.

Das bedeutet, wählt ein Benutzer in Power Query bestimmte Spalten einer Datenquelle und filtert oder ersetzt Werte, wird im Hintergrund ein M-Skript erzeugt. Vielleicht fühlt sich der Eine oder Andere an Excel-VBA erinnert: Wenn man nicht weiter wusste, hat man ein Makro aufgenommen und dann nachgesehen, welchen VBA-Code Excel generiert hat. Viele haben VBA nur auf diese Weise gelernt!

Versierte Benutzer können M auch selbst editieren. Die Sprache bringt eine Vielzahl Funktionen mit, um Leerzeichen zu löschen, Datumsformate anzupassen, Zeitspannen zweier Felder zu berechnen, zu Filtern und vieles, vieles mehr. Die Sprache M ist übrigens in Excel und Power BI identisch.

Datenquellen

Power Query unterstützt viele Datenquellen - auch hier: Tendenz steigend. Dadurch, dass Drittanbieter selbst Module für Power Query anbieten können, wächst das Angebot kontinuierlich. Hier ein leiner Auszug von Datenquellen, die in Power Query (Power BI) angeboten werden:

  • Excel: Natürlich können wir Daten aus einer Excel-Datei lesen
  • Text/CSV: klar
  • XML: schon komplizierter, da hier Daten hierarchisch vorliegen
  • Ordner: Alle Dateien aus Ordnern und Unterordner einlesen
  • PDF: Auch das geht. Wenn die PDF Datei nicht aus gescannten Bilddaten besteht erkennt Power Query beispielsweise Tabellen und formt die Daten verwertbar um
  • SharePoint-Ordner: Da die Tendenz zur Speicherung in der Cloud geht, können natürlich auch Dateien in SharePoint eingelesen werden
  • Datenbanken: SQL-Server, Oracle, DB2, Informix, MySQL, PostgreSQL, Sybase, Teradata, ...
  • SAP HANA: Es gibt auch einen HANA-Connector für SAP. Wer sagt eigentlich, dass man Auswertungen in SAP machen muss?
  • Dynamics: Die ERP-Systeme der Microsoft Dynamics-Suite bringen eigene Daten-Connectoren mit
  • Azure: Sehr viele Dienste in der Microsoft Azure Cloud lassen sich direkt mit Power Query abfragen
  • Salesforce
  • LinkedIn
  • uvm.

Da auch abstraktere Vebrindungenstechniken, wie ODBC oder Web-Aufrufe zur Verfügung stehen, ist man auch hier nicht auf angebotene Daten-Connectoren beschränkt.

Import, Direct Query und Aggregations

Direct Query

Power BI (dieser Abschnitt gilt nur für Power BI) nutzt Power Query nicht nur, um Daten zu importieren. Der Import-Mode ist nur eine Variante. Power BI ist auch in der Lage direkt auf die Quelldaten zuzugreifen, wenn ein Report aufbereitet wird oder wenn ein Benutzer mit Filtern im Report interagiert. Dieser Modus wird Direct Query genannt.

Direct Query funktioniert natürlich vor allem mit Datenbanken als Datenquelle. Datei-Datenquellen bieten den Direct Query Modus nicht an. Der Vorteil von Direct Query ist die Aktualität der Daten. Mit direktem Durchgriff auf beispielsweise das eigene ERP-System werden neue oder geänderte Bestellungen in den Reports sofort sichtbar. Klar: Das geht etwas zu Lasten der Geschwindigkeit.

Aggregations

Direct Query ist toll, wenn mit aktuellen Daten gearbeitet werden soll. Berechnungen, wie beispielsweise Anzahl Bestellungen pro Jahr werden aber dann an die Datenbank weitergegeben. Das merkt man an der Geschwindigkeit. Power BI bietet hier eine Hybrid-Lösung: Für Aggregationen (Bestellungen pro Jahr, Summe Nettoumsatz) werden importierte Daten herangezogen, die beispielsweise jede Stunde aktualisiert werden. Beim Zugriff auf Einzelinformationen (eine konkrete Bestellung, ein konkreter Kunde) wird via Direct Query auf die Quelldatenbank zugegriffen.

Für wen ist Power Query

Das ist leicht zu sagen: Für alle, die mit Daten zu tun haben.

Power Query finden wir in Power BI und Excel Power Pivot. Um wiederverwendbare Abfragen im Team bereitstellen zu können gibt es Dataflows.

Power Apps sind ein neuer Dienst von Microsoft, um Aufgaben IT gestützt im Unternehmen zu automatisieren. Auch hier kommt Power Query und die Sprache M zum Einsatz, wenn es um Daten Laden und Transfomieren geht.

Wie oben kurz erwähnt gibt es seit vielen Jahren die SQL Server Integration Services. Hier werden komplexe ETL-Prozesse definiert. Beispielsweise für zentrale Data Warehouse Systeme. Auch die Integration Services bieten eine Power Query Source an, um Daten aus Datenquellen abzufragen.

Power Query ist also in einer Vielzahl an Werkzeugen verfügbar. Tendenz steigend. Power Query ist gleichzeitig so aufgebaut, dass jeder Benutzer, also auch nicht IT affine Benutzer, das Werkzeug bedienen können. Je tiefer man sich in die Sprache M einarbeitet, desto komplexere Abfragen kann man entwerfen. Damit addressiert Power Query nicht nur Excel-Benutzer, sondern auch Daten-Profis in der IT.

Beispiele für Transformationen

Power Query kann prima aus "20201220" das Datum 20.12.2020 umwandeln. Es kann aber noch viel mehr - ein paar Beispiele:

Ent-Pivotieren

Oft erhält man von Lieferanten oder Kunden Daten, die man auswerten möchte. Häufig sind diese schon in einer Pivot-Darstellung: Umsätze pro Produkt und Monat. Die Produkte sind in den Zeilen, die Moate leider in den Spalten. Die Spaltentitel sind damit natürlich dynamisch. Sie ändern sich jeden Monat oder jedes Jahr. Power Query kennt eine Transformation entpivotieren. Das ist so ähnlich wie in Excel eine Transposition. Nur dass man das auf einzelne Spalten beschränken kann. Wahnsinnig hilfreich. Vor allem in Kombination mit Lesen aus Ordner, was ich unten beschreibe.

Verweise

Aus einer Abfrage machen wir zwei: Wir haben eine Auftragstabelle in der alle Kundendaten enthalten sind. Die Aufträge sind eindeutig, die Kunden aber nicht. Klar, weil ein Kunde hat hoffentlich mehr als einmal bestellt. Über Verweise können nun zwei Abfragen verzweigt werden. Die eine enthält nur noch die Auftragsdaten und die andere nur noch die Kundendaten. Das ist wichtig, wenn man gute Datenmodelle in Excel Power Pivot oder Power BI aufbauen möchte.

Web-Scraping

Power Query in der Variante von Power BI (Power Query in Excel und in Power BI unterscheiden sich etwas) kennt sogar Web scraping. Es geht darum aus einer oder mehreren Webseiten Informationen zu extrahieren. Im einfachsten Fall aus einer Wikipedia-Seite einfach alle Währungen. Andere laden aus Marktplätzen ganze Anbieterlisten.

Ordner abfragen

Oft hat man Daten in mehreren Dateien. Beispielsweise für jeden Monat eine eigene. Jeden Monat kommt eine hinzu. Viele unserer Kunden haben früher diese Dateien geöffnet und die Daten immer hinten dran gehängt. Damit man am Ende eine Datei für den Import gehabt hat. Klappt aber nur bis 1 Million Zeilen, wenn man reines Excel verwendet.

Power Query kennt eine Ordner-Abfrage. Diese Abfrage liest alle Dateien eines Ordners (und Unterordner) ein. Diese lassen sich (wenn sie im selben Fomat vorliegen) kombinieren. Das bedeutet, dass Power Query einfach das gleiche macht, wie man bisher manuell gemacht hat. Und das dynamisch. Wenn also im kommenden Monat weitere Dateien hinzu kommen: Aktualisieren genügt. Alles andere erfolgt automatisch!

Parameter

Viele Abfragen verweisen lokale oder auf Netzwerkverzeichnisse. Ein Umzug bedeutet alle Abfragen zu aktualisieren. Oder man arbetiet mit Parametern, wie hier erläutert:

Parameter in Power Query

Natürlich auch prima nutzbar, um zwischen Test und Produktiv umzuschalten. Oder die Basisadresse eines WebServices zu hinterlegen. Oder den Datenbank-Server und Port zentral zu administrieren, oder....

Power Query lernen

Mit Power Query kann man einfach anfangen. Einfach in Excel oder Power BI das AddIn starten und loslegen. Es gibt viele Tutorials online.

Für einen tieferen Einstieg lohnt sich ein Training. Das Training ist am besten kombiniert mit Power BI oder Excel Power Pivot. Das macht am meisten Sinn, da Power Query in diese Produkte integriert ist. Daher ergibt eine isolierte Betrachtung wenig Sinn.

Bücher können wir leider keine empfehlen. Meist wird technisch das Produkt erklärt. Wir halten es für deutlich zielführender, wenn Power Query immer an konkreten Aufgabenstellungen erläutert wird. Zum Profi wird man dann Stück für Stück selbst.

Sie möchten mit uns arbeiten?

Gerne arbeiten wir mit Ihnen zusammen. Wir bieten Grundlagen-Trainings an - in Power BI und Excel Power Pivot. Wir coachen Sie und Ihre Mitarbeiter oder setzen Anforderungen selbst um. Außerdem bieten wir eine Art Sprechstunde an, damit Sie oder Ihre Kollegen nicht zu lange an einem Problem hängen bleiben.

Kontaktieren Sie uns - per E-Mail oder rufen Sie einfach an.

Fazit

Power Query ist ein sehr mächtiges Werkzeug. Ich selbst dachte anfänglich, dass Power Query eher ein Spielzeug ist und für Leute, die wenig IT Verständnis haben. Weit gefehlt. Heute bin ich Fan von Power Query und seinen Möglichkeiten. Und hoffe, dass es in immer mehr Systeme eingebaut wird.