Zusammenfassung

Erfahren Sie mehr über einige häufige Formatierungsprobleme im Zusammenhang mit Hauptbuchdateien und wie Sie den Formatierungsprozess mit Excel Power Query automatisieren können.

In diesem Artikel verwenden wir den Journal-Export von QuickBooks Desktop, jedoch werden viele der durchgeführten Schritte auch für andere Exporte von Buchhaltungssoftware anwendbar sein.

Anwendungsfälle

Wenn Sie viele Hauptbuchdateien von derselben Buchhaltungssoftware haben, die alle im gleichen Format vorliegen und vor der Eingabe in MindBridge transformiert werden müssen, könnte Excel Power Query das perfekte Werkzeug für Sie sein.

Sobald eine Datei in ein verarbeitbares Format transformiert wurde und die durchgeführten Schritte aufgezeichnet wurden, können Sie jede Datei im gleichen Format sofort transformieren!

Das Problem

Nachfolgend ein Beispiel für die Formatierung eines QuickBooks Desktop Journal-Berichts, entnommen aus unserem Extraktionsleitfaden.

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

1. Die Daten sind nach Transaktion gruppiert; die Spalten Trans#, Typ, Erfasst/Zuletzt geändert, Zuletzt geändert von, Datum und Num müssen ihre Daten auf jede Zeile der Transaktion herunterkopieren.
2. Trans# ist kein Pflichtfeld in QuickBooks, daher haben nicht alle Transaktionen eine Trans#.
3. Die Kontonummer und der Name belegen dasselbe Feld.
4. Es gibt überflüssige Zeilen, wie z.B. Gesamtsummen.

Die Lösung

1. Füllen Sie die leeren Zeilen mit den relevanten Daten aus jeder Spalte mit der Funktion Nach unten füllen.
2. Stellen Sie beim Ausfüllen der Daten sicher, dass die leeren Trans#-Felder (z.B. Zellen B5 und B27) beibehalten und nicht mit falschen Daten gefüllt werden, indem Sie eine Benutzerdefinierte Spalte verwenden.
3. Teilen Sie die Kontonummer und den Namen in separate Spalten auf, indem Sie die Funktion Spalte nach Trennzeichen aufteilen verwenden.
4. Filtern Sie die leeren Felder in einer Spalte heraus, die Daten in jeder Eintragszeile enthält.
   
   

Daten abrufen und transformieren

Um mit der Transformation der Daten zu beginnen, müssen wir die Daten zuerst in Power Query importieren. Es gibt verschiedene Möglichkeiten, dies je nach Dateityp zu tun.

1. Öffnen Sie entweder ein neues Arbeitsblatt oder Ihre Hauptbuchdatei in Excel.
2. Gehen Sie zum Daten-Ribbon oben. Im Abschnitt Daten abrufen und transformieren finden Sie verschiedene Optionen, um die Daten abzurufen, je nachdem, ob Ihre Daten in Ihrem geöffneten Arbeitsblatt oder in einer separaten Datei vorliegen.
3. Wählen Sie die geeignete Methode für die Daten, die Sie haben. In diesem Beispiel wählen wir Aus Tabelle/Bereich, da sich unsere Daten in unserem geöffneten Arbeitsblatt befinden. Wählen Sie eine Zelle innerhalb Ihrer Daten aus, und Excel wird dann versuchen, die vorhandenen Daten zu erkennen und in eine Tabelle zu konvertieren. Achten Sie darauf, dass alle Ihre Daten in dieser Tabelle enthalten sind und passen Sie die Größe im Tabelle erstellen-Pop-up entsprechend an.
   Wenn Sie eine .txt- oder .csv-Datei importieren möchten, wählen Sie einfach Aus Text/CSV und wählen Sie Daten transformieren aus dem Vorschaufenster, das angezeigt wird.
   
   
   
4. Jetzt, da die Daten in Power Query geladen wurden, können wir mit dem Transformationsprozess beginnen. Oben in den Ribbons gibt es zahlreiche Möglichkeiten, die Daten zu manipulieren. Im rechten Bereich mit dem Titel Abfrageeinstellungen können wir die Eigenschaften und Angewendete Schritte sehen. In Power Query wird bei jeder Aktion zur Manipulation der Daten automatisch ein Schritt erstellt, der in diesem Bereich angezeigt wird. Diese aufgezeichneten Schritte sind das Prinzip hinter der Automatisierung.
5. Wir können unsere Abfrage im Name-Feld 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 Option haben möchten, Datentypen manuell auszuwählen, anstatt sie automatisch zuzuweisen. Dazu wählen Sie das Kreuzsymbol neben dem Schritt.
   
   
   Wir sind jetzt bereit, die Daten zu manipulieren!
   
   

Daten mit Nach unten füllen ausfüllen

Wir können beginnen, die Daten zu transformieren, indem wir die leeren Zeilen in den folgenden Spalten ausfüllen:

• Trans#
• Typ
• Erfasst/Zuletzt geändert
• Zuletzt geändert von
• Datum
• Num

In diesem Beispiel wissen wir, dass das Datum immer in der ersten Zeile einer Transaktionsgruppe vorhanden ist, jedoch kann Trans# manchmal leer sein.

1. Beginnen Sie mit der Erstellung einer benutzerdefinierten Spalte für die Transaktionsnummer, wobei Sie berücksichtigen müssen, dass wir die Zeilen beibehalten müssen, in denen die Transaktionsnummer leer ist. Gehen Sie zum Spalte hinzufügen-Ribbon und wählen Sie Benutzerdefinierte Spalte erstellen. Wenn das Fenster für die benutzerdefinierte Spalte erscheint, benennen wir es 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, die wir verwenden werden, um unsere benutzerdefinierte Spalte zu erstellen.
   Die Logik hinter dieser Formel besteht darin, eine Zeile auf Daten in den Spalten Trans# und Datum zu überprüfen. Es ist erwähnenswert, dass der Wert "null" in Power Query eine leere Zelle darstellt. Wenn die Transaktionsnummernzelle leer ist und das Datum nicht leer ist, möchten wir ein Sonderzeichen ausgeben, in diesem Fall ein "@"-Symbol. Wenn die Trans#- und die Datum-Zellen beide Werte enthalten, geben wir den Wert in der Trans#-Zelle aus. Wenn sowohl die Trans#- als auch die Datum-Zellen leer sind, geben wir null aus.

   
   
   
3. Der Abschnitt Verfügbare Spalten auf der rechten Seite ermöglicht es Ihnen, Spaltennamen in das Feld Benutzerdefinierte Spaltenformel einzufügen, um die benutzerdefinierte Formel, die Ihre eigenen Daten referenziert, einfach zu erstellen. Der benutzerdefinierte Spaltenersteller erkennt automatisch alle Syntaxfehler, um Ihnen zu helfen, die richtige Lösung zu finden. Sobald die Formel ausgefüllt ist, wählen Sie Ok, um Ihre neue benutzerdefinierte Spalte zu erstellen.
4. Die benutzerdefinierte Spalte Transaktions-ID wurde nun erstellt, die entweder die Transaktionsnummer oder ein Sonderzeichen in der ersten Zeile jeder Transaktion ausfüllt. Klicken Sie mit der rechten Maustaste auf den Spaltenkopf der Transaktions-ID und wählen Sie Füllen>Nach unten, um die null-Werte mit den Werten darüber zu füllen.
5. Wir können jetzt Werte ersetzen verwenden, um dieses Sonderzeichen, in diesem Fall "@", zu finden und durch null zu ersetzen. Dies wird die Zeilen mit leeren Transaktions-IDs beibehalten, während die notwendigen Daten nach unten gefüllt werden.
   
   
   
6. Mit dieser Logik können wir nun benutzerdefinierte Spalten erstellen für:
   • Typ
   • Erfasst/Zuletzt geändert
   • Zuletzt geändert von
7. Wir können Benutzerdefinierte Spalte erstellen erneut öffnen und die gleiche Formel kopieren und einfügen, wobei die entsprechenden Spalten ersetzt werden.
   
   

Spalten nach Trennzeichen aufteilen

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

1. Klicken Sie mit der rechten Maustaste auf den Spaltenkopf und wählen Sie Spalte aufteilen > Nach Trennzeichen.
   Dies öffnet ein Fenster mit einigen Optionen. Power Query versucht, das Trennzeichen automatisch zu erkennen und gibt ein vorgeschlagenes Trennzeichen an. Wenn es falsch ist, können Sie ein anderes Trennzeichen aus dem Menü auswählen oder ein benutzerdefiniertes Trennzeichen eingeben. Sie können auch die Position und das Vorkommen der Aufteilung im Abschnitt Aufteilen bei auswählen.
   
   
   
2. Wählen Sie OK, sobald Sie mit der Konfiguration zufrieden sind.
   Power Query wird die Spalte in separate Kontonummer- und Kontoname-Spalten aufteilen.
3. Sie werden feststellen, dass Power Query versucht, einen Typ ändern-Schritt hinzuzufügen, der erkennt, welche Art von Daten sich in den neuen Spalten befindet, und versucht, ihnen automatisch einen Typ zuzuweisen.
   Auch hier möchten wir diesen Schritt entfernen, da er zu Problemen mit Kontonummern führen kann, die führende Nullen haben.
4. Wir können diese neu erstellten Spalten umbenennen, indem wir auf den zugewiesenen Namen im Spaltenkopf doppelklicken. In diesem Beispiel wurden sie Kontonummer und Kontobeschreibung genannt, um mit den MindBridge-Spalten übereinzustimmen.
   
   

Daten bereinigen

Mit den nun abgeschlossenen größeren Änderungen können wir uns auf kleinere Aufgaben konzentrieren, wie das Neuanordnen der Spalten, das Löschen überflüssiger Daten, das Ändern der Datentypen und das Sicherstellen, dass die Daten den Standards entsprechen, die es ermöglichen, sie in MindBridge zu importieren.

1. Einige Spalten wurden durch die benutzerdefinierten Spalten ersetzt, die früher im Prozess erstellt wurden. Sie können jede dieser Spalten löschen, indem Sie mit der rechten Maustaste auf den Spaltenkopf klicken und Entfernen auswählen:
   • Trans#
   • Typ
   • Erfasst/Zuletzt geändert
   • Zuletzt geändert von
   • Datum
   • Num
2. Sie können die Spalten nach Bedarf neu anordnen, indem Sie den Spaltenkopf nach links oder rechts an die gewünschte Position ziehen.
3. Sie können den Datentyp in jeder Spalte ändern, indem Sie das Typ-Symbol im Spaltenkopf auswählen. Es ist wichtig sicherzustellen, dass...
   • Die Datumsspalten das Datum-Format verwenden
   • Die Erfassungsdatumsspalte das Datum/Uhrzeit-Format verwendet
   • Die Kredit/Soll/Betrag-Spalten das Dezimalzahl-Format verwenden
   • Wir empfehlen, die Transaktions-ID-Spalte als Text beizubehalten, da die Umwandlung in ein Zahlenformat alle führenden Nullen in der ID entfernen würde.
4. Filtern Sie die leeren Felder in der Kontonummer-Spalte heraus, um sicherzustellen, dass die Zeilen mit überflüssigen Daten, wie z.B. Gesamtsummen, entfernt werden.
5. Verwenden Sie das Werkzeug Werte ersetzen erneut, um die "null"-Werte in Soll/Kred. durch "0" zu ersetzen.
   
   

Daten laden und zukünftige Nutzung

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

Wählen Sie Schließen & Laden oben links. Ein neues Blatt wird erstellt, das mit den formatierten Daten gefüllt ist. Sie können die Daten jetzt in eine neue Arbeitsmappe kopieren und einfügen und diese in MindBridge importieren.

Mit der nun erstellten Abfrage können Sie die generierten Schritte für jeden Datensatz nutzen, der im gleichen Format vorliegt, d.h. er hat die gleichen Spaltennamen, Spaltenpositionen und Spaltenanzahl. Es gibt 2 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 Sie neue Daten ein. Sobald Ihre neuen Daten vorhanden sind, navigieren Sie zum Daten-Ribbon und wählen Alle aktualisieren.
   
   
   
2. Wenn Sie Daten abrufen: Aus Datei verwendet haben, bearbeiten Sie die Quelle der Abfrage im Angewendete Schritte-Bereich:
   
   
   

Haben Sie noch Fragen? Chatten Sie mit uns oder senden Sie eine Anfrage für weitere Unterstützung.