Wir 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 du Excel Power Pivot kennst, dann sind dir vielleicht Reverse Linked Table ein Begriff. Mit diesen Tabellen kann man in Excel auf das Datenmodell in Power Pivot zugreifen. Und auf diese Weise die Daten 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.

Wie man solche Tabellen anlegt findest du hier:

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 hast du 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 herunte (früher wurde eine ODC-Datei heruntergeladen, also nur die Datenquelleinformationen, die man mit einer neuen Ecel-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 öffnest du die Excel-Datei aus dem Power BI-Service und wechselst im Menü auf den Reiter Daten. Dort öffnest du das FlyOut mit dem Namen Abfragen und Verbindungen über den entsprechenden Menüpunkt. In diesem FlyOut siehst du normalerweise die Power Query-Abfragen, wenn du mit Excel Power Pivot arbeitest. Das FlyOut hat noch einen zweiten Reiter mit Namen Verbindungen. Dort findest du die Datenquellen-Informationen zum Power BI-Datenmodell.

Abfragen und Verbindungen in Excel

Via Doppelklick öffnet sich der Eigenschaftsdialog der Datenquelle. Dort findest du im Reiter Definition den sogenannten ConnectionString. Viel wichtiger: unten findest du eine Schaltfläche Verbindungsdatei exportieren.

Excel Dialog Verbindungseigenschaften

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

Excel Dialog Vorhandene Verbindungen

Dort findest du die Schaltfläche Nach weiteren Elementen suchen... über die du die gerade exportierte Datei wieder importieren kannst. Excel möchte gleich eine Pivot-Tabelle anlegen. Falls du das verschieben möchtest, wähle 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. Wundere dich also nicht, dass du ein Feld wie Preis nicht in die Pivot-Tabelle ziehen kannst. Es muss schon ein Measure sein. Falls das für dich nicht logisch klingt, lies unbedingt den Artikel Implizite Measures - mehr Fluch als Segen auf unserem Blog.

Falls du die Datenverbindung neu importiert hast und nur die Verbindung erstellt hast, musst du eine neue Pivot-Tabelle unter Nutzung der neu importierten Datenquelle anlegen. Dazu wählst du im Menü im Reiter Einfügen die Funktion PivotTable und im Dialog wählst du 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 du die importierte Power BI Datenquelle finden solltest. Der Name fängt an mit pbiazure://...

Wähle aus der Datenmodell-Feldliste eine 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 du das von Power BI kennst: 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 du den Artikel mit den Reverse Linked Tables gelesen hast, dann wirst du jetzt ganz 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 du nicht weißt, 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 du das in dem Dialog bei Befehlstext einträgst, erhälst du das Ergebnis in der aktuellen Tabelle. Und natürlich können dort auch deutlich komplexere Abfragen hinterlegt werden. Und voila: auf diese Weise kannst du 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.