Wenn Sie mit aus Ihren IT-Systemen importierten Dateien arbeiten, kommt es immer wieder vor, dass Zeichenfolgen verbunden (=verkettet) werden müssen. Die Ursachen dafür sind vielfältig. Wenn Sie mit Power Query Dateien in ein Modell laden, um Sie dann zu transformieren (z.B. Zeichenfolgen verketten), so gibt es für die Verbindung zwei Möglichkeiten. Beide werden in diesem Beitrag vorgestellt. Power Query ermöglicht es nämlich zwei oder mehr Spalten Abfrage zusammenführen. Sie können Spalten zusammenführen, um sie durch eine zusammengeführte Spalte zu ersetzen, oder Sie können neben den Spalten, die zusammengeführt sind, eine neue zusammengeführte Spalte erstellen.
Zeichen in einer Zeichenkette trennen und auf mehrere Spalten aufteilen? – Ein alter Hut werden Sie abwinken, wenn Sie diese Zeilen lesen. Das lösen Sie in Excel mit Daten ●Text in Spalten, oder mit einer der Text-Funktionen oder in Power Query mit Spalte Teilen. Dabei nutzen Sie die festen Spaltenbreiten aus, oder dass es Trennzeichen gibt, nach denen die Trennung vollzogen werden kann. Was aber, wenn Sie eine Numerische/Nichtnummerische Zeichenfolge trennen wollen, bei der der Übergang nicht an der gleichen Stelle erfolgt? Dafür gibt es in Power Query einen tollen Befehl, besser gesagt zwei! Getreu dem Motto: Rechts die Maus und links die Kaffeetasse!
Mit seinen Power Tools (Power Query, Power Pivot, Power BI & Co. ) stellt Microsoft Business Analytics-Lösungen zur Verfügung, mit denen Sie Daten aus unterschiedlichsten Datenquellen extrahieren und in einem Modell zusammenführen, dann verdichten, analysieren und visualisieren können.
Die Informationen, die aus den Daten gewonnen werden sollen dabei valide, praxisbezogen und verständlich sein. Noch dazu sollen die Modelle Auswertungen nach wechselnden Kriterien unterstützen und flexibel erweiterbar sein.
Mit welchen Methoden kann man dieses komplexe Ziel erreichen?
Für die Verständlichkeit sorgen Sie selbst durch die Auswahl des Reporting Designs und der Visualisierungen. Die Reports selbst müssen aber auch performant sein, da lange Wartezeiten bei Aktualisieren oder beim Drilldown den Arbeits- und Gedankenfluss stören. Und sie müssen flexibel sein, damit man bei der Datenanalyse relativ frei nach beliebigen Kriterien auswerten kann, also nicht durch die Datenstrukturen schon in bestimmte Richtungen gezwungen wird. Beispielsweise kann eine Vertriebsanalyse sowohl nach Datum, wie nach Region oder Produkt interessant sein. Daneben könnte aber auch eine Analyse nach Kundengruppe, Bestellmenge oder gar Wetter interessant sein.
Setzen wir einfach einmal voraus, dass die erforderlichen Informationen überhaupt vorhanden sind (was leider in der Realität im ersten Schritt nicht mal sicher ist). Damit Daten flexibel ausgewertet werden können, darf die Struktur der Speicherung möglichst wenige Einschränkungen vorgeben.
Hier könnte man zwischen zwei grundsätzlich unterschiedlichen Ansätzen der Modellierung unterscheiden: Stern – und Schneeflocken-Schema
Meist gibt es eine zentrale Tabelle, die sogenannte Faktentabelle, die die Daten enthält, etwa Umsatzzahlen, Absatzzahlen, Plandaten, Kontostände etc. Diese sind meist numerisch und besitzen eine Skala wie Stück, Euro, Tage oder ähnliches. Sie besitzen auch Zusatzinformationen etwa zu Kunden, Regionen, Produkten. Diese Informationen werden aber nicht in der zentralen Datentabelle gespeichert – dort sind lediglich Schlüsselkriterien hinterlegt, die auf andere Tabellen verweisen.
Zu diesen Schlüssel gibt es mehrere Zusatztabellen, sogenannte Dimensionstabellen.
Bei Stern-Schema beziehen sich nun alle oben genannten Schlüssel der Faktentabelle direkt auf eine Dimensionstabelle, aus der die Zusatzinformationen dann ausgelesen werden können (man kann sich das wie den guten alten SVERWEIS vorstellen).
Stern-Schema (Quelle: Microsoft)
Beim Schneeflockenmodell dagegen können auch den Dimensionstabellen weitere Schlüssel und nochmal Dimensionstabellen zugeordnet werden.
Schneeflocken-Schema (Quelle: Wikipedia)
Für eine flexible Auswertung ist das Stern-Schema am besten geeignet, da es erlaubt, mithilfe von Gruppieren, Pivotieren etc. beliebige Verdichtungen in den gewünschten Dimensionen zu erstellen. In EXCEL-Denkweise: es lässt uns, wie in einer Pivottabelle, die Gruppierungkriterien beliebig tauschen.
Beim Schneeflocken-Schema wäre dies schwerer, da für unterschiedliche Auswertungen jeweils eigene Abfragen erzeugt werden müssten.
Zur Aufbereitung der Rohdaten dienen Tools wie Power Query, die eine Normalisierung der Daten, d.h. die Aufteilung der gelieferten Daten in Dimensions- und Faktentabellen mit relativ wenig Aufwand ermöglichen.
In einem der großen Unternehmen der Pharmabranche haben wir einen Performance-Report zu den Themen Prozesszeiten, Reklamationsquoten usw. überarbeitet. War er vorher 75 MB groß und mit dem Aufwand von drei Personentagen pro Monat aktualisiert, so ist er jetzt 750KB groß und permanent aktualisierbar, bei verbesserter Performance. Aufwand für die Entwicklung der Power Query-Lösung ca. 1 Personentag. Mehr dazu hier.
Mit Power Query können Sie externe Daten in Ihre Excel-Modell importieren und dabei diese Daten (Struktur, Datentypen usw.) so verändern, dass sie in die gewünschte Zielstruktur geladen werden und Ihren Anforderungen entsprechen. Sie investieren also lediglich einmalig Zeit, um den Prozess zum Laden & Transformieren der gewünschten Daten zu entwickeln! Anschließend können Sie die Daten manuell oder automatisch aktualisieren, um so im Zielmodell die Daten permanent mit dem neuesten Stand der Datenquellen zu erhalten.
In diesem Beitrag erhalten Sie eine (nicht vollständige, aber ständig wachsende) Übersicht von Power Query-Lösungen aus unserer Projekten:
Seit 2018 dürfen wir den HR-Bereich eines DAX-Unternehmens unterstützen. Dort wird SAP Success Factors eingesetzt, um die Weiterbildung und Personalentwicklung im Konzern zu organisieren.
SAP SuccessFactors ist ein umfangreiches und mächtiges Cloud-basiertes Tool, um den HR-Bereich eines Unternehmens mit zahlreichen Modulen zu steuern. Wenn es allerdings darum geht, ein paar Berichte zu erstellen, scheint es etwas problematisch zu werden. Wir stützen uns dabei auf die Erfahrungen und Aussagen der Mitarbeiter unseres Auftraggebers zum Modul “Learning”. Das Modul scheint sehr wenig individualisierbar zu sein. Daher nutzen die Mitarbeiter die Exportfunktionen und arbeiten mit den Flat File-ähnlichen Excel- oder CSV-Dateien weiter. Aufgrund der Struktur der exportierten Dateien, gestaltet sich die Bearbeitung in Excel etwas problematisch.
Wie geht man beim Aufbau eines Dashboards vor? Was ist dabei zu beachten? Wie sollten die Daten verdichtet werden? Mit welchen Excel-Techniken sollte man arbeiten? Wir denken dabei vom Ziel her. Hier folgen Hinweise, die Ihnen nach unserer Meinung beim Aufbau eines Dashboards helfen können. Denn zu 80% besteht ein Dashboards aus einem Datenmodell, nur zu 20% aus “schönen Diagrammen&Tabellen”!
Ich beobachte bei Projekten meist eine Vorgehensweise, bei der erst einmal alle verfügbaren Daten in irgendeiner Form “eingesammelt” werden, meist nach der Devise: “Was ich schon habe, kann mir niemand mehr wegnehmen”. Dann folgt eine Überlegung, welche Kennzahl können daraus gebildet , welche Diagramme erzeugt werden. Das geht einher mit den Überlegungen, welche Informationen die Empfänger wohl haben möchten (oder müssten?), ohne dass dazu ein Dialog geführt wird. Daraus ergeben sich nach meiner Beobachtung sehr unhandliche Modelle, obwohl eine andere Vorgehensweise mlgw. zielführender sein könnte.
Empfängerorientierung als Voraussetzung
Im ersten Schritt würde ich den Informationsbedarf der Empfänger klären, verbunden mit der Frage nach Vorstellungen zur Usability. Dazu kann man bereits einige dynamische Diagramme, Tabellen, Kennzahlenbäume vorstellen und das Feedback aufnehmen Durch die Reaktion auf solche Beispiele (ohne echte Daten) kann man oft auch erkennen, was Empfänger wirklich wollen und benötigen. Denn oft ist ihnen das selbst nicht bewusst, wird aber mit solchen Beispielen klarer Beispiele.
Umsetzung – zielorientiert und top down
Mit diesen recht konkreten Vorstellungen kann man sich an die eigentliche Umsetzung machen, denn nun hat man eine Zielvorgabe und kann von dieser aus rückwärts gehen:
Vom Ziel ausgehend (interaktive Diagramme, Tabellen, Kennzahlenbäume), beschreiben Sie die dafür notwendigen Daten. Denn vielleicht werden noch besondere Elemente zu Visualisierung gewünscht. Auch dafür werden Zahlen benötigt, denn Diagramme sind visualisierte Zahlen. Diese Zahlen, die nicht aus den Datenquellen stammen, müssen automatisch generiert werden und in einer bestimmten Struktur bereitgestellt. Dabei helfen zahlreiche Excel-Funktionen.
Die Beschreibung der im ersten Schritt gewünschten Visualisierungen verdeutlicht die Zahlen, die dafür in Form von Tabellen oder Einzelwerten vorliegen müssen. Vor allem werden auf dieser Ebene die Daten für verschiedene Selektionskriterien (Kategorie, Zeit, Geografie) bereitgestellt und aufbereitet.
Die Beschreibung der im Schritt davor erwünschten Datenselektion schafft Klarheit darüber, welche Daten aus den Datenquellen benötigt werden. Spätestens hier sollte man sich darüber im Klaren sein, in welchem Umfang historische Daten aus den Datenquellen übernommen werden müssen.
Welche Daten werden aus welchen Quellen benötigt? Wie stark müssen die Daten transformiert und verdichtet werden? Wie wichtig ist eine permanente oder monatliche Aktualisierung?
Die Abbildung zeigt alle Schritte auf und zeigt die dafür benötigten oder zur Verfügung stehenden Excel-Techniken..
In der Beispieldatei werden die wünschenswerten Excel-Techniken zur Erstellung eines Dashboards aufgelistet. Planung, Tests mit den Datenquellen und Erstellung des Dashboards benötigen nach meiner Erfahrung nach dieser Vorgehensweise ca. 3-5 Personentage.
Immer wieder werde ich auf Publikationen, Dokumente oder Posts in den Sozialen Medien aufmerksam, in denen Nachteile von Excel aufgezählt werden. Interessanterweise zählt der jeweilige Autor nicht nur die Nachteile von Excel auf, sondern stellt meist auch noch eine Alternative zu Excel vor. Es handelt sich dann meist um ein Tool einer Softwareschmiede, auf dessen payroll der Autor auch zu finden ist. Seit 1989 arbeite ich mit Excel und kenne seine Stärken und weiß, wie man seine “Schwächen” umgehen kann. Kürzlich habe ich wieder ein Whitepaper gefunden. Hier eine Serie von Gegendarstellungen zu den darin aufgestellten Behauptungen.
Gegenrede:
Das hier geschilderte angebliche Excel-Problem ist keines. Wenn man mal davon absieht, dass die hier geschilderte Arbeitsweise in den 80er und frühen 90er Jahren des letzten Jahrhunderts verbreitet war, gab es auch damals schon SAP und Datenbanken. Dann ist das Problem keines der Applikation, sondern eher deren (falsche) Anwendung. Und mit PowerPivot ist es auch in Excel möglich, mehrdimensionale Datenmodelle zu erstellen, auch wenn es noch 10.000 Spreadsheets geben sollte.
Aber von Anfang an:
Was will uns der Autor eigentlich mit seinen Behauptungen mitteilen? Was sollen “unbegrenzte Datenmodelle für alle Dimensionen des Finanzbereichs” sein? Ich hoffe doch ganz stark, dass der Autor mit “seinem” Tool bei “seinen” Kunden jeweils nur ein Datenmodell erstellt, denn sonst bekommt sein Kunde Probleme mit dem Ansatz des “Single-source-of-truth“!
Was will uns der Autor damit mitteilen, wenn er behauptet die Anwender wären gezwungen mit “Berichten mehrerer Abteilungen zu arbeiten, statt mit dynamischen Masken”? Ich kann hier nicht erkennen, was das mit Mehrdimensionalität zu tun haben soll?
Zusammenfassend liest sich dieses Problem erst einmal als schrecklicher Nachteil, stellt sich aber als unnötige Dramatisierung heraus. Warum nur?
Wenn Ihnen dieser Beitrag gefallen hat, dann teilen Sie ihn gerne. Falls Sie Anmerkungen haben, schreiben Sie bitte einen Kommentar, oder senden Sie mir eine Mail an info@prt.de.
Neulich im Projekt mussten mehrere CSV-Dateien in einem Modell zusammengefasst und darin u.a. Kontierungen aufgefüllt werden. Die CSV-Dateien wurden aus einem IT-System exportiert und enthielten u.a. Kontierungen. Beim Export gingen die führenden Nullen verloren, so dass in der CSV-Datei jeweils “nur” eine Zahl zu finden war. Für die Lösung dieser Aufgabe habe ich natürlich Power Query eingesetzt, zumal alle Dateien in einem Ordner gespeichert waren und laufend welche dazu kamen.
In Excel haber ich in früheren Versionen die Funktionen VERKETTEN(), WIEDERHOLEN() und LÄNGE() kombiniert, in Power Query gibt es die interessante Funktionen Text.PadEnd() / Text.PadStart(), die Zeichen auffüllen. Genauso, wie die Funktion WIEDERHOLEN(), liefern auch die Power Query-Funktionen eine Zeichenfolge vom Datentyp TEXT:
—————————————————————————————————-
Syntax Text.PadEnd()
Text.PadEnd(text as nullable text, count as number, optional character as nullable text)
Das Argument text ist eine Zeichenfolge oder der Inhalt eines Feldes (oder einer “Spalte”) in Power Query, der oder die durch Einfügen eines anderen Zeichens zu einer definierten Zeichenlänge nach der vorhandenen Zeichenfolge aufgefüllt werden soll.
Die gewünschte Zeichenlänge wird durch das Argument count definiert und muss numerisch angegeben werden.
Mit dem Argument character wird das zum Auffüllen verwendete Zeichen angegeben. Werden Zahlen zum Auffüllen verwendet, so müssen diese in Anführungszeichen gesetzt werden (z.B. “0”).
————————————————————————————-
Syntax Text.PadStart
Text.PadStart(text as nullable text, count as number, optional character as nullable text)
Die Argumente haben die gleich Bedeutung wie bei der Funktion Text.PadEnd(), nur das hier die Zeichen vor die ursprüngliche Zeichenfolge gesetzt werden.
Wenn Ihnen dieser Beitrag gefallen hat, dann teilen Sie ihn gerne. Falls Sie Anmerkungen haben, schreiben Sie bitte einen Kommentar, oder senden Sie mir eine Mail an info@prt.de.
Immer wieder werde ich auf Publikationen, Dokumente oder Posts in den Sozialen Medien aufmerksam, in denen Nachteile von Excel aufgezählt werden. Interessanterweise zählt der jeweilige Autor nicht nur die Nachteile von Excel auf, sondern stellt meist auch noch eine Alternative zu Excel vor. Es handelt sich dann meist um ein Tool einer Softwareschmiede, auf dessen payroll der Autor auch zu finden ist. Seit 1989 arbeite ich mit Excel und kenne seine Stärken und weiß, wie man seine “Schwächen” umgehen kann. Kürzlich habe ich wieder ein Whitepaper gefunden. Hier eine Serie von Gegendarstellungen zu den darin aufgestellten Behauptungen.
Gegenrede:
Die Argumentation ist in meinen Augen ein wenig “schräg”. Wieso kommt hier auf einmal PowerPoint ins Spiel? Natürlich wird in vielen Unternehmen PowerPoint für Managementberichte eingesetzt, und natürlich ist das nicht sinnvoll, aber was hat das mit Excel zu tun? Was bedeutet zeitgemäß? Es hat schon seine Gründe, warum Microsoft die Power-Tools entwickelt hat und gerade Excel365 mit neuen Funktionen und dynamischen Arrays als Frontend “fit” für große Datenvolumina macht.
Diese PowerPivot und Power Query stehen seit Excel2010 zur Verfügung und können als Basis für interaktive Excel-Dashboards genutzt werden. Wenn man weiß, wie es geht (der Autor weiß es offensichtlich nicht oder will es nicht wissen), dann kann man sehr effiziente und sehr effektive Excel-Modell erstellen, Daten detailliert analysieren, zusätzliche Informationen ergänzen und stets aktuell ein Gesamtbild eines Unternehmens liefern.
——————————————————————————————————————————-
Wenn Ihnen dieser Beitrag gefallen hat, dann teilen Sie ihn gerne. Falls Sie Anmerkungen haben, schreiben Sie bitte einen Kommentar, oder senden Sie mir eine Mail an info@prt.de.
