Power Query Masterclass: Wie Sie den Text-Inhalt von Zellen erkennen, trennen und automatisch als Datum formatieren

In der Welt der Datenanalyse und -verarbeitung gibt es kaum etwas Frustrierenderes als unformatierte oder inkonsistent formatierte Datumsangaben. Oft finden wir uns mit Tabellen konfrontiert, in denen Datumsangaben als reiner Text vorliegen, mal als „YYYYMMDD”, mal als „DD.MM.YYYY”, oder sogar eingebettet in längere Textketten. Der Versuch, diese manuell zu bereinigen, ist nicht nur zeitaufwendig, sondern auch extrem fehleranfällig. Genau hier kommt Power Query ins Spiel – ein leistungsstarkes Tool, das in Excel und Power BI integriert ist und uns hilft, solche Herausforderungen mit Bravour zu meistern. Diese Masterclass führt Sie Schritt für Schritt durch den Prozess, wie Sie Textinhalte, die Datumsangaben enthalten, erkennen, trennen und zuverlässig in ein korrektes Datumsformat umwandeln können.

Das Problem: Unruly Date Formats

Bevor wir uns der Lösung widmen, lassen Sie uns das Problem genauer beleuchten. Stellen Sie sich vor, Sie importieren Daten aus verschiedenen Quellen: ein CSV-Export aus einem alten System, eine Tabelle aus einer Website und ein manuell gepflegtes Excel-Blatt. In jedem dieser Datensätze könnten Datumsangaben in unterschiedlicher Form vorliegen:

• „20230115” (JahrMonatTag, ohne Trennzeichen)
• „15. Januar 2023” (Deutsch, ausgeschriebener Monat)
• „Jan-15-23” (Englisch, abgekürzter Monat, zweistelliges Jahr)
• „15/01/2023 14:30” (Datum mit Uhrzeit)
• „Auftrag vom 2023-01-15” (Datum eingebettet in Text)
• „Q1 2023” (Quartalsangabe, kein spezifisches Datum)

Versucht man nun, diese Spalten direkt in ein Datumsformat umzuwandeln, scheitert Excel oder Power BI oft. Die Zellen bleiben Text, werden zu Fehlern (#VALUE!) oder, noch schlimmer, falsch interpretiert (z.B. „01/05/2023” wird als 5. Januar interpretiert, obwohl es der 1. Mai sein sollte, je nach Ländereinstellung). Dies hat weitreichende Folgen für die Datenanalyse: Datumsberechnungen sind unmöglich, Filter funktionieren nicht richtig, und die Datenqualität leidet erheblich. Die manuelle Korrektur ist schlichtweg keine Option für größere Datensätze oder wiederkehrende Importe. Hier brilliert Power Query als Automatisierungswerkzeug.

Ihr Daten-Transformations-Held: Power Query

Power Query, auch bekannt als Get & Transform, ist ein ETL-Tool (Extrahieren, Transformieren, Laden), das Ihnen erlaubt, Daten aus einer Vielzahl von Quellen zu importieren, zu bereinigen, zu transformieren und in das gewünschte Format zu bringen. Das Beste daran: Alle Schritte, die Sie im Power Query Editor durchführen, werden aufgezeichnet und können bei jedem Datenrefresh automatisch wiederholt werden. Dies macht es zum idealen Werkzeug für die Automatisierung von Datenbereinigungsprozessen.

Sie finden Power Query in Excel unter dem Reiter „Daten” in der Gruppe „Daten abrufen & transformieren” (oder „Abrufen und Transformieren”). In Power BI Desktop ist es der Standardeditor für die Datenvorbereitung. Für diesen Artikel gehen wir von der Excel-Integration aus, die Schritte sind jedoch weitgehend identisch.

Schritt-für-Schritt-Anleitung: Text-Datumsangaben erkennen und bereinigen

Lassen Sie uns nun in die Praxis eintauchen und verschiedene Szenarien durchspielen.

1. Daten in den Power Query Editor laden

Um zu beginnen, laden Sie Ihre Daten in den Power Query Editor. Angenommen, Ihre Daten befinden sich in einer Excel-Tabelle:

1. Markieren Sie Ihre Daten.
2. Gehen Sie zu „Daten” > „Aus Tabelle/Bereich”.
3. Bestätigen Sie den Bereich und klicken Sie auf „OK”.

Der Power Query Editor öffnet sich und zeigt eine Vorschau Ihrer Daten. Identifizieren Sie die Spalte, die die problematischen Datumsangaben enthält. Nehmen wir an, diese Spalte heißt „TextDatum”.

2. Szenario 1: Einfache, aber inkonsistente Formate (z.B. „DD.MM.YYYY” und „MM/DD/YYYY” gemischt)

Manchmal sind die Text-Datumsangaben zwar in einem gängigen Format, aber die Ländereinstellung ist das Problem. Zum Beispiel könnte „01.05.2023” korrekt als 1. Mai interpretiert werden, während „01/05/2023” in US-Englisch als 5. Januar gelesen wird.

1. Wählen Sie die Spalte „TextDatum” aus.
2. Klicken Sie im Reiter „Transformieren” auf „Datentyp” und wählen Sie „Datum” (oder „Datum/Uhrzeit”, falls Zeiten enthalten sind).
3. Wenn Sie eine Fehlermeldung erhalten oder falsche Ergebnisse sehen, machen Sie den Schritt rückgängig (im Bereich „Angewendete Schritte” rechts).
4. Klicken Sie stattdessen erneut auf „Datentyp” > „Datum”, aber diesmal klicken Sie auf den kleinen Pfeil neben „Datentyp” und wählen „Gebietsschema verwenden…”.
5. Im Dialogfeld wählen Sie „Datum” als Datentyp und suchen unter „Gebietsschema” das passende Format, das den Großteil Ihrer Daten korrekt interpretiert (z.B. „Deutsch (Deutschland)” für „DD.MM.YYYY” oder „Englisch (Vereinigte Staaten)” für „MM/DD/YYYY”).

Dies ist oft der erste und einfachste Schritt. Wenn alle Datumsangaben einem spezifischen Gebietsschema entsprechen, löst dies das Problem sofort.

3. Szenario 2: Datumsangaben eingebettet in längere Textketten

Was aber, wenn das Datum nicht die gesamte Zelle ausfüllt, sondern in einem Satz wie „Die Bestellung wurde am 2023-01-15 aufgegeben.” steht?

A. Trennen nach Trennzeichen

Wenn das Datum durch ein klares Trennzeichen vom Rest des Textes abgetrennt ist, können Sie die Spalte teilen:

1. Wählen Sie die Spalte „TextDatum” aus.
2. Gehen Sie zu „Startseite” oder „Transformieren” > „Spalte teilen” > „Nach Trennzeichen”.
3. Im Dialogfeld geben Sie das Trennzeichen ein (z.B. „am „, wenn Sie „Die Bestellung wurde am 2023-01-15 aufgegeben” haben).
4. Wählen Sie aus, wo das Trennzeichen angewendet werden soll (z.B. „Beim äußersten linken Trennzeichen” oder „Jedes Vorkommen des Trennzeichens”).
5. Klicken Sie auf „OK”.

Dies erstellt neue Spalten. Die Spalte, die das Datum enthält, kann dann wie in Szenario 1 in ein Datumsformat umgewandelt werden. Möglicherweise müssen Sie noch unerwünschte Zeichen entfernen (z.B. ein abschließendes „.”).

B. Extrahieren nach Position oder Text zwischen Trennzeichen

Manchmal sind Datumsangaben an einer festen Position (z.B. immer die letzten 8 Zeichen einer Zeichenkette) oder zwischen zwei bekannten Textteilen eingebettet. Hierfür bietet Power Query leistungsstarke „Extrahieren”-Funktionen:

1. Wählen Sie die Spalte „TextDatum” aus.
2. Gehen Sie zu „Transformieren” > „Extrahieren”.
3. Wählen Sie die passende Option:
   • „Erste Zeichen”: Wenn das Datum immer am Anfang steht und eine feste Länge hat (z.B. „20230115_Order” -> 8 Zeichen).
   • „Letzte Zeichen”: Wenn das Datum immer am Ende steht und eine feste Länge hat (z.B. „Order_20230115” -> 8 Zeichen).
   • „Text zwischen Trennzeichen”: Wenn das Datum immer zwischen zwei bestimmten Zeichen oder Texten steht (z.B. „von [” und „]” in „Bericht von [2023-01-15]”).
   • „Text vor Trennzeichen” oder „Text nach Trennzeichen”: Für spezifischere Fälle.
   • „Länge”: Um Zeichen ab einem bestimmten Index zu extrahieren.
4. Folgen Sie den Anweisungen im Dialogfeld und geben Sie die notwendigen Parameter ein (Anzahl der Zeichen, Trennzeichen, etc.).

Diese Funktionen erstellen eine neue Spalte mit dem extrahierten Text, der dann zum Datum konvertiert werden kann. Denken Sie daran, die ursprüngliche Spalte ggf. zu duplizieren, bevor Sie Extraktionsoperationen durchführen, falls Sie den Originaltext noch benötigen.

4. Szenario 3: Komplexe und irreguläre Datumsangaben (z.B. „15. Januar 2023”, „January 15, 2023”, „15th Jan 23”)

Dies ist oft der kniffligste Fall, wenn Datumsangaben in unterschiedlichen, nicht-standardisierten oder sprachspezifischen Textformaten vorliegen. Hier greifen wir zu einer Kombination aus Textmanipulation und der leistungsstarken M-Sprache von Power Query.

A. Benutzerdefinierte Spalte mit M-Sprache

1. Wählen Sie die Spalte „TextDatum” aus (oder eine bereits extrahierte Datums-Spalte).
2. Gehen Sie zu „Spalte hinzufügen” > „Benutzerdefinierte Spalte”.
3. Geben Sie einen Namen für die neue Spalte ein, z.B. „Datum_Final”.
4. Im Feld „Benutzerdefinierte Spaltenformel” geben Sie M-Sprach-Funktionen ein. Die wichtigste Funktion hier ist Date.FromText(). Diese Funktion versucht, Text in ein Datum umzuwandeln und kann ein optionales „Gebietsschema” oder „Format” als zweiten Parameter annehmen.

Beispiel: Mehrere Formate in einer Spalte handhaben

Angenommen, Sie haben eine Spalte mit Datumsangaben, die entweder als „DD.MM.YYYY” oder als „YYYYMMDD” vorliegen.

Formel in der benutzerdefinierten Spalte:

try Date.FromText([TextDatum], [Format="dd.MM.yyyy", Culture="de-DE"])
otherwise try Date.FromText([TextDatum], [Format="yyyyMMdd"])
otherwise null

Erklärung:

• try ... otherwise ...: Dies ist ein entscheidender Mechanismus für die Fehlerbehandlung in M. Es versucht den ersten Ausdruck; wenn dieser einen Fehler verursacht (z.B. weil das Format nicht passt), wird der zweite Ausdruck ausgeführt.
• Date.FromText([TextDatum], [Format="dd.MM.yyyy", Culture="de-DE"]): Versucht, den Text als deutsches Datum im Format Tag.Monat.Jahr zu interpretieren.
• Date.FromText([TextDatum], [Format="yyyyMMdd"]): Versucht, den Text als JahrMonatTag zu interpretieren.
• null: Wenn keines der Formate passt, wird in dieser Zelle null eingefügt. So können Sie später Fehler identifizieren.

B. Textbereinigung vor der Datumskonvertierung

Oft müssen Sie den Text noch vor der Konvertierung bereinigen:

• Groß-/Kleinschreibung anpassen: Text.Proper([Spalte]) (Jedes Wort groß) oder Text.Lower() (Alles klein). Wichtig, wenn Monatsnamen mal groß, mal klein geschrieben sind.
• Unerwünschte Zeichen entfernen: Text.Replace([Spalte], "th", "") oder Text.Remove([Spalte], {"st", "nd", "rd"}). Nützlich für englische Datumsangaben wie „1st January”.
• Monatsnamen anpassen: Wenn Sie z.B. „Januar” zu „Jan” oder „Jan.” ändern müssen, um eine Konsistenz herzustellen, die Date.FromText besser verarbeiten kann: Text.Replace([Spalte], "Januar", "Jan"). Sie können mehrere Text.Replace-Aufrufe verketten.
• Leerräume entfernen: Text.Trim([Spalte]) entfernt führende und abschließende Leerzeichen, Text.Clean([Spalte]) entfernt nicht druckbare Zeichen.

Diese Bereinigungsschritte können entweder in einer Kette von „Hinzugefügte Spalten” oder innerhalb einer einzigen komplexeren „Benutzerdefinierten Spalte” erfolgen. Es empfiehlt sich, schrittweise vorzugehen und die Ergebnisse jeder Transformation zu überprüfen.

Beispiel für komplexere Bereinigung und Konvertierung:

Angenommen, Sie haben Daten wie „15th Jan 2023” oder „20.Feb.2023”.

let
    SourceText = [TextDatum],
    CleanedText = Text.Replace(Text.Replace(Text.Replace(Text.Replace(SourceText, "th", ""), "st", ""), "nd", ""), "rd", ""),
    ReplacedMonthText = Text.Replace(Text.Replace(CleanedText, "Jan", "01"), "Feb", "02"), // weitere Monate hinzufügen
    Attempt1 = try Date.FromText(ReplacedMonthText, [Format="dd MMM yyyy", Culture="en-US"]) otherwise null,
    Attempt2 = try Date.FromText(ReplacedMonthText, [Format="dd.MMM.yyyy", Culture="de-DE"]) otherwise null
in
    if Attempt1 <> null then Attempt1
    else if Attempt2 <> null then Attempt2
    else null

Diese Formel ist ein Beispiel, wie Sie mit der M-Sprache eine robuste Lösung für sehr unterschiedliche Formate erstellen können. Sie können die Text.Replace-Logik erweitern, um alle Monatsnamen zu standardisieren, bevor Sie die Konvertierung versuchen.

5. Automatisches Formatieren als Datum

Nachdem Sie den Textinhalt so weit transformiert haben, dass er als Datum erkennbar ist (z.B. „15.01.2023” oder „2023-01-15”), ist der letzte Schritt die eigentliche Typkonvertierung. Auch hier ist die Auswahl des richtigen Gebietsschemas entscheidend, um sicherzustellen, dass Tag und Monat korrekt interpretiert werden.

1. Wählen Sie die Spalte mit den nun bereinigten Datumsangaben aus.
2. Klicken Sie auf das Symbol für den Datentyp (oft ABC|123) oben links im Spaltenkopf.
3. Wählen Sie „Datum” oder „Datum/Uhrzeit”.
4. Alternativ, und dies ist oft die sicherere Methode, wählen Sie unter „Transformieren” > „Datentyp” > „Gebietsschema verwenden…” und wählen Sie „Datum” und das passende Gebietsschema (z.B. „Deutsch (Deutschland)”).

Nun sollte Ihre Spalte korrekt als Datum formatiert sein. Zellen, die nicht konvertiert werden konnten, werden je nach vorheriger `try…otherwise`-Logik als `null` oder als Fehler angezeigt. Fehler können Sie dann filtern und die Ursachen im Originaltext untersuchen.

Best Practices & Wichtige Tipps

• Schrittweise arbeiten: Zerlegen Sie komplexe Transformationen in kleinere, überschaubare Schritte. Das macht die Fehlerbehebung einfacher.
• Spalten duplizieren: Bevor Sie eine Spalte manipulieren, die Sie möglicherweise später noch im Originalzustand benötigen, duplizieren Sie diese (Rechtsklick auf Spaltenkopf > „Spalte duplizieren”).
• Angewendete Schritte überprüfen: Achten Sie auf den Bereich „Angewendete Schritte” auf der rechten Seite des Power Query Editors. Hier können Sie Schritte rückgängig machen, bearbeiten oder neu anordnen.
• M-Sprache lernen: Auch wenn Power Query viele Aufgaben über die Benutzeroberfläche löst, ist ein grundlegendes Verständnis der M-Sprache (der Formelsprache von Power Query) von unschätzbarem Wert für komplexere Szenarien und die Fehlerbehebung.
• Fehler behandeln: Nutzen Sie try ... otherwise ..., um potenzielle Konvertierungsfehler abzufangen und durch null oder einen Standardwert zu ersetzen. Dies verhindert, dass der gesamte Abfragevorgang fehlschlägt.
• Null-Werte managen: Wenn nach der Transformation noch null-Werte in Ihrer Datumsspalte sind, müssen Sie entscheiden, ob Sie diese Zeilen filtern, durch einen Standardwert ersetzen oder die Originaldaten manuell korrigieren.
• Performance beachten: Bei sehr großen Datensätzen können viele Textoperationen die Performance beeinträchtigen. Versuchen Sie, unnötige Spalten frühzeitig zu entfernen.

Zusammenfassung & Ausblick

Die Fähigkeit, Datumsangaben aus unstrukturiertem oder inkonsistentem Text zu extrahieren und korrekt zu formatieren, ist eine Kernkompetenz im Bereich der Datenbereinigung. Mit Power Query haben Sie ein mächtiges Werkzeug an der Hand, das diese oft mühsame Aufgabe automatisiert und so Ihre Datenanalyse erheblich beschleunigt und zuverlässiger macht. Von einfachen Typumwandlungen mit Gebietsschema-Optionen über das Extrahieren von Textteilen bis hin zur Verwendung der fortgeschrittenen M-Sprache für komplexe Szenarien – Power Query bietet eine Lösung für fast jede Herausforderung.

Übung macht den Meister! Experimentieren Sie mit Ihren eigenen Datensätzen und entdecken Sie die vielfältigen Möglichkeiten, die Power Query Ihnen bietet. Sie werden schnell feststellen, dass dieses Tool unverzichtbar wird, wenn es darum geht, Ihre Daten in eine analysierbare und konsistente Form zu bringen. Die in dieser Masterclass gelernten Techniken bilden die Grundlage für viele weitere fortgeschrittene Datenoperationen, sei es in Excel oder in professionellen BI-Tools wie Power BI.