Im Normalfall wollen wir Daten in das Excel Power Pivot Datenmodell laden um sie anschließend mittels Pivot-Tabellen auszuwerten. Dazu steht uns mit Power Query ein mächtiges Werkzeug zur Verfügung. Was aber, wenn man Daten im Excel Power Pivot Datenmodell angepasst hat und diese Daten aus dem Datenmodell exportieren möchte?
Update
Diesen Artikel haben wir aktualisiert!. Schau dir unseren neuen Artikel inklusive Video an, wie man Daten aus dem Datenmodell lädt!
Daten aus dem Datenmodell: Warum überhaupt
Aus verschiedenen Systemen haben wir mühsam Daten zusammengetragen und sorgsam aufbereitet. Und doch wollen wir die Daten wieder aus dem Datenmodell herausbekommen. Vielleicht willst du die Daten in einem anderen Tool weiterverwenden oder klassische Excel-Formeln verwenden, die dir in Excel Power Pivot fehlen. In diesem Artikel wollen wir dazu einen Weg anschauen, der ohne zusätzliche Tools auskommt. Wir legen eine Tabelle in Excel an, die mit dem Datenmodell verbunden ist. Mit einer DAX-Abfrage holen wir Daten oder Teile davon aus dem Datenmodell wieder in die Arbeitsmappe. Ganz ohne Pivot-Tabelle. Für diese Tabellen aht sich der Begriff Reverse Linked Table durch gesetzt. Eine Linked Table gibt es schon: Dabei handelt es sich um eine Tabelle, deren Daten in das Datenmodell geladen werden. Reverse also, weil wir in diesem Szenario die Daten aus dem Datenmodell zurückholen möchten.
Erster Schritt: Tabelle anlegen
Wir gehen mal davon aus, wir haben bereits Daten in unserem Datenmodell. Dann fügen wir als ersten Schritt eine neue Tabelle in unsere Excel-Arbeitsmappe ein. Dazu wählst du im Ribbon Daten den Button Vorhandene Verbindungen (der Button versteckt sich bei kleineren Bildschirmen hinter Externe Daten abrufen): Im nachfolgenden Dialog wählst du dann den Reiter Tabellen und dort die eine Tabelle, deren Daten du zurückholen möchtest. Du kannst auch eine andere wählen! Es bietet sich hier an eine Tabelle zu wählen, die möglichst wenige Zeilen hat. Wenn die Reverse Linked Table mal existiert, kannst du auf alle beliebigen Daten im Datenmodell zugreifen. Nach dem Klick auf Öffnen wählst du den Eintrag Tabelle und den Bereich, in dem die Daten erscheinen sollen (Neues Arbeitsblatt hat sich bewährt). Die Tabelle wird angelegt und mit den Daten befüllt. Aber nicht nur das, die Tabelle weiß auch wo die Daten herkommen und die Abfrage kann angepasst werden.
Zweiter Schritt: DAX Abfrage ändern
Via Rechtsklick auf der Tabelle findest du im Kontextmenü den Eintrag Tabelle und darunter DAX bearbeiten: Im folgenden Dialogfenster stellst du den Befehlstyp auf DAX und kannst nun eine DAX-Abfrage eingeben. Das Ergebnis wird dann in die Tabelle eingefügt:
Weiterverarbeitung der Daten
In Excel können die Daten in der Tabelle nun ganz normal weiterverwendet oder über Speichern unter als CSV zu Weiterverwendung abgespeichert werden. Solange sich die Struktur der Tabelle in der verwendeten DAX-Abfrage nicht grundlegend verändert (einzelne Spalten können ohne Probleme hinzugefügt bzw. entfernt werden) und Tabellenformeln am Ende (rechte Seite) der Tabelle manuell hinzugefügt werden, bleiben diese nach meiner Erfahrung auch zuverlässig erhalten. Bei umfangreichen manuellen Änderungen macht natürlich ein Backup der Arbeitsmappe vor der Änderung der DAX-Abfrage Sinn. Tipp: Leider wird im DAX bearbeiten Dialog ein einfaches Textfeld ohne jede Hilfestellung verwendet, wir empfehlen daher unseren Kunden das kostenfreie DAX Studio Excel AddIn zu installieren, das eine sehr komfortable Umgebung zur Erstellung von DAX-Abfragen mitbringt: Hier können die Abfragen entwickelt und dann per Copy&Paste in den DAX bearbeiten Dialog der entsprechenden Tabelle kopiert werden.
Komma und Semikolon
Funktionen in Excel heißen in einer deutschen Variante anders als in der englischen. Ein für mich wirklich furchtbarer Umstand. Mein Lieblingsbeispiel: Im englischen heißt die Funktion zum Löschen von Leerzeichen TRIM (wie in den meisten Programmiersprachen). In einem deutschen Excel: GLÄTTEN. Wie auch sonst.... Bei DAX hat man darauf geachtet, dass die Funktionen immer gleich heißen. Leider gibt es doch ein Unterschied: In einer englischen Installation werden Kommata verwendet, um Parameter in Funktionen zu übergeben, in einer deutschen Installation das Semikolon:
Um die Verwirrung zu vervollständigen: Bei einer deutschen Installation musst du DAX-Abfragen einer Reverse Linked Table ebenfalls Kommata verwenden! Die Kollegen von SQLBI haben beim DAX-Studio eine Funktion eingebaut, mit der du einfach Komma gegen Semikolon tauschen kannst.
Beispiele für DAX-Abfragen
Grundsätzlich können alle DAX-Abfragen verwendet werden die eine Tabelle zurückgeben. Vor jeder Abfrage muss EVALUATE stehen.
Filtern und sortieren von Daten
Spalten eingrenzen
Um nur einzelne Spalten aus den Daten zu extrahieren, muss beachtet werden, dass SUMMARIZE nur unterschiedliche Werte zurückgibt (wie ein distinct in SQL).
Sollen wirklich alle Zeilen ausgegeben werden (z.B. weil die Anzahl relevant ist) kann dies mit folgender Abfrage erreicht werden (gleichzeitig wird hier noch der Spaltenname für die Beschreibung angepasst. Wichtig ist hierbei, dass immer ein eindeutiger Schlüssel (im Beispiel die Artikelnummer) verwendet werden muss.
Daten aus mehreren Tabellen verwenden
Natürlich kann auf verbundene (Lookup-)Tabellen im Datenmodell jederzeit zugegriffen werden.
Einschränkungen
Da wir uns mit dieser Methode weiterhin in Excel befinden, funktioniert der beschriebene Weg natürlich nur, wenn die zu exportierenden Daten in eine Excel-Tabelle passen (maximal 1 Mio. Zeilen, ca. 16.000 Spalten). Wenn größere Datenmengen exportiert werden sollen wird es komplizierter. Eventuell lassen sich die Daten anhand eines Kriteriums leicht in mehrere Teile aufteilen (beispielsweise anhand der Jahreszahl), dann können die Daten pro Jahr in eine eigene Tabelle geladen werden. Hier kommt es natürlich auch darauf an, was weiter mit den Daten passieren soll.
Abschließende Hinweise
- Die DAX-Abfrage funktioniert auch bei "normalen" Excel-Tabellen (also solche, die über die Funktion Tabelle im Ribbon Einfügen erstellt wurden). Zu beachten ist, dass die Aktualisierung der Daten nur auf explizite Anforderung erfolgt (beispielsweise über den Button Alle aktualisieren im Ribbon Daten).
- Die in der Tabelle in Excel errechneten Werte können zurück nach Power Pivot importiert werden. Das wird dann Linkback-Tabelle genannt. Wie bereits in früheren Artikeln erwähnt, können jegliche Tabellen in Excel-Arbeitsblättern einfach ins Datenmodell (Power Pivot) geladen werden: Button Zu Datenmodell hinzufügen im Ribbon Power Pivot). (Obacht mit der Aktualisierungsreihenfolge wenn mehrere dieser Linkback-Tabellen wieder zusammengefasst werden etc. Aber für den normalen Fall Power Pivot -> Excel -> Power Pivot funktioniert es in der Regel gut).
- Diese DAX-Dokumentation bei Microsoft enthält eine Übersicht und Beschreibung zu allen DAX-Funktionen.
Kommentar hinterlassen