kurze Einführung in Excel Power Pivot


Sven Galonska

Youtube Channel Excel Power BI






Ganz still und leise führte Microsoft mit Office 2010 eine Reihe von Excel AddIns (Power Query, Power Pivot, Power View, Power Map) ein, die ab Office 2013 als Power BI bezeichnet werden.

Diese tools können vor allem im Bereich des Self Service BI (Fachbereich beschafft, erstellt, analysiert und visualisiert Kennzahlen eigenständig) sinnvoll eingesetzt werden.

Im folgenden soll eine kurzer Auflistung der Funktionen von Excel Power Pivot erfolgen.
Ein schematisches Self Service BI Beispiel im Einkaufscontrolling finden Sie auf dem data-science-blog.

1 heterogene Datenquellen anbinden, laden und transformieren

siehe auch Power Query (ETL Komponente)

verschiedene, verteilte Datei-Formate
- Flat files (Text-, CSV-Dateien)
- Excel Dateien
- Access Tabellen
- SQL Server Tabellen
- Datenfeeds
usw.

können mit Excel Power Pivot direkt an ein tabellarisches, In-Memory-Modell angebunden werden.

Ändern sich die Dateninhalte der Quelldateien, können diese mit einem Klick aktualisiert werden.

Selbst Transformationen der Datentypen (Text zu Zahl, Text zu Datum usw) können bereits hier erfolgen.

siehe auch schema.ini für Import-Spezifikationen

2 Kombinieren und Finden

 angebundene Datenquellen können in Relation gesetzt werden (Primär-, Fremdschlüssel), die weit verbreitete Funktion SVERWEIS() wird ersetzt durch die wesentlich einfacher handhabbare DAX-Funktion RELATED().

DAX (Data Analysis Expressions).

siehe Einführung in DAX

3 Anreicherung und Kennzahlenbildung

Mittels der umfangreichen Formelsprache DAX können die Daten bei Bedarf angereichert werden.
Daten, die nicht in den nativen Quelldaten vorhanden sind, können mit einer Geschäftslogik aus bestehenden Spalten abgleitet werden.

Kennzahlen können bereits im Modell vorberechnet werden, um sie später in einer Pivottabelle zu verwenden.

siehe auch DAX Funktionen, Daten in Formation bringen
verwendete measures und Formeln im Modell auflisten

4 Analyse und Visualisierung

 auf Basis dieses Modells kann mit der Pivottabelle eine Analyse wie gewohnt durchgeführt werden.
In Verbindung mit sog. Datenschnitten (slicer) kann ein intuitiv bedienbarer Filterkontext erstellt werden.
Slicer haben darüber hinaus den Vorteil, dass sie bessere Einsichten in die Daten gewähren.

Zur Visualisierung kann Pivot Chart oder ein weiteres tool aus der Power BI Familie - Power View -verwendet werden

siehe auch Datenschnitte (Slicer), Spaltenbezeichnungen nach Drill Down bereinigen
Maximum abzurufender Datensätze erhöhen


Beispiel semantisches Modell Power Pivot




5 Sharepoint BI solution

 In Verbindung mit Microsoft Sharepoint können die Modelle über eine Web-Oberfläche bei Bedarf verteilt werden.





Eine gute Einführung in das Thema Excel Power Pivot finden Sie bei Torsten Lieder


End-to-End Lösung (kostenlos) unter https://powerbi.microsoft.com/desktop








Kommentare

Kommentar veröffentlichen

Beliebte Posts aus diesem Blog

Vergleich mit 2 Bedingungen, INDEX(), VERGLEICH()

Wenn man 2 Tabellen mit jeweils 2 gleichen Spalten hat (zB Nachnahme, Vorname) und eine 3te Spalte (zB Telefon) von einer Tabelle in eine andere übernehmen will, stellt die Funktion SVERWEIS() keinen adäquaten Lösungsansatz mehr dar. Mit einer Kombination der Funktionen INDEX() und VERGLEICH() ist es dennoch möglich, diese Aufgabe mit Excel Formeln (Matrixformel) zu lösen. =INDEX(Tabelle1!$A$2:$C$5;VERGLEICH(A2&B2;Tabelle1!$A$2:$A$5&Tabelle1!$B$2:$B$5;0);3) wobei gilt: Tabelle 1 Spalte A zB Nachname Tabelle 1 Spalte B zB Vorname Tabelle 1 Spalte C zB Telefon (Wert welcher in Tabelle 2 bei inhaltlicher Gleichheit Spalten A, B übernommen werden soll) Formel in Tabelle 2 Spalte C aufbauen und mit STRG-SHIFT-RETURN (Matrixformel) abschließen und runterkopieren. Ein weiteres Beispiel zu Matrixformeln siehe folgenden Blog Beitrag seit Office 365 -> Microsoft 365 gibt es eine weitere, neue Alternative, Funktion XVERWEIS()
Mehr anzeigen

Dropdown Liste mit Mehrfachauswahl

Bild
Standardmäßig kann in einem Dropdownfeld nur ein Eintrag ausgewählt werden (Daten -> Datentools -> Datenüberprüfung). Ein dynamisches Dropdownfeld kann folgendermaßen realisiert werden abhängige, dynamische Dropdownfelder können folgendermaßen in Excel  realisiert werden weiterführende Informationen siehe hier Um eine Mehrfachauswahl zu realisieren, kann der folgende VBA-Code im betreffenden Arbeitsblatt verwendet werden (rechte Maustaste auf Arbeitsblatt -> Code einfügen). --- SCHNIPP -- Private Sub Worksheet_Change(ByVal Target As Range) '** Mehrfachauswahl über DropDown-Liste (Gültigkeitsprüfung) '** Einfügen im Code-Container des betreffenden Arbeitsblattes '** Dimensionierung der Variablen Dim rngDV As Range Dim wert_old As String Dim wertnew As String '** Errorhandling On Error GoTo Errorhandling '** Mehrfachauswahl im definierten Bereich (Bsp. B4:B14) durchführen If Not Application.Intersect(Target, Range("B4:B14&
Mehr anzeigen

nützliche Text Funktionen in Power Query

Bild
Im Folgenden werden ein paar in der Praxis nützliche Text Funktionen in Power Query aufgelistet. Im Voraus soll darauf hingewiesen werden, dass es 2 wesentliche Unterschiede zwischen Excel und Power Query Formeln / Funktionen gibt: case sensitivity Excel Formel unterscheiden nicht zwischen Groß- und Kleinschreibung, Power Query Formeln indes schon. Wenn eine Power Query Signatur Text.Range vorgibt, dann wird TEXT.RANGE oder text.range nicht funktionieren (case sensitive). Basis 1 versus Basis 0 Excel Formeln / Funktionen beziehen sich immer auf die Basis 1, d.h. man fängt mit 1 an zu zählen. Auf der anderen Seite startet das Zählen in einer Power Query Funktion immer mit 0, nicht 1. Vergleich Excel Text mit Power Query Funktionen Text.Contains(Text,Suchstring) gibt TRUE zurück, wenn <Suchstring> in <Text> beinhaltet ist, andernfalls FALSE z.B. Text.Contains("Power Query","Query") Rückgabewert = TRUE Text.Remove([Column],{"
Mehr anzeigen