3. Datenimport mit Power Query

Datenimport nach Excel mit SVERWEIS sehr aufwendig

In vielen Controlling-Abteilungen werden monatlich Daten aus den Vorsystemen als Dateien exportiert, diese Dateien als Tabellenblätter in einer Zieldatei eingefügt und mit der Funktion SVERWEIS zu einer „Master-Tabelle“ verbunden. Dies ist mit einigem Aufwand verbunden:

• Oft müssen Strukturen und Formate verändert werden (Transformation), weil z.B. unnötige Zwischensummen entfernt und Spaltentrennung aufgehoben werden müssen.
• Textformate bei Zahlen müssen in numerische Zahlen verwandelt, Zeilenumbrüche und Leerzeichen entfernt, negative Vorzeichen vorangestellt werden usw.
• Teilweise werden Daten sehr aggregiert in Form einer Tabelle geliefert, werden aber als Liste benötigt. Zur Umwandlung in eine Liste wird dann meist VBA eingesetzt.

Die „Master-Tabelle“ dient im Folgenden als Datenquelle für eine Analyse mit der Pivot-Tabelle, für ein Reporting uvm.

Durch diese "typische" Vorgehensweise entstehen unnötig große Excel-Dateien, die unnötig redundante Dateien enthalten und zudem sehr unperfomant sind. Die größte Datei, die mir in diesem Kontext begegnet ist, war 75 MB groß, diente einem Performance Reporting, bestand aus 7 Downloads mit mehreren 10.000 Zeilen aus SAP FICO und war völlig unperformant. Jede Aktualisierung dauerte Minuten! 
Obwohl es seit Excel 2010 das ETL-Feature Power Query gibt, wird in sehr vielen Controlling-Abteilungen immer noch in der oben beschrieben Art und Weise gearbeitet. Seit Excel 2016 ist Power Query fester Bestandteil von Excel. Es ist erstaunlicherweise noch immer weitgehend unbekannt und ungenutzt. 

…mit Power Query sind es nur ein paar Mausklicks

Power Query ist als Teil der Microsoft Power Plattform ein Gamechanger, was Datenimport und Datenaufbereitung in Excel betrifft, und sehr anwenderfreundlich. Durch die Vereinfachung der Datenabfrage und -integration ermöglicht es Power Query Benutzern aller Kenntnisstufen, leistungsstarke Datenanalysen durchzuführen. 

Was ist Power Query? Ursprünglich entwickelt, um die Datenabfrage- und -bearbeitung innerhalb von Microsoft Excel zu ermöglichen, ist Power Query inzwischen auch Bestandteil von Power BI und Microsoft Dataverse. Microsoft Dataverse Daten ist ein Dienst, der es ermöglicht, Daten sowie Metadaten, z. B. von Dynamics 365, Office 365, Power Plattformen ) cloud-basiert zu speichern und zu verwalten. 

Der Vorteil von Power Query ist, dass viele Transformationsschritte, die in Excel mit Text-Funktionen oder VBA umgesetzt werden müssen, hier bereits als einfache Menü-Befehle im sogenannten Power Query Editor zur Verfügung stehen. Außerdem ist die Vorgehensweise im Grunde immer die gleiche und besteht aus drei Schritten:

1. Verbinden: Herstellen einer Verbindung zu einer oder mehreren Datenquellen in der Cloud, in einem Dienst, auf einem SharePoint-Ordner, einer Datenbank oder einer Datei auf einem Fileserver. Die Datenquelle wird in Form von Tabellen in ein Power Query-Modell, das Teil einer Excel Datei ist, importiert. 
2. Transformieren: Bearbeiten der Daten im Power Query-Editor mittels Menüs oder durch Programmierung mittels der Power Query-Formelsprache „M“ im Abfrage-Editor, um die gewünschte Zielstruktur und Datenauswahl herzustellen. Die Datenquelle wird dadurch nicht verändert, das Prinzip der Datenkonsistenz  bleibt erhalten. Datenkonsistenz bedeutet, dass das Prinzip der Single source of truth erhalten bleibt, so dass alle Benutzer, die auf die Daten zugreifen, dieselben korrekten und aktuellen Informationen erhalten, unabhängig davon, wann und wo der Zugriff erfolgt.
3. Laden: Zum Abschluss laden Sie das Ergebnis der Transformation aus dem Datenmodell in die Excel-Oberfläche. Das ist entweder eine „Intelligente Tabelle “ oder eine Verbindung, die z.B. die Daten direkt an eine Pivot-Tabelle übergibt. Außerdem können Sie das Ergebnis der Transformation direkt an das Power Pivot-Datenmodell übergeben. Die „Intelligente Tabelle“ kann manuell, in Zeitintervallen oder beim Öffnen der Datei aktualisiert werden.

Weitere Vorteile von Power Query

Power Query hat viele Vorteile und ist eigentlich ein „must have“ für Controller. Als Datenquellen können die gängigen Datenbanken (Oracle, SQL Server usw.), SAP HANA oder Dateien (TXT, XLSX, CSV,PDF) genutzt werden. Was mich persönlich begeistert, ist die Möglichkeit, alle Dateien eines Ordners auf einem Fileserver oder einem SharePoint mit ein paar Mausklicks zu einem Datenmodell zusammenzuführen. Daraus ergeben sich Vorteile für den Planungsprozess oder z.B. für Year-to-Date-Auswertungen:

• Power Query erstellt eine dauerhafte Verbindung zur Dauerquelle, sodass stets aktuelle Daten in der Zieldatei vorhanden sind. Jeder einzelne Transformationsschritt wird im Editor aufgezeichnet, sodass einerseits eine Dokumentation vorliegt, andererseits jederzeit nachträglich Änderungen durchgeführt werden können.
• Mit Hilfe von Power Query können Tabellen pivotiert (wide format) oder depivotiert (long format) werden. Das war zuvor nur mit VBA möglich. 
• Die Zeitersparnis ist enorm. Die größte Zeitersparnis konnte bei einem DAX-Unternehmen erzielt werden, in dem in einer Fachabteilung monatlich 4 Personenwochen aufgewendet wurden, um einen Prozess mit Excel durchzuführen. Ein in drei Personenstunden erstelltes Power Query-Modell führt nun den gleichen Prozess automatisch durch! 

Power Query lässt sich hervorragend für Planung, Reporting, Dashboards, Projektmanagement einsetzen sowie als Vorstufe für das Datenmodell von Power Pivot. Power Pivot ist nicht in der Lage, Datenstrukturen zu transformieren. Für die optimale Modellierung von Excel-Dateien ermöglicht Power Query den automatischen Datenimport auf die Eingabeebene in Form einer „Intelligenten“ Tabelle .

Ist Excel noch zeitgemäß? Aber ganz sicher mit den Prinzipien des Modern Excel!