Die Aufgabe – Planungen und Berichte erstellen Die meisten Finanzverantwortlichen und Controller schieben zu Beginn des Monats Überstunden, um alle zugesendeten Dateien zu konsolidieren, zu überprüfen und ggf. anzupassen. Die unterschiedlichen Dateien zu konsolidieren, Monat für Monat, ist ein mühsamer, ermüdender und vor allem fehleranfälliger Prozess. Das Ziel – Viel Arbeit sparen durch automatisierten Import Jeder hat schon davon geträumt, einen extra Mitarbeiter oder Praktikanten einzustellen, der ihm zuverlässig diese Arbeit abnimmt. Aber im Ernst, in Excel gibt es (ab Version 2016 bzw. v.16.0 standardmäßig) mit Power Query ein leistungsfähiges „Import-Tool“, das genau diese regelmäßigen Arbeiten schnell und verlässlich übernehmen kann. Anm.: Anwender von Excel 2010 und 2013 können sich kostenlos ein Power Query Add-In auf der Seite von Microsoft herunterladen und installieren. Die Kompatibilität der in diesem Beitrag erläuterten Transformationen mit diesen veralteten Excel-Versionen wurde aber nicht geprüft und kann nicht garantiert werden. Die Herausforderung – Unterschiedlich strukturierte Daten aus Vorsystemen Im Idealfall sind die zu bearbeitenden Dateien (z.B. Excel-, csv- oder txt-Files) gleich aufgebaut bzw. strukturiert. D.h. jede Datei beinhaltet die gleichen Datenelemente und Spaltenüberschriften. In der Realität ist der Idealfall allerdings selten. Vielmehr sind die Ausgangsdaten häufig unterschiedlich strukturiert, da diese bspw. aus verschiedenen Vorsystemen stammen. Im Folgenden wollen wir deshalb einen solchen realitätsnahen Fall am Beispiel der Konsolidierung von sog. offenen Posten (OPOS) aus verschiedenen Buchhaltungssystemen erläutern. Diese könnten dann bspw. in unserer „Liquiditätsplanung PREMIUM“ für eine umfassenden Forecast genutzt werden. In dem hier gewählten Beispiel haben wir 3 verschiedene Gesellschaften, diese nutzen für ihre Finanzbuchhaltung Software von DATEV, Addison und Lexware Professional, so dass die jeweiligen Exportformate unterschiedliche Strukturen haben. Nicht nur stimmen die Spaltenbezeichnungen nicht überein, auch die Reihenfolge der Spalten ist unterschiedlich und auch die Spaltenanzahl divergiert. Darüber hinaus fehlen zum Teil Informationen (z.B. Angaben zur Währung). Bevor man die Daten abrufen und transformieren kann ist es notwendig, ein „Zielformat“ bzw. eine Zielstruktur festzulegen. Hier sei eine konsolidierte Liquiditätsplanung mit dem Tool „Liquiditätsplanung PREMIUM“ beabsichtigt. Dabei wird die folgende Datenstruktur für die OPOS Debitoren verlangt (vgl. Abb. 1.1). Abb. 1.1: Zielstruktur – Bezeichnungen und Reihenfolge der Spalten in finaler Tabelle Damit sind nicht nur die exakten Spaltenüberschriften vorgegeben (wichtig für die automatische spätere Konsolidierung), sondern es wird (in diesem speziellen Beispiel) auch ein Währungskürzel verlangt (z.B. EUR, USD, CAD etc.) sowie ein Soll- bzw. Haben-Kennzeichen („S“ oder „H“) in Spalte H. Zu Weiterverarbeitung innerhalb des Tools „Liquiditätsplanung PREMIUM“ muss der Saldobetrag (Spalte F) immer positiv ausgewiesen werden. Dabei haben dann „normale“ Debitoren ein SOLL-Kennzeichen in Spalte H, kreditorische Debitoren (z.B. bei Verbuchung von Gutschriften) ein Haben-Kennzeichen. Hört sich kompliziert an, ist aber genau die Logik des DATEV-Standardexportformates. Schließlich bekommen viele KMU ihre Daten von ihrem Steuerberater oder greifen von extern auf die Software „DATEV Kanzlei-Rechnungswesen“ zu. Die Lösung – Eine Import-Prozedur die alle Ausgangsdaten in das Zielformat bringt Schauen wir uns also die einzelnen Schritte an, um eine automatische Import-Prozedur zu erzeugen, die unabhängig von der Variabilität in den Datenstrukturen, den zuverlässigen Import und die Aufbereitung der Dateien gemäß der gewünschten Zielstruktur bewerkstelligt. Am Ende dieses Beitrages finden Sie einen Link, der ihnen den kostenlosen Download der zugehörigen Excel-Dateien ermöglicht. Wie das Anfügen von Daten in Power Query funktioniert Das Anfügen von Tabellen unterschiedlicher Struktur kann mit Power Query zu unerwünschten Ergebnissen führen. Für das Verständnis ist es wichtig zu wissen, dass Power Query die Struktur der ersten Tabelle als eine Art Schablone bzw. Vorlage für den Aufbau der finalen Tabelle nutzt. Dabei werden diejenigen Spalten aneinander angefügt, welche dieselbe Spaltenbezeichnung haben. Da Power Query dabei auch „case sensitive“ ist, kommt es für ein korrektes Anfügen auch darauf an, dass die Groß- und Kleinschreibung bei den Spaltenbezeichnungen identisch ist (B ≠ b). Die Funktionsweise ist exemplarisch in Abb. 1.2 dargestellt. Abb. 1.2: Unterschiedliche Spaltenbezeichnung wird als „neue Spalte“ interpretiert Sofern Spalten unterschiedliche Bezeichnungen haben (auch wenn dies nur die Groß- bzw. Kleinschreibung betrifft => Im Beispiel „b“ statt „B“ und „d“ statt „D“) werden diese als zwei unterschiedliche Spalten erkannt und nicht aneinander angefügt, sondern stattdessen wird für jede dieser (hier kleingeschriebenen) Spalten eine neue Spalte in der Ergebnistabelle angefügt. Die exakt gleiche Spaltenbeschriftung der zu übernehmenden Spalten ist also für das Zusammenfügen unterschiedlicher Tabellen (hier DATEV-, Addison- und Lexware-Export) von zentraler Wichtigkeit. Die Vorgehensweise im Überblick Folgende 3 Schritte müssen der Reihe nach umgesetzt werden: Strukturtabelle als Vorlage definieren/importieren Exportdateien importieren und transformieren Abfragen hintereinander hängen und Daten zusammenfügen   Schritt 1 Strukturtabelle definieren bzw. importieren Zunächst definieren wir für das anschließende Anfügeprozedere eine leere Strukturtabelle, deren einzige Aufgabe es ist, als Vorlage bzw. Schablone für die weiteren angefügten Tabellen zu dienen. In Excel kann diese Strukturtabelle einfach in einem Tabellenblatt manuell angelegt und in Power Query geladen werden. Alternativ kann man die Strukturtabelle auch direkt in M schreiben. Der M-Code sehe in unserem Fall folgendermaßen aus: = Table.FromRecords({[Debi Nr = null, Debi Name = null, Re Nr = null, Re Datum = null, Fälligkeit = null, Saldo = null, WKZ = null, SH Saldo = null]}) Die Tabelle enthält dann einen Datensatz mit NULL-Werten, der am Ende ggf. ausgefiltert werden muss. Wir nutzen im Weiteren die manuell erstellte Strukturtabelle, eine „intelligente“ Tabelle namens „Debi_Strukturtab“ die in der Datei „Strukturtabelle_Debi.xlsx“ abgespeichert ist. Dazu klicken wir unter „Daten“ auf „Neue Abfrage“, „Aus Datei“, „Aus Arbeitsmappe“ (siehe Abb. 1.3) und wählen anschließend im Navigator die intelligente Tabelle namens „Debi_Strukturtab“ aus. Diese leere Tabelle haben wir vorher ganz normal in Excel gemäß unseren Wünschen bzw. Anforderungen erstellt (in diesem Beispiel ist eine spätere Verwendung der Daten in der Liquiditätsplanung PREMIUM das Ziel). Sie enthält auch die bei allen anderen Importdateien zu verwendenden Spaltennamen. Abb. 1.3: Excel-Datei bzw. bestimmtes Arbeitsblatt in Power Query laden Im Anschluss an die Selektion der gewünschten Daten besteht die Möglichkeit, die Daten über die Schaltfläche „Laden“ direkt in ein entsprechendes Ziel zu laden (ein Excel-Tabellenblatt, das Excel Datenmodell, oder beides parallel), oder vor dem Laden über die Schaltfläche „Bearbeiten“ noch Modifikationen an den Daten vorzunehmen. Da wir keine Veränderungen in der leeren Tabelle vornehmen wollen wählen wir „Laden in“, haken