Excel im betrieblichen Umfeld

Anbei ein Beispiel, wie man über einen Parameter die Anbindung einer Access Datenbank parametrisieren kann. Dies ist immer dann sinnvoll, wenn die Dateien von einem System auf ein anderes übertragen werden sollen, ohne dass die relevante Code Zeile in der Abfrage manuell angepasst werden soll. Wenn sowohl die Excel Datei mit Power Query Abfragen als auch die Access Datenbank in ein und demselben Ordner liegen funktioniert die Anbindung mittels folgender Methode: (Dieses Beispiel kann analog auch für andere Datenquellentypen angepasst werden.) 1 Neue Abfrage erstellen, folgenden Code einfügen (vorher alles entfernen) ---SCHNIPP --- (ParameterName as text) => let ParamSource = Excel.CurrentWorkbook(){[Name="Parameter"]}[Content], ParamRow = Table.SelectRows(ParamSource, each ([Parameter] = ParameterName)), Value= if Table.IsEmpty(ParamRow)=true then null else Record.Field(ParamRow{0},"Value") in Value ---SCHNAPP--- 2 Funktion in fnGetParam...

Normalerweise werden Daten aus unterschiedlichen Datenquellen (Excel Tabellen usw) in das Power Pivot Datenmodell geladen, um sie anschließend mit einer Pivot Tabelle auszuwerten. Jedoch kann es immer wieder vorkommen, dass man die sorgfältig zusammengetragenen und aufbereiteten Daten aus dem Datenmodell herausbekommen will, um sie mit weiteren tools oder klassischem Excel weiter bearbeiten zu können. Das kann u.a. dann sinnvoll sein, wenn verschiedene Datenquellen mit Power Pivot in Relation gesetzt und angereichert worden sind. Hierzu folgendermaßen vorgehen: 1 Reiter Daten, externe Daten abrufen, vorhandene Datenverbindungen 2 Tabelle in neues Arbeitsblatt laden 3 Tabelle wird angelegt und befüllt, Abfrage kann bearbeitet werden (DAX) 4 rechte Maustaste auf Tabelle ->  Tabelle -> DAX bearbeiten EVALUATE Tabellenname Power Pivotmodell gibt alle Daten zurück Beispiele für DAX Ausdrücke EVALUATE tbl_Material ...

Die Kenntnis über das Datum der letzten Aktualisierung ist zwingend notwendig, um Kennzahl richtig interpretieren zu können. Falls ein solches Datum nicht vorhanden ist, kann dieses mit folgendem Trick aus dem Datenmodell bezogen werden: In folgendem Beispiel liegt das Belegdatum der Bestellungen vor. Mittels einer DAX Funktion kann ein measure gebildet werden, welches das letzte Belegdatum ermittelt Mit Hilfe einer CUBE Funktion kann dieses ausgelesen werden: =CUBEWERT("ThisWorkbookDataModel";"[Measures].[letztes_Belegdatum]") Mittels TEXT Funktion kann das Datum im richtigen Fomat dargestellt werden: ="letztes Bestelldatum: " & TEXT(CUBEWERT("ThisWorkbookDataModel";"[Measures].[letztes_Belegdatum]");"tt.MM.jjjj")

Wenn in einem Excel-Tabellenblatt Hyperlinks auf andere Datei-Mappen im Verzeichnis vorhanden sind, kann es zu Problemen kommen, wenn die Datei mit den enthaltenen Links gespeichert und später in einen anderen Ordner verschoben wird. Hyperlinks auf andere Mappen werden von Excel in der Regel als „relative Verweise“ (z. B. Ordnername\Datei.xls) eingefügt. Nach dem Verschieben verlieren diese Links dann ihren Bezug. Daher ist es wichtig, Excel dazu zu bringen, „absolute Verweise“ (z. B. \\Computername\Ordnername\Datei.xls) zu verwenden. Ein Eintrag in den Datei-Eigenschaften zwingt Excel dazu, in der Mappe absolute Hyperlinks zu verwenden. Vorgehensweise: 1        Nach dem Einfügen der Hyperlinks Datei speichern 2        Reiter Datei -> Informationen 3        Eigenschaften -> erweiterte Eigenschaften 4        Im Dialogfenster auf Reiter Zusammenfassung wechseln 5      ...

Um ausgeblendete Zeilen und Spalten je Tabelle wieder einzublenden, kann folgender VBA Code verwendet werden. Hierzu den VBA Code in ein neues Modul der VBA Entwicklungsumgebung kopieren und über Reiter Entwicklungstools -> Makro starten: --- SCHNIPP --- Public Sub Zeilen_Spalten_einblenden()     Dim i As Integer         On Error Resume Next         For i = 1 To ActiveWorkbook.Worksheets.Count                 With ActiveWorkbook             .Worksheets(i).Cells.EntireColumn.Hidden = False             .Worksheets(i).Cells.EntireRow.Hidden = False         End With     Next i         Call MsgBox("Alle Zeilen und Spalten" + vbCrLf + "je Tabelle" + vbCrLf + "sind eingeblendet") End Sub --- SCHNAPP ---

Ausgangslage: Wenn man die Spalten M1, M2, M3 addiert, führen leere oder NULL Werte in der Ausgangstabelle dazu, dass das Ergebnis leer oder NULL ist. Verwendet man indes List.Sum() wird die Zeilensumme korrekt berechnet: = Table.AddColumn(Quelle, "Summe", each List.Sum({[M1],[M2],[M3]})) Dies gilt auch für alle anderen Aggregatfunktionen. Alternative = List.Sum(List.Range(Record.ToList(_),1)) weiterführender link Lern Video

Im Folgenden wird eine Methode mit Power Pivot und den DAX Funktionen PATH(), PATHITEM(), LOOKUPVALUE() beschrieben, um ein Merkmal / Dimension zu hierarchisieren 1 Tabelle 1, Tabelle 2 zum Datenmodell hinzufügen (Power Pivot) 2 Tabelle 1, Spalte hinzufügen DAX Funktion =PATH([Warengruppe];[Parent]) 3 weitere Spalten für Ebenen hinzufügen (hier: Spalten Ebene1, Ebene2, Ebene3, Ebene4) =LOOKUPVALUE(Tabelle2[Warengruppe_Text];Tabelle2[Warengruppe];PATHITEM(Tabelle1[Pfad];1)) Exkurs LOOKUPVALUE() 4 in Diagrammsicht wechseln und neue Hierarchie erstellen erweiterter Ansatz Unschön sind dabei leere (Unter-) Zweige (gelb markiert) Diese können durch folgende Erweiterung des Modells vermieden werden berechnete Spalte anlegen (Name HierarchieTiefe): = Pathlength ( Tabelle2[Pfad] ) 3 neue Measures anlegen: BaumTiefe:=ISFILTERED ( Tabelle2[Ebene1] ) + ISFILTERED ( Tabelle2[Ebene2] )     + ISFILTERED ( Tabelle2[Ebene3] ) + ISFI...