Ich werde oft gefragt: wo ist denn in Power BI die Pivottabelle?
Im Teil 1 unserer Reihe über Power BI Visuals möchten wir dieses Visual nun etwas näher unter die Lupe nehmen.
WeiterlesenMit 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.
Im Seminar Datenmodellierung mit Power Query und Power BI diskutieren und testen wir die Vor- und Nachteile der Modell in der Praxis.
Wir sind Mitglied in Microsoft´s Special Preferred Users Channel (SPUC) und erhalten experimentelle Features zum Testen. Erfahren Sie hier und heute, was in naher Zukunft in Excel implementiert sein wird.
Als ich neulich mal wieder ein Update von Excel erhalten habe, durfte ich eine neue Form künstlicher Intelligenz in Excel kennen lernen: den intelligenten Formel- und Modell-Optimierer (iFOpt).
WeiterlesenWir berichten an dieser Stelle von praktischen Fragestellungen und deren Lösungen aus unserem Streifenkarten-Coaching.
Bei einem Coaching auf Streifenkartenbasis in einem deutschen Consulting-Unternehmen entstand eine recht aktuelle Fragestellung: unterjährig wurde der Mehrwertsteuersatz geändert – haben wir ja alle dieses live Jahr miterlebt! Nun sollte die Berechnung von Bruttobeträgen in einer intelligenten Tabelle ab einem bestimmten Datum eben mit der neuen Mehrwertsteuer durchgeführt werden.
In einer Intelligenten Tabelle soll ab einer bestimmten Zeile eine abweichende Formel eingesetzt. Konkret: im letzten Jahr änderte sich zweimal die Mehrwertsteuer.
Intelligente Tabellen sind gar nicht mehr so clever, wenn innerhalb einer Spalte die Formel wechseln soll. Dafür sind sie nicht gemacht!
Nun ließe sich zwar mit einer WENN-Funktion dieser Wechsel abbilden, aber wer weiß, ob nicht die Mehrwertsteuer in absehbarer Zeit wieder geändert wird? Ich würde dringendst abraten, das dann wieder von Hand in einer Formel zu pflegen…
Schlechtes Beispiel:
=WENN(Monat(Rechnungsdatum)<7;1,19*NettoBetrag;1,16*Nettobetrag)
Geben wir die Mehrwertsteuersätze einfach in eine kleine, clevere Tabelle, der wir natürlich sofort einen aussagefähigen Namen gegeben haben: tblMwSt. Die Mehrwertsteuersätze gelten ab dem jeweils angegebenen Tag.
Und dann nutzen wir unseren guten alten Freund, den SVERWEIS, um die korrekten Werte zuzuordnen.
Formel für den Bruttopreis:
=[@[Preis Netto]]*(1+SVERWEIS([@Rechnungsdatum];tblMwSt;2;-1))
Hierbei ist der Parameter Bereich_Verweis auf -1 zu setzen, da ja nicht für jeden Monat ein MwSt.-Satz abgegeben werden soll, sondern nur, ab wann ein MwSt.-Satz gilt (daher auch der komische Wert 01.01.1901, das das erste Datum der Microsoft-Welt darstellt). Kommen Sie bitte auch hier niemals auf die Idee, das mit geschachtelten WENN-Formeln zu versuchen!
Dies und Ähnliches bearbeiten wir im Seminar Excel im Finanzwesen und Controlling.
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.
Nach einem Seminar möchte man sofort die gerade kennengelernten Techniken einsetzen. Leider wartet meist der Alltag mit zahlreichen Aufgaben auf und verhindert die schnelle Umsetzung. Wenn es dann endlich soweit ist, ist Einiges schon halb vergessen und das Projekt stockt. Denn irgendwie müssen dafür ja immer Zeitfenster im Alltag gefunden werden.
Und wie war das nochmal mit der Funktion XY?
Schnell gegoogelt, aber leider findet sich erst nach langem Suchen nur eine leidlich ähnliche Fragestellung…
Hier kann die Streifenkarte hilfreich sein, da gemeinsam an Aufgaben gearbeitet wird und die beste Lösung sofort gefunden werden kann. Und dies geschieht stundenweise oder auch mal einen Vormittag lang und ist so sehr produktiv. So lässt sich die Zeit gut nutzen um zielorientiert online an Dashboards, Reporting-Lösungen und/oder Power Query-Modellen zu arbeiten. Und dabei zahlen Sie nur für die Coaching-Zeit, nicht für die 30 Jahre Erfahrung, die dahinter stecken!
Hier eine lockere Serie von realisierten Coachings per Streifenkarte:
So auch bei einem langjährigen Auftraggeber (mittelständisches Unternehmen in Deutschland):
Ein findiger Werkstudent hatte ein ausgefeiltes EXCEL-Modell für eine Variantenkalkulation mit komplexen Formeln inkl. viel Programmierung in VBA erstellt. Sie ahnen es schon? Genau, Murphy’s law! Das Modell musste dringend angepasst und erweitert werden. Und der Werkstudent war nicht mehr im Unternehmen…
Ein Anruf, bei uns, ein Termincheck und schon waren wir behilflich. Dank einer Erfahrung von 30 Jahren konnten wir uns recht schnell in das Modell finden und verschiedenste Lösungen entwickeln. Ein Vorteil, wenn man seit vielen Jahren unterschiedliche Unternehmen, deren Geschäftsmodelle, Prozesse und Needs kennenlernen darf. So wird unsere Analysefähigkeit und Kreativität gefordert! 🙂
Im aktuellen Fall konnten wir die bestehende Datei analysieren, optimieren und dem Auftraggeber die Funktionsweise dieses wichtigen Excel-Modells so transparent machen, dass er künftige Änderungen ohne uns durchführen kann!
Zunächst gab es Gerüchte… das unter Controllern verpönte, aber leider oft abgelieferte Dateiformat PDF könne vielleicht… eines Tages… in PowerBI eingelesen werden. Eines Tages war dann plötzlich die Möglichkeit da, den Connector zu PDF in PowerBI zu nutzen, aber nur als Vorschau-Feature… in einem bestimmten Update Kanal… wenn man es extra aktivierte… Dann war es endlich soweit: PDF-Connector für PowerBI! Für alle! Einfach im Menü! Aber eben leider nur in PowerBI, nicht in Excel.
In meinen Seminaren habe ich immer wieder darauf hingewiesen, dass Power Query (PQ) in PowerBI und PQ in Excel zwar funktional etwa dasselbe leisten, sehr ähnlich aussehen,dann aber manchmal bei der Funktionssyntax und vor allem in den Datenquellen große Unterschiede aufweisen, da – man kann es kaum glauben – dies zwei völlig unterschiedliche Produkte sind! Es sind auch zwei unterschiedliche Entwicklerteams in Redmont mit der Entwicklung beauftragt. Ich habe jedoch die Hoffnung nie aufgegeben, dass auch PQ in Excel diesen PDF-Connector bekommen könnte, da schon viele der Features von PQ für PowerBI erhalten hat. Zwar immer zeitverzögert, aber immerhin.
Und nun ist er da: der Connector zu PDF für Power Query in Excel! Leider nur, wenn Sie ein Office365 Abonnement haben… die neuesten Updates haben…
An einer einfachen Beispieldatei sehen Sie, wie dies funktioniert. Leider sind in Power Query die Pfade von Haus aus absolut, sodass Sie diese in Ihrer Umgebung zunächst anpassen müssen.
Falls Sie selbst mit dem PDF-Connector experimentieren wollen, dann starten Sie über Daten abrufen und transformieren / aus Datei / aus PDF …
Ein wenig holprig ist die Implementierung dennoch.
Top oder Flop? Ich bin für Top, wenn sich manchmal ein Import aus PDF nicht umgehen lässt, aber es ist immer noch Topper, eine direkte Verbindungen zur Quelldatenbank aufzubauen.
PS Meine Hoffnung auf PDF für PowerQuery wurde also erhört. Die Hoffnung auf Power Query für Access – lediglich für den Datenimport, nicht die Datenmanipulation – gebe ich jedoch bald auf. Mein Verbesserungsvorschlag auf Access Uservoice gehörte damals zu den am höchst bewerteten Beiträgen und wurde doch von Microsoft doch abgelehnt: no current plan… 🙁
——————————————————————————————————————————-
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.
Excel365 erhält derzeit viele neue Funktionen, die Anwendern von Datenbanken und der Pivot-Tabelle schon lange gute Dienste leisten.
Die Funktion EINDEUTIG (englische Version: UNIQUE) aus der Funktionskategorie Nachschlagen und Verweisen ermöglicht es, in Zellbereichen Funktionen der Pivot-Tabelle nachzubilden, bspw. Werte eindeutig dazustellen und Dubletten zu unterdrücken, alle eindeutigen Werte-Kombinationen einer Liste darzustellen und vieles mehr.
Diese Techniken sind Datenbankanwendern in der Sprache SQL als GROUP BY-Befehl bekannt.
Doch sehen Sie selbst im Beispiel…
Sortieren von Daten ist „Teufelszeug“! 😉
Das Sortieren von Daten in Excel verletzt die Forderung, Daten im Original aufzubewahren! Denn der Ursprung der Daten lässt sich nicht mehr (im Gegensatz zu Datenbanken) zurückverfolgen oder wiederherstellen. Oder bei falscher Markierung in der Tabelle wird u.U. nur ein Teil der Daten sortiert.
Wie auch immer: die Sortierung lässt nicht mehr zum Ursprungszustand zurückverfolgen.
Microsoft hat mit seinem April-Update die seit 2018 angekündigten neuen Features endlich zur Verfügung gestellt. Eine davon ist die Funktion SORTIERENNACH().
SORTIERENNACH() sortiert die Inhalte eines Bereichs oder einer Matrix anhand der Werte in einem anderen Bereich oder einer Matrix. Dabei regiert die Funktion auf eine Veränderung der Größe des Quellbereichs, was etwas mit dem neuen Konzept der Dynamischen Arrays zu tun hat.
Wer noch nicht mit Excel365 arbeitet, der setzt bitte weiterhin die Funktion KKLEINSTE() und die „Intelligente Tabelle“ ein, wir in unserem Beispiel zum Sortieren eines Balkendiagramms ohne VBA?
Jetzt haben wir dieses Beispiel eines automatisch sortierten Balken-Diagramms mit der Funktion SORTIERENNACH() aufbereitet. Sehen Sie hier unser Beispiel…
————————————————————————————————————————–
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.
In einem Projekt für ein Schweizer Logistik-Unternehmen habe ich beim Einsatz von Power Query geringfügige Abweichungen bei den Kontrollsummen entdeckt. Die Ursache war schnell gefunden: Rundungsdifferenzen!
Diese entstanden, da die im CSV-Format auf SharePoint gespeicherten Dateien eine geringere Genauigkeit bei den Dezimalstellen aufweisen, Excel jedoch mit 15 Stellen Genauigkeit rechnet.
Ich dachte mir: in Excel würde ich nun Runden, dann mache ich das doch in Power Query genauso! Schließlich gibt es in Power Query zahlreiche Funktionen, die den Tabellen-Funktionen von Excel (scheinbar!) entsprechen. Also mal schnell die Funktion ROUND in die Abfragen eingebaut und schon sah das Ergebnis besser aus.
Bis ich dann in den Testdaten wieder eine Abweichung entdeckte. Und nur in bestimmten Fällen…
Das brachte mich dazu, das Thema Runden in Power Query genauer zu testen.
Und siehe da:
Es gibt unglaublich viele Methoden, um mit Power Query Dezimalzahlen zu runden, jedoch führt nur eine Methode zum gewünschten Ziel!
Vermeiden Sie Rundungsfehler in Power Query!
Sehen Sie hierzu das Beispiel Fehlerquelle_Runden…
————————————————————————————————————————–
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.
Nach unseren ersten ganztägigen Online-Seminare haben wir ein neues Format getestet: die Aufteilung eines zweitägigen Präsenz-Seminars auf vier Vormittage.
Dazu habe ich vorab die obligatorischen Pausen-Snacks per Post an die Teilnehmer verschickt, damit sich „echte“ Seminaratmosphäre einstellt! Für Kaffee, Tee und Getränke musste jeder selbst sorgen…
