Beim Auswerten von Projektdaten sind so manche Hürden zu überwinden: Daten stammen aus unterschiedlichen Quellen oder sie weichen in ihrer Struktur und Granularität voneinander ab. Mit den Standardfunktionen in Pivot ist in solchen Fällen eine differenzierte Auswertung nicht möglich. Doch mit Power Query und Power Pivot stellt Excel seit Version 2016 Funktionen für Business Intelligence (BI) zur Verfügung, die Pivot flexibler und leistungsfähiger machen. Damit erübrigen sich aufwendige VBA-Lösungen, die bisher von der IT oder externen Spezialisten entwickelt werden mussten. Mit Power Query und Power Pivot können die Datenanalysten in den Fachabteilungen nun selbst die eingehenden Projektdaten umfassend und differenziert auswerten.

Dieser dreiteilige Beitrag zeigt Ihnen an einem Praxisbeispiel, wie Sie Power Query und Power Pivot beim Auswerten von Projektdaten einsetzen. Sie erfahren, mit welchen Schritten Sie

• unstrukturierte Daten aus unterschiedlichen Quellen mit Power Query automatisiert aufbereiten,
• diese dann in das Datenmodell laden und dort in Beziehung zueinander setzen und
• schließlich in Power Pivot mit DAX-Formeln über mehrere Tabellen hinweg umfassend analysieren.

Dieser erste Teil des Beitrags zeigt am Beispiel eines Plan-Ist-Vergleichs von Projektkosten den Weg vom Einlesen und Aufbereiten der verteilten Daten in Power Query bis zur Auswertung in Power Pivot. Die hier verwendeten Daten spiegeln eine Situation wider, die in der Praxis oft anzutreffen ist: Mehrere Faktoren verhindern eine direkte Pivot-Auswertung.

Drei Hürden sind zu überwinden:

• Daten stammen aus verteilten Quellen: Die Projektdaten und Plan-Kosten liegen in einer Excel-Mappe als Tabellen vor. Die Ist-Kosten hingegen stammen aus einem Fremdsystem und werden täglich oder wöchentlich in einer CSV-Datei übermittelt.
• Daten weisen unterschiedliche Granularität auf: Die Plan-Kosten liegen auf Monatsbasis, die Ist-Kosten hingegen auf Tagesbasis vor.
• Die Datenstruktur lässt sich nicht mit Pivot verarbeiten: Die Plankosten werden in einer vorbereiteten Liste manuell erfasst. Monat für Monat wird eine neue Spalte hinzugefügt. Damit liegen die Plandaten verteilt auf mehrere Spalten vor, was eine Auswertung mit Pivot unmöglich macht (mehr dazu in "So sorgen Sie für perfekte Pivot-Berichte in Microsoft-Excel").

In 3 Sätzen: Das erledigt Power Query

Mit Power Query lassen sich Daten aus verschiedenen Quellen automatisiert in eine Excel-Arbeitsmappe importieren, bereinigen und in die passende Struktur umwandeln. Zusatztabellen können generiert werden, um zeitliche Analysen zu erstellen. Die so vorbereiteten Tabellen lassen sich ins Datenmodell laden.

In 3 Sätzen: Das leisten Power Pivot mit Datenmodell und DAX

Power Pivot ermöglicht den Zugriff auf das Datenmodell. Dort werden die Daten zueinander in Beziehung gesetzt. Mit der Formelsprache DAX im Datenmodell werden Measures erstellt, die als zusätzliche und ausgefeilte Berechnungen in Pivot-Tabellen dienen.

Verteilte Projektdaten in 4 Schritten für die Analyse aufbereiten

In diesen Schritten werden die Daten aufbereitet und analysiert:

• Schritt 1: Die verschiedenen Datenquellen – eine Excel-Mappe mit zwei Tabellen sowie eine CSV-Datei – werden mit Power Query eingelesen und bereinigt.
• Schritt 2: In Power Query wird eine passende Kalendertabelle erstellt, die es später ermöglicht, unterschiedliche Zeiträume bedarfsgerecht darzustellen.
• Schritt 3: Die so aufbereiteten Datenbestände werden ins Datenmodell geladen und dort in Beziehung zueinander gesetzt. Das eröffnet zusätzliche Auswertungsmöglichkeiten.
• Schritt 4: In Power Pivot werden mit DAX-Formeln Berechnungen erstellt – sogenannte Measures. Diese ermöglichen die unkomplizierte Berechnung beliebiger Kennzahlen in den bestehenden Pivot-Tabellen. Dies wird in Teil 2 der Artikelfolge beschrieben.

Schritt 1: Die Daten mit Power Query einlesen, bereinigen und anreichern

Power Query ist das Tool zum Automatisieren von Datenimport und -aufbereitung. Für den Import bietet es eine Vielzahl von Schnittstellen zu unterschiedlichsten Datenquellentypen: TXT, CSV, Excel, relationale und analytische Datenbanken oder Online-Dienste. Um die importierten Daten anschließend zu bereinigen und aufzubereiten, stellt der Editor von Power Query zahlreiche leistungsfähige Transformationsbefehle zur Verfügung, die sich ganz einfach per Mausklick ausführen lassen und so die schnelle Aufbereitung ermöglichen. Bild 4 zeigt, welche Funktionen von Power Query im Praxisbeispiel zum Einsatz kommen.

Der Datenimport und die Datentransformation mit Power Query bilden die Basis für die anschließende Analyse mittels Datenmodell.