Wie man Daten aus dem Excel Power Pivot Datenmodell exportiert, darüber haben wir bereits geschrieben. Daten lassen sich aber auch aus dem Power BI Service exportieren. Wie das geht, zeigen wir in diesem Artikel.

Excel und das eigene Power Pivot Datenmodell

Wenn Sie Excel Power Pivot kennen, dann sind Ihnen vielleicht Reverse Linked Table ein Begriff. Mit diesen Tabellen kann man in Excel auf das eigene Power Pivot Datenmodell zugreifen. Und dadurch Daten zurück in ein Arbeitsblatt exportieren. Das kann Sinn ergeben, wenn man im Datenmodell umfangreiche Berechnungen durchgeführt hat. Und nun möchte man diese Daten nicht nur auswerten, sondern exportieren.

So hat einer unserer Kunden Auftragsdaten aus SAP importiert. Mit gekauften Daten von Mitbewerbern wurden in Power Pivot verschiedene Preismodelle durchgerechnet. Das Ergebnis kann dann über Reverse Linked Tables wieder exportiert werden - und daraus wird eine CSV Datei erzeugt. Diese dient als Importdatei für die neuen Preise in SAP.

Im Detail beschrieben haben wir das in diesen beiden Artikeln:

Excel und Power BI

In Power BI Desktop legt man ein Datenmodell und diverse Reports an. Anschließend werden diese im Power BI Service veröffentlicht. Auf das Datenmodell haben Sie von Excel aus Zugriff. Dazu wählt man im Power BI Service die Option In Excel analysieren auf dem gewünschten Datenmodell.

Power BI Datenmodell in Excel analysieren

Der Power BI Service erzeugt dadurch eine Excel-Datei, die das online verfügbare Datenmodell als Datenquelle eingebunden hat, und lädt dieses über den Browser herunter (früher wurde eine ODC-Datei heruntergeladen, also nur die Datenquelleinformationen, die man mit einer neuen Excel-Datei öffnen konnte).

Datenquelle exportieren/importieren

Möchte man die Datenquelle in einer vorhandenen Excel-Datei verwenden, muss man die Datenquellen-Information aus der heruntergeladenen Excel-Datei zuerst exportieren. Dazu öffnen Sie die Excel-Datei aus dem Power BI-Service und wechseln im Menü auf den Reiter Daten. Dort öffnen Sie das FlyOut mit dem Namen Abfragen und Verbindungen über den entsprechenden Menüpunkt. In diesem FlyOut sehen Sie normalerweise die Power Query-Abfragen, wenn Sie mit Excel Power Pivot arbeiten. Das FlyOut hat noch einen zweiten Reiter mit Namen Verbindungen. Dort finden Sie die Datenquellen-Informationen zum Power BI-Datenmodell.

Abfragen und Verbindungen in Excel

Via Doppelklick öffnet sich der Eigenschaftsdialog der Datenquelle. Dort ist im Reiter Definition der sogenannte ConnectionString. Viel wichtiger: unten finden Sie eine Schaltfläche Verbindungsdatei exportieren.

Excel Dialog Verbindungseigenschaften

In einer neuen oder bestehenden Excel Datei kann man diese Verbindungsdaten importieren. Einfach im Menü in den Reiter Daten wechseln, und dort via Vorhandene Verbindungen den entsprechenden Dialog öffnen.

Excel Dialog Vorhandene Verbindungen

Dort suchen Sie die Schaltfläche Nach weiteren Elementen suchen... über die Sie die gerade exportierte Datei wieder importieren können. Excel möchte gleich eine Pivot-Tabelle anlegen. Falls Sie das verschieben möchten, wählen Sie Nur Verbindung erstellen.

Neues Element anlegen

Die vom Power BI Service erzeugte Exceldatei hat bereits einen Platzhalter für eine neue Pivot-Tabelle. Durch die Datenverbindung kann man jetzt auf alle Elemente des Power BI-Datenmodell zugreifen. Aber Achtung: Implizite Measures funktionieren hier nicht. Wundern Sie sich nicht, dass Sie ein Feld wie Preis nicht in die Pivot-Tabelle ziehen können. Es muss schon ein Measure sein. Falls das für Sie nicht logisch klingt, lesen Sie unbedingt unseren den Artikel Implizite Measures - mehr Fluch als Segen.

Falls Sie die Datenverbindung neu importiert und nur die Verbindung erstellt haben, müssen Sie eine neue Pivot-Tabelle unter Nutzung der neu importierten Datenquelle anlegen. Dazu wählen Sie im Menü im Reiter Einfügen die Funktion PivotTable und im Dialog wählen Sie die Option Externe Datenquelle verwenden.

Pivot-Tabelle in Excel mit externer Datenquelle anlegen

Über die Schaltfläche Verbindung auswählen öffnet sich ein Dialog mit einer Liste, in der Sie die importierte Power BI Datenquelle finden sollten. Der Name fängt an mit pbiazure://...

Wählen Sie aus der Datenmodell-Feldliste ein Measure aus (wie oben erwähnt: es muss eine Measure sein, da implizite Aggregationen hier nicht unterstützt werden).

Drill-Through in Excel

Auch in Excel kennt man einen Drill-Through. Der ist bei weitem nicht so komfortabel, wie man das von Power BI kennt: Durch einen Doppelklick auf einen Wert in einer Pivot-Tabelle öffnet sich ein neues Arbeitsblatt. Und dort werden die Daten angezeigt, die zu dem Ergebnis in der Pivot-Tabelle geführt haben (mit einer Standard-Begrenzung von 1.000 Zeilen).

Es passiert aber noch etwas viel wichtigeres: Es wird eine Tabelle angelegt, die als Datenquelle das Power BI-Datenmodell hat. Wenn Sie den Artikel mit den Reverse Linked Tables gelesen haben, dann werden Sie jetzt schnell via rechter Maustaste auf der neu erzeugten Tabelle das Kontextmenü öffnen. Leider ist die Option DAX bearbeiten... ausgegraut. Enttäuscht?

Excel Kontextmenü auf einer Tabelle

MDX und DAX

Das Power Pivot-Datenmodell als auch das Power BI-Datenmodell wird von Excel nicht via DAX, sondern via MDX abgefragt Davon merkt der Benutzer im Normalfall nichts. Aus einer Pivot-Tabelle wird also intern MDX erzeugt. Wenn Sie nicht wissen, was MDX ist: macht nichts. Wichtig zu wissen ist, dass das Power BI Datenmodell beides versteht: sowohl MDX als auch DAX. Wenn wir bei der Tabelle im Kontextmenü den Eintrag Anbfrage bearbeiten... öffnen, finden wir die MDX-Abfrage, die hinter der Tabelle steckt.

Excel Tabellen Abfrage bearbeiten

In meinem Datenmodell gibt es eine Tabelle DimCalendar. Mit folgender DAX-Abfrage erhalte ich aus dieser Tabelle alle Zeilen des Jahres 2012:

Wenn Sie das im Dialog bei Befehlstext eintragen, arhalten Sie das Ergebnis in der aktuellen Tabelle. Und natürlich können dort auch deutlich komplexere Abfragen hinterlegt werden. Und voila: auf diese Weise können Sie via Excel Daten aus einem Power BI Datenmodell exportieren.

Anwendungsgebiete?

Gibt es viele. Wir haben in einem Kundenprojekt auf Basis dieser Daten automatisiert PDF-Rechnungen über Excel-Makros erzeugt. Das ist zwar sicher nicht der schönste Weg. Aber er ist ungeheur effizient, was Umsetzungszeit und Änderungsfreundlichkeit angeht.