Zusammenfassung

Lernen Sie einige häufige Formatierungsprobleme im Zusammenhang mit Hauptbuchdateien kennen und erfahren Sie, wie Sie den Formatierungsprozess mit Excel Power Query automatisieren können.

In diesem Artikel verwenden wir den Hauptbuchexport aus QuickBooks Desktop, jedoch gelten viele der Schritte auch für andere Exporte von Buchhaltungssoftware.

Anwendungsfälle

Wenn Sie viele Hauptbuchdateien aus derselben Buchhaltungssoftware haben, die alle im gleichen Format vorliegen und vor dem Einlesen in MindBridge umgewandelt werden müssen, dann könnte Excel Power Query das perfekte Tool für Sie sein.

Sobald eine Datei in ein einlesbares Format umgewandelt wurde und die dabei durchgeführten Schritte aufgezeichnet wurden, können Sie jede beliebige Datei sofort in dasselbe Format umwandeln!

Das Problem

Nachfolgend sehen Sie ein Beispiel für die Formatierung eines QuickBooks Desktop Hauptbuchberichts, der unserem Extraktionsleitfaden entnommen wurde.

Die Probleme, die verhindern, dass diese Datei von MindBridge eingelesen wird, sind:

1. Die Daten sind nach Transaktionen gruppiert. Die Spalten Transaktionsnummer, Art, Eingegeben / Letzte Änderung, Zuletzt geändert von, Datum und Nummer müssen bis zu jeder Zeile der Transaktion mit Daten gefüllt werden.
2. Die Transaktionsnummer ist kein Pflichtfeld in QuickBooks, daher haben nicht alle Transaktionen eine Transaktionsnummer.
3. Die Kontonummer und der Name belegen das gleiche Feld.
4. Es gibt fremde Zeilen, wie zum Beispiel Summen.

Die Lösung

1. Füllen Sie die leeren Zeilen mit den entsprechenden Daten aus jeder Spalte unter Verwendung der Funktion Abwärts füllen.
2. Achten Sie beim Füllen der Daten darauf, dass die leeren Transaktionsnummerfelder (z. B. die Zellen B5 und B27) beibehalten und nicht unter Verwendung einer benutzerdefinierten Spalte mit falschen Daten aufgefüllt werden.
3. Teilen Sie die Kontonummer und den Namen in separate Spalten auf, indem Sie die Funktion Spalte nach Trennzeichen teilen verwenden.
4. Filtern Sie die Leerzeichen in einer Spalte heraus, die Daten in jeder Buchungszeile enthält.
   
   

Abrufen und Transformieren von Daten

Um mit der Transformation der Daten zu beginnen, müssen wir die Daten zunächst in Power Query importieren. Je nach Dateityp gibt es verschiedene Möglichkeiten, dies zu tun.

1. Öffnen Sie entweder ein neues Arbeitsblatt oder Ihre Hauptbuchdatei in Excel.
2. Navigieren Sie zum Menüband Daten am oberen Rand. Innerhalb des Abschnitts Daten abrufen & transformieren finden Sie eine Reihe von Optionen zum Abrufen der Daten, je nachdem, ob sich die Daten in Ihrem geöffneten Arbeitsblatt oder in einer separaten Datei befinden.
3. Wählen Sie die geeignete Methode für Ihre Daten. In diesem Beispiel wählen wir Aus Tabelle/Bereich, da sich unsere Daten innerhalb unseres offenen Arbeitsblatts befinden. Wählen Sie eine Zelle in Ihren Daten aus und Excel versucht daraufhin, die vorhandenen Daten zu erkennen und sie in eine Tabelle zu konvertieren. Achten Sie darauf, dass sich alle Ihre Daten in dieser Tabelle befinden, und passen Sie die Größe im Popup-Fenster Tabelle erstellen entsprechend an.
   Wenn Sie eine .txt- oder .csv-Datei importieren möchten, wählen Sie einfach Aus Text/CSV und dann Daten transformieren aus dem sich öffnenden Vorschaufenster.
   
   
   
4. Nachdem die Daten nun in Power Query geladen wurden, können wir mit dem Transformationsprozess beginnen. Entlang der oberen Menübänder gibt es zahlreiche Möglichkeiten, die Daten zu manipulieren. Im rechten Bereich mit dem Titel Abfrageeinstellungen sehen Sie die Eigenschaften und Angewendete Schritte. Wenn Sie in Power Query eine Handlung zur Manipulation der Daten vornehmen, wird automatisch ein Schritt erstellt, der in diesem Bereich eingesehen werden kann. Diese aufgezeichneten Schritte sind das Prinzip hinter der Automatisierung.
5. Wir können unsere Abfrage im Kästchen Name benennen. Sie werden sehen, dass einige Schritte automatisch angewendet wurden. Der Schritt Typ ändern wurde auf alle Spalten angewendet; Excel versucht im Wesentlichen zu erraten, welche Daten sich in jeder Spalte befinden, und hat ihnen einen Typ zugewiesen.
   Wir müssen diesen Schritt entfernen, da wir die Möglichkeit haben möchten, Datentypen manuell auszuwählen, anstatt sie automatisch zuweisen zu lassen. Wählen Sie dazu das Kreuzsymbol neben dem Schritt aus.
   
   
   Wir sind nun bereit, mit der Manipulation der Daten zu beginnen!
   
   

Füllen Sie die Daten unter Verwendung von „Abwärts füllen“.

Wir können mit der Transformation der Daten durch Ausfüllen der leeren Zeilen in den folgenden Spalten beginnen:

• Trans#
• Art
• Eingegeben / Zuletzt geändert
• Zuletzt geändert von
• Datum
• Anzahl

In diesem Beispiel wissen wir, dass das Datum immer in der ersten Zeile einer Transaktionsgruppe steht, während Transaktionsnummer manchmal leer sein kann.

1. Beginnen Sie mit der Erstellung einer benutzerdefinierten Spalte für die Transaktionsnummer, wobei berücksichtigt werden muss, dass die Zeilen, die keine Transaktionsnummer enthalten, erhalten bleiben müssen. Navigieren Sie zum Menüband Spalte hinzufügen und wählen Sie Benutzerdefinierte Spalte aus. Wenn sich das Fenster für die benutzerdefinierte Spalte öffnet, benennen wir sie Transaktions-ID.
   
   
   

2. if [#"Trans #"] = null and [Date] <> null then "@" else
   if [#"Trans #"] <> null and [Date] <> null then [#"Trans #"] else null

   Dies ist die Formel, mit der wir unsere benutzerdefinierte Spalte erstellen werden.
   Die Logik hinter dieser Formel besteht darin, eine Zeile auf Daten zu prüfen, die sowohl in der Spalte Transaktionsnummer als auch in der Spalte Datum vorhanden sind. Es ist erwähnenswert, dass der Wert „null“ in Power Query eine leere Zelle darstellt. Ist die Zelle mit der Transaktionsnummer leer und das Datum nicht leer, möchten wir ein Sonderzeichen ausgeben. In diesem Fall ein „@“-Symbol. Enthalten die Zellen Transaktionsnummer und Datum beide Werte, geben wir den Wert in der Zelle Transaktionsnummer aus. Wenn sowohl die Transaktionsnummer- als auch das Datum-Zellen leer sind, geben wir null aus.

   
   
   
3. Im Bereich Verfügbare Spalten auf der rechten Seite können Sie Spaltennamen in das Feld Benutzerdefinierte Spaltenformel einfügen, um auf einfache Weise eine benutzerdefinierte Formel zu erstellen, die sich auf Ihre eigenen Daten bezieht. Der Benutzerdefinierte Spaltenersteller erkennt automatisch alle Syntaxfehler und hilft Ihnen, die richtige Lösung zu finden. Sobald Sie die Formel eingegeben haben, wählen Sie OK, um Ihre neue benutzerdefinierte Spalte zu erstellen.
4. Die benutzerdefinierte Spalte Transaktions-ID wurde jetzt erstellt, die entweder die Transaktionsnummer oder ein Sonderzeichen in die erste Zeile jeder Transaktion einfügt. Führen Sie einen Rechtsklick auf die Spaltenüberschrift Transaktions-ID aus und wählen Sie Ausfüllen > Nach unten, um die Nullwerte mit den obigen Werten zu füllen.
5. Wir können jetzt Werte ersetzen verwenden, um diese Sonderzeichen zu finden, in diesem Fall „@“, und sie durch null ersetzen. Auf diese Weise bleiben die Zeilen ohne Transaktions-IDs erhalten, während die erforderlichen Daten ausgefüllt werden.
   
   
   
6. Mit dieser Logik können wir jetzt benutzerdefinierte Spalten erstellen für:
   • Art
   • Eingegeben / Zuletzt geändert
   • Zuletzt geändert von
7. Wir können Benutzerdefinierte Spalte erstellen erneut öffnen und dieselbe Formel kopieren und einfügen, um die entsprechenden Spalten zu ersetzen.
   
   

Spalten teilen nach Trennzeichen

Als Nächstes können wir das Problem lösen, dass die Kontonummer und der Kontoname die gleiche Spalte belegen. In diesem Beispiel werden die Nummer und der Name durch ein Trennzeichen getrennt. Mit der Funktion Spalten teilen nach Trennzeichen in Power Query können wir diese Werte einfach in zwei separate Spalten aufteilen.

1. Führen Sie einen Rechtsklick auf die Spaltenüberschrift aus und wählen Sie Spalte teilen > nach Trennzeichen.
   Daraufhin wird ein Fenster mit einigen Optionen angezeigt. Power Query versucht, das Trennzeichen automatisch zu erkennen und gibt ein vorgeschlagenes Trennzeichen an. Sollte es nicht korrekt sein, können Sie ein anderes Trennzeichen aus dem Menü auswählen oder ein benutzerdefiniertes Trennzeichen eingeben. Sie können zudem die Position und das Auftreten der Aufteilung unter dem Abschnitt Aufteilen auswählen.
   
   
   
2. Wählen Sie OK, wenn Sie mit der Konfiguration zufrieden sind.
   Power Query teilt die Spalte in separate Spalten für Kontonummer und Kontoname auf.
3. Sie werden feststellen, dass Power Query versucht, einen Schritt Typ ändern hinzuzufügen, der erkennt, welcher Datentyp in den neuen Spalten enthalten ist, und versucht, diesen automatisch einen Typ zuzuweisen.
   Auch diesen Schritt möchten wir entfernen, da er zu Problemen mit Kontonummern führen kann, die führende Nullen enthalten.
4. Wir können diese neu erstellten Spalten durch einen Doppelklick auf den zugewiesenen Namen in der Spaltenüberschrift umbenennen. In diesem Beispiel wurden sie als Kontonummer und Kontobeschreibung bezeichnet, damit sie mit den MindBridge-Spalten übereinstimmen.
   
   

Bereinigen der Daten

Da die wichtigsten Änderungen nun abgeschlossen sind, können wir uns auf kleinere Aufgaben konzentrieren, wie zum Beispiel die Neuanordnung der Spalten, das Löschen aller Fremddaten, die Änderung der Datentypen und die Sicherstellung, dass die Daten den Standards entsprechen, die den Import in MindBridge ermöglichen.

1. Einige Spalten wurden durch die zuvor erstellten benutzerdefinierten Spalten ersetzt. Sie können jede dieser Spalten durch Rechtsklicken auf die Spaltenüberschrift und Auswahl von Entfernen löschen:
   • Trans#
   • Art
   • Eingegeben / Zuletzt geändert
   • Zuletzt geändert von
   • Datum
   • Anzahl
2. Sie können die Spalten nach Bedarf neu anordnen, indem Sie die Spaltenüberschrift auswählen und nach links oder rechts an die gewünschte Position ziehen.
3. Sie können den Datentyp in jeder Spalte ändern, indem Sie das Symbol Typ in jeder Spaltenüberschrift auswählen. Es ist wichtig, sicherzustellen, dass ...
   • die Datumsspalten das Format Datum verwenden,
   • die Spalte „Erfassungsdatum“ ein Format Datums-/Uhrzeit verwendet,
   • die Haben-/Soll-/Betragsspalten ein Format Dezimalzahl verwenden.
   • Wir empfehlen, die Spalte Transaktions-ID als Text beizubehalten, da bei der Umwandlung in ein Zahlenformat alle vorangestellten Nullen in der ID entfernt werden.
4. Filtern Sie die Leerstellen in der Spalte Kontonummer heraus, um sicherzustellen, dass die Zeilen mit Fremddaten, wie etwa Summen, entfernt werden.
5. Verwenden Sie das Werkzeug Werte ersetzen erneut, um die „null“-Werte in Soll/Haben durch „0“ zu ersetzen.
   
   

Laden von Daten und zukünftige Verwendung

Nachdem die Daten im richtigen Format vorliegen und die Schritte aufgezeichnet wurden, können wir schließen und die Daten in ein Excel-Arbeitsblatt laden.

Wählen Sie oben links Schließen & Laden. Es wird ein neues Arbeitsblatt mit einer Tabelle erstellt, die mit den formatierten Daten gefüllt ist. Sie können die Daten nun kopieren und in eine neue Arbeitsmappe einfügen und diese in MindBridge importieren.

Mit der nun erstellten Abfrage können Sie die erzeugten Schritte für jeden Datensatz verwenden, der das gleiche Format hat, d. h. die gleichen Spaltennamen, die gleiche Spaltenposition und die gleiche Anzahl von Spalten. Es gibt zwei Möglichkeiten, einen neuen Datensatz in die Abfrage zu laden:

1. Wenn Sie Daten abrufen: Aus Tabelle/Bereich verwendet haben, löschen Sie einfach die alten Daten aus der Tabelle und fügen neue Daten ein. Sobald Ihre neuen Daten vorhanden sind, navigieren Sie zum Menüband „Daten“ und wählen Sie Alle aktualisieren.
   
   
   
2. Wenn Sie Daten abrufen: Aus Datei verwendet haben, bearbeiten Sie die Quelle der Abfrage in dem Bereich Angewendete Schritte:
   
   
   

Möchten Sie sonst noch etwas wissen? Sie können auch mit uns chatten oder eine Anfrage für weitere Unterstützung übermitteln.