Mit Power Query oder nun Daten abrufen und transformieren, kann man sehr performant Tabellen verbinden und benötigt keinen SVERWEIS() mehr. Allerdings ist auch Power Query darauf angewiesen, dass die Schlüsselkriterien übereinstimmen, ja es ist sogar noch strenger: Der Datentyp muss auch übereinstimmen! Irgendwann im Sommer 2019, mit einem wöchentlichen Update von Excel365 , gab es plötzlich für Power Query eine neue Funktionalität: Fuzzy Matching!
Damit können nun auch Datensätze verbunden werden, bei denen die Schlüsselkriterien ähnlich, aber nicht übereinstimmend sind. Diese neue Technik heißt Fuzzy Matching und ist als weitere Join-Variante verfügbar. Sie kann dann aktiviert werden, wenn man mit dem Befehl Abfragen zusammenführen arbeitet. Die gab es schon seit einiger Zeit für PowerBi, das ja Power Query und PowerPivot integriert hat.
Meist begegnen uns in unseren Seminaren Excel-Anwender, die durch von Microsoft verwendeten Begriff verwirrt sind und die PowerTools verwechseln oder gar keine Vorstellung von der Nützlichkeit dieser Tools haben. Denn leider fördern die gewählten Namen auch nicht gerade das Verständnis. Das Problem liegt m.E. darin, dass die verwendeten Begriff von “Programmierern” festgelegt wurden und nicht von “Kommunikatoren”. Anders ist es m.E. ist es nicht zu erklären, warum die Festlegung der Namen nicht aus der perspektive von Anwendern gewählt wurde. Doch halt, bei Power Query hat Microsoft dazu gelernt, denn es heißt ja nun “Daten abrufen und Transformieren! In diesem Beitrag sollen die Unterschiede und Gemeinsamkeiten kurz beschrieben werden. zu beiden Tools finden Sie in diesem BLOG ausführliche Beschreibungen der Funktionalitäten.
Eigentlich ist diese Frage nicht ganz exakt gestellt. Eigentlich müsste man diese Frage für Power BI Desktop-Berichte und Excel-Dashboards stellen. Denn Power BI ist inzwischen eine sehr große Plattform geworden, die alles Mögliche bietet.
Auf den ersten Blick könnte man meinen, Power BI Desktop (ab hier nur noch Power BI genannt!) und Dashboards mit Excel seien sich sehr ähnlich. Optisch ähnlich können sie wohl sein, technisch gibt es jedoch große Unterschiede. Je nach Zielsetzung sollten Sie richtig entscheiden, welches Tool Sie einsetzen. Jeder Lösungsansatz hat seine Vor- und Nachteile…
Hier mal eine völlig subjektive Einschätzungen aus ganz persönlicher Sicht und Erfahrung:
Im Internet gibt es zahlreiche Quellen für die für das Controlling wichtigen statistischen Daten. Liegen diese Daten annähernd in Tabellenform vor, lassen sich diese Daten mit mehr oder weniger großem Aufwand per Copy & Paste in ein Excel-Modell übertragen. Gerade Internetdaten werden laufend aktualisiert. Daher lassen sich diese hervorragend mit Power Query dynamisch einem Excel-Modell hinzufügen.
Viel Jahre sah der übliche Daten-Aufbereitungsprozess für Controller immer gleich aus:
Vorsysteme wie SAP liefern Daten als Export in Form von Text-, CSV- oder Excel-Dateien. Diese wurden dann in mehr oder weniger aufwändigen Verfahren aufbereitet, um saubere Listen zu bekommen, die dann in Excel gespeichert wurden. Dies Daten konnten dann mit den verschiedensten Techniken, wie etwa SUMMEWENN, TEILERGEBNIS, PivotTabellen oder Konsolidieren verdichtet und mittels Diagrammen visualisiert werden.
“Wozu brauchen wir eigentlich ACCESS noch?” werde ich oft gefragt und frage mich das auch immer wieder selbst.
Na, zum strukturierten Speichern von Daten natürlich! Datenbank eben. Das haben wir hier im BLOG ja schon diskutiert…
Ja, aber EXCEL kann doch auch speichern und ist viel leichter zu bedienen.
Das stimmt, aber man kann nicht so viele Zeilen speichern, es ist weniger performant, nicht organisiert und stabil, weder relational noch redundanz-arm, von Positionsunabhängigkeit und struktureller Flexibilität ganz zu schweigen! Und die Performance ist in jedem Fall noch schlechter, wenn durch das Herunterkopierern vieler SVERWEISE ineffizient gearbeitet wird!
Wer seine ersten Versuche mit Power Query unternimmt, kommt oft an den Punkt, an dem Power Query mit unerklärlichen Meldungen aufwartet, für die es noch dazu in den einschlägigen Foren keine Lösungen gibt. Das geht meist einher mit einer ungewöhnlichen Dauer bei den Aktualisierungen einher. Und dass, obwohl die 4GB bei der Dateigröße noch nicht erreicht wurden!
Die Ursachen dafür sind nicht immer eindeutig auszumachen, liegen unter Umständen in einer Kombination verschiedener Ursachen begründet:
Problem 1:
Nach dem Einstieg in die Nutzung von Power Query neigen die meisten Anwender dazu, mit zu vielen Einzelschritte Daten aus den Datenquellen zu verbinden und zu transformieren. So gibt es häufig redundante Schritte (z.B. Ändern des Datentyps für eine Spalte) oder zu viele einzelne Datenselektionsschritte. Mit der Kaffeetasse in der einen und der Maustaste in der anderen Hand, geht das ganz schnell! 😊 Allerdings werden diese Schritte bei einer Aktualisierung alle erneut durchgeführt. Das kann dann in der Summe dazu führen, dass Aktualisierungen außergewöhnlich lang dauern oder sich das Modell dabei “aufhängt”. Hier hilft nur “Ausmisten” und optimieren von Transformationsschritten.
Problem 2:
Power Query bietet die fantastische Möglichkeit, verschiedene Datenquellen in einem Datenmodell zu verbinden und die Daten aus diesen Quellen zu transformieren. Dabei kann ein ähnliches Problem entstehen, wie in unter 1 geschildert, auch wenn man alle Schritte optimiert. Hier hilft das sogenannte Staging-Kozept .
Abbildung: Staging-Konzeption in Anlehnung an Ken Pulse
Eine mögliche Methode, Abfragen aufzuteilen und zu ordnen, ist die Aufteilung in zwei Phasen bzw. Gruppen. Dafür sind die Begriffe Staging Queries und Loading Queries gebräuchlich.
Bei Staging Queries wird für jede Datenquelle eine Abfrage erstellt, die nur diese eine Datenquelle einliest. In dieser Abfrage finden alle Transformationen statt, es werden aber keine weiteren Datenquellen oder Abfrageergebnisse eingebunden. Bei Loading Queries werden dagegen Ergebnisse von einer oder mehreren Staging Queries weiterverarbeitet. Sie greifen nie direkt auf eine Datenquelle zu, sondern beziehen sich stattdessen auf die jeweilige Staging Query.
Problem 3:
Sind viele “Filter”-Befehle notwendig, um im Power Query-Modell Datenselektionen vornehmen zu können, leidet bei großen Datenmengen die Performance. In diesem Fall (und nicht nur in diesem) ist es vorteilhaft, als Datenquelle ein Datenbank zu verwenden. Denn technisch gesehen ist es ein großer Unterschied, ob Sie als Datenquelle eine Datei (Excel, TXT, CSV) oder eine Datenbank nutzen. Während die Dateien beispielsweise keine Möglichkeit zur Filterung von Daten bieten, gibt es in einer Datenbank viele Möglichkeiten, Daten zu filtern, bzw. zu selektieren. Power Query setzt beim Zugriff auf Datenbanken eine Technik namens Query Folding ein. Query Folding ermöglicht es Power Query, möglichst viel Verarbeitungsschritte auf die Datenquelle auszulagern. Dazu werden Transformationsschritte in die Sprache der Datenbank übersetzt (z.B. SQL-Select, Distinct) und gesendet. Enthält eine Datenbank bspw. 25 Millionen Datensätze und sollen davon nur 2.000 verwendet werden, so geschieht die Selektion in der Datenbank. Danach werden nur diese 2.000 Datensätze in das Power-Query-Modell geladen und bearbeitet.
Fazit:
Power Query ist fantastisch im spontanen, flexiblen Zugriff auf Daten und in deren Verarbeitung (Transformation). Möchten Sie Power Query für ein permanentes (Reporting-)Modell einsetzen, so sollten Sie auf jeden Fall eine Datenbank als Datenquelle verwenden. Dies ist besonders dann notwendig, wenn Sie die Historie Ihrer Daten sichern und verwenden wollen. Haben Sie Office Pro im Einsatz, dann verfügen Sie auch über eine Access-Lizenz, die Open-Source Datenbank MySQL böte sich ebenfalls an, bzw. die bei Ihnen bereits im Zusammenhang mit Ihrem ERP-System (z.B. SAP) genutzte Datenbank (SQL Server, Oracle, DB2 usw.) . Einen möglichen Workaround dazu finden Sie in diesem BLOG-Beitrag.
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.
Normalerweise setzten wir mit großer Begeisterung Power Query für den Zugriff auf verschiedene Datenquelle und die Verdichtung von Daten zu einer Tabelle ein. (Mehr dazu in diesem Beitrag).
Allerdings kann man Power Query auch für “Aufräumarbeiten” einsetzen. Haben Sie auch verschiedene Dateien doppelt und x-fach auf Ihrem Laufwerk gespeichert? Verschiedene Versionen, Stände, Varianten? Excel kann beim Aufräumen helfen! Mit Power Query können Sie ganz leicht ein Inhaltverzeichnis eines Orders oder eines ganzen Laufwerks erstellen. Mit Filtern nach Dateitypen, Häufigkeitszählung, Sortieren und Gruppieren auch nach Teilen des Dateinamens etc.
In einem Beitragvor wenigen Tagen habe ich die phantastischen Möglichkeiten von Power Query als Ersatz für den SVERWEIS beschrieben. Diese Möglichkeiten habe ich als SVERWEIS 3.0 beschrieben. Tatsächlich handelt es sich um sogenannnte JOINS (also eine Datenbanktechnik), die dabei von Power Query verwendet wird. Im Beispiel haben wir Tabellen zu einem Modell miteinander verbunden und dabei die Grundeinstellung “Linker Join” verwendet”.
Ein SQL-Join (deutsch: Verbindung) bildet aus den Datensätzen zweier Tabellen einer relationalen Datenbank eine Ergebnistabelle, deren Datensätze Attribute beider Tabellen entsprechend einer angegebenen Verbundbedingung (Join-Art) enthält.
Kürzlich in erhielt ich in einem Strategie Workshop eine ältere Excel-Datei mit einer Balanced Scorecard. Als ich damit begann Optimierungen in diesem Modell vorzunehmen, bemerkte ich die “seltsame” Schriftart in den Spalten- und Zeilenköpfen. Es handelt sich um eine Serifenschriftart! Kurzer Blick in eine andere Datei: Eindeutig keine Serifenschriftart in den Zeilen und Spaltenköpfen!
Aus bestimmten Gründen wollte und konnte ich diese Serifenschriftart nicht beibehalten. Doch wie ändern?
Vorweg, es handelt sich hier nicht um ein “kriegsentscheidendes” Excel-Wissen. Manchmal ärgert man sich aber über solche Kleinigkeiten! 😉
