Projektauswertungen mit Power Query, Power Pivot, Datenmodell und DAX Wenn Pivot nicht ausreicht – die BI-Tools von Excel im Praxiseinsatz

Teil 1:
Verteilte Daten automatisiert importieren und aufbereiten
Wenn Pivot nicht ausreicht – die BI-Tools von Excel im Praxiseinsatz

Verteilte Daten, verschieden Granularität, falsche Struktur: Wer Projektdaten in Excel per Pivot auswerten möchte, stößt oft an Grenzen. Doch mit Power Query und Power Pivot stellt Excel BI-Funktionen zur Verfügung, die Pivot flexibler und leistungsfähiger machen. Ein Praxisbeispiel zeigt, wie Sie Projektdaten damit in vier Schritten für die Analyse aufbereiten.

Management Summary

Download ZIPDownload ZIP

Projektauswertungen mit Power Query, Power Pivot, Datenmodell und DAX Wenn Pivot nicht ausreicht – die BI-Tools von Excel im Praxiseinsatz

Teil 1:
Verteilte Daten automatisiert importieren und aufbereiten
Wenn Pivot nicht ausreicht – die BI-Tools von Excel im Praxiseinsatz

Verteilte Daten, verschieden Granularität, falsche Struktur: Wer Projektdaten in Excel per Pivot auswerten möchte, stößt oft an Grenzen. Doch mit Power Query und Power Pivot stellt Excel BI-Funktionen zur Verfügung, die Pivot flexibler und leistungsfähiger machen. Ein Praxisbeispiel zeigt, wie Sie Projektdaten damit in vier Schritten für die Analyse aufbereiten.

Management Summary

Wir empfehlen zum Thema Berichten
13.06.2024
1.495,00,-
Methoden des modernen Portfoliomanagements

Die richtigen Dinge tun – für mehr Fokus, Agilität und Produktivität im Unternehmen! In unserem E-Learning-Seminar lernen Sie in nur 4 Workshops, wie Sie Ihr Portfolio mit modernen Methoden organisieren und ausbauen. Mehr Infos

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.

Bild 1: Vorschau auf eine fertige Pivot-Auswertung zum Vergleichen der Plan- und Ist-Kosten über beliebige Zeiträume – einfach und interaktiv bedienbar über die rechts eingebauten Zeitachsen
Bild 1: Vorschau auf eine fertige Pivot-Auswertung zum Vergleichen der Plan- und Ist-Kosten über beliebige Zeiträume – einfach und interaktiv bedienbar über die rechts eingebauten Zeitachsen

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").
Bild 2: Überblick über die BI-Tools in Excel und was sie leisten
Bild 2: Überblick über die BI-Tools in Excel und was sie leisten

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.
Bild 3: Mit diesen vier Schritten wird hier im Praxisbeispiel die bedarfsgerechte Auswertung der Projektkosten aufgebaut
Bild 3: Mit diesen vier Schritten wird hier im Praxisbeispiel die bedarfsgerechte Auswertung der Projektkosten aufgebaut

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.

Bild 4: Funktionen von Power Query, die hier im Praxisbeispiel zum Einsatz kommen
Bild 4: Funktionen von Power Query, die hier im Praxisbeispiel zum Einsatz kommen

Fortsetzungen des Fachartikels

Teil 2:
Pivot-Auswertungen mit DAX-Measures erweitern

Wer Projektdaten mit Pivot auswerten möchte, stößt oft an Grenzen. Erfahren Sie in Teil 2 der Artikelserie, wie Sie mit DAX-Measures die Pivot-Feldliste erweitern und bedarfsgerechte Kennzahlen über mehrere Tabellen hinweg berechnen.

Teil 3:
Mit DAX-Measures Kennzahlen ermitteln, die über den Standard hinausgehen

Wer Projektdaten mit Pivot auswerten möchte, stößt oft an Grenzen. Erfahren Sie in Teil 3 der Artikelserie, wie Sie mit expliziten Measures Auswertungen erstellen, die mit Standardberechnungen nicht möglich wären.

Das könnte Sie auch interessieren

Vorschaubild

Sie wollen Ihre Pivot-Tabellen in Excel möglichst unkompliziert erstellen und das Maximum aus dem Informationsgehalt Ihrer Auswertungen herausholen? Dieter Schiecke gibt Ihnen 12 Tipps an die Hand, mit denen Sie sich Zeit und Nerven sparen.

Vorschaubild

Pivot-Tabellen sind das perfekte Tool, wenn es darum geht, umfangreiche Informationen zu unterschiedlichen Fragestellungen auszuwerten, ohne sich mit Formeln oder Filtern beschäftigen zu müssen.

Vorschaubild

Sie arbeiten gerne mit Pivot-Tabellen, ärgern sich aber über die starre Struktur der Tabellen? Dann verwenden Sie stattdessen doch Cube-Funktionen!

Unlocked Content
Um Daten für eine Excel-Auswertung zusammenzuführen, ist oft zeitaufwendige Klick-Arbeit notwendig – vor allem, wenn Sie Daten manuell übertragen müssen. Schneller gelingt das in vielen Fällen, wenn Sie die Datenabfrage automatisieren.
Teil 2:
Pivot-Tabelle zum Kosten-Cockpit erweitern
Im ersten Teil haben Sie erfahren, wie Sie kompakte Pivot-Tabellen aus umfangreichen Daten erstellen. Mit einigen Zusatzelementen können Sie die Aussagekraft solcher Auswertungen noch deutlich erhöhen.