Kalendarische Daten mit Uhrzeit in einer Spalte in gemischtem Landesformat (DE und US) zu nur Datum (DE)
Wissensstand: Level 1 ⇒ Einsteiger in PQ, keine/kaum Erfahrung
In einem Forum wurde von einem Fragesteller die Aufgabe zur Diskussion gestellt, wie Datum- und Zeit-Einträge unterschiedlicher Gebietsformatierungen (hier: DE und US) in ein einheitliches Format als nur Datum im heimischen Format dargestellt werden können. Zum Zeitpunkt der Erstellung dieses Beitrages lag eine Lösung in VBA vor, die durchaus praktikabel ist und in vielen Fällen auch die erste Wahl sein kann. In manchen Unternehmen ist jedoch VBA/Makro aus guten Gründen untersagt bzw. deaktiviert. Da bietet es sich an, über Power Query als Lösung nachzudenken.
Ich habe die Text-basierte Aufstellung in eine Excel-Tabelle übertragen und als Basis für zwei mögliche Lösungen mit Power Query verwendet. Laden Sie dieses File hier herunter. Beide von mir vorgestellten Lösungen sind gleichwertig, weitere Ansätze sind natürlich auch denkbar.
Beginnen Sie damit, die Daten in eine „intelligente” Tabelle/Liste zu formatieren. Danach importieren Sie die Daten in den Power Query-Editor. Anschließend stellt sich das dann so dar:
Direkt nach dem Import
Der erste Arbeitsschritt wird sein, die Daten der Spalte1 als Datentyp: Text zu formatieren. Als Einsteiger werden sie gewiss im Register Home und Gruppe Transformieren den Datentyp entsprechend ändern. Wenn Sie experimentierfreudig sind, können Sie direkt nach dem Import die Bearbeitungszeile dahingehend ändern, dass sie das letzte Argument type any in type text (Kleinschreibung!) ändern. Das geht zwar nicht schneller, führt aber gewiss zum Gewinn neuer Erkenntnisse. 😉
Möglichkeit 1
Um das Datum von der Uhrzeit zu trennen, gibt es (natürlich) mehrere Möglichkeiten. Auch in älteren Versionen des Power Query funktioniert garantiert die Methode, die Spalte nach Trennzeichen zu teilen. Das funktioniert, weil die Uhrzeit in jedem Falle durch ein Leerzeichen vom Datum getrennt ist. Gehen Sie also in der Gruppe Transformieren über Spalte teilen | Nach Trennzeichen und wählen Sie statt des Vorschlages Doppelpunkt das Leerzeichen. Anschließend löschen Sie (aus Gründen der Übersichtlichkeit) die abgetrennte Spalte mit der Uhrzeit. Ich selber bin (in der Version 2019) folgenden Weg gegangen: Transformieren | Textspalte | Extrahieren | Text vor Trennzeichen und gebe im Textfeld Trennzeichen über die Tastatur ein Leerzeichen ein. Damit erreiche ich, dass keine zusätzliche Spalte mit der Uhrzeit generiert wird.
Eine direkte Konvertierung dieser kalendarischen Daten im gemischten Landes-Format (DE und US) ist nur in VBA möglich, meines Wissens nicht Power Query. Mit einer Formel in einer benutzerdefinierten Spalte ist das aber gut machbar. Aktivieren Sie das Register Spalte hinzufügen und wählen Sie das Symbol Benutzerdefinierte Spalte. Es öffnet sich ein Dialog-Fenster und dort tragen Sie die hier gezeigten Werte ein:
Diese Benutzerdefinierte Formel ist zielführend
Das ganze hier noch einmal in Textform: Bei Neuer Spaltenname geben Sie als Überschrift Datum ein. Und die Benutzerdefinierte Spaltenformel sieht so aus:
= if Text.Contains([Spalte1], ".")
then Date.From([Spalte1])
else Date.From([Spalte1], "us-US")
Übersetzt bedeutet das: Wenn der Wert der jeweiligen Zeile in Spalte1 einen Punkt enthält, dann soll der Wert aus Spalte1 direkt verwendet werden, ansonsten ebenfalls der Wert aus Spalte1, jedoch unter dem Aspekt, dass dieses Datum im US-Format vorliegt.
Prinzipiell ist damit das Ziel erreicht. Formal sollten jedoch noch zwei Schritte gegangen werden: Rechtsklick in die Spalte Datum und im Kontextmenü Typ ändern | Datum und als praktisch letzten Schritt werden Sie Spalte1 löschen; es wird ja nur das auf lokale Verhältnisse angepasst Datum gebraucht. Schließen & laden oder Schließen & laden in…, um die Abfrage in ein Excel Arbeitsblatt zu speichern.
Möglichkeit 2
Diese Möglichkeit ist vielleicht etwas „schlanker”, muss deswegen aber nicht besser sein. Sie werden die Daten natürlich vom Excel-Arbeitsblatt in den Power Query-Editor importieren. Auch die Konvertierung dieser Spalte in den Datentyp Text ist zum Vorgehen der ersten Möglichkeit identisch. Die Uhrzeit bleibt hier aber erhalten, obwohl das Ziel auch hier das reine Datum ist. Das wird in der folgenden Formel bedacht.
Nun werden sie wieder eine Benutzerdefinierte Spalte erstellen und als Neuer Spaltenname natürlich wieder Datum schreiben. Die hier verwendete Formel stellt sich so dar:
= if Text.Contains([Spalte1], ".")
then DateTime.From([Spalte1])
else DateTime.From([Spalte1], "us-US")
Der eigentliche Unterschied zur vorher aufgezeigten Formel ist, dass hier die Funktionen DateTime und nicht Date verwendet wurde. Und das Ergebnis ist naturgemäß auch vom Typ Datum und Zeit:
Die Rückgabe der Formel ist vom Typ Datum und Zeit
Wechsel zum Register Transformieren, Gruppe Aus Datum & Uhrzeit | Datum | Nur Datum oder Rechtsklick in die Überschrift und dort Transformieren | Nur Datum. In jedem Fall hat die Ergebnis-Spalte bereits den Datentyp Datum, braucht nun also nicht mehr angepasst werden. Bleibt nur noch, die erste Spalte mit den Original-Daten zu löschen und diese Abfrage an der Wunschposition zu speichern.
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 1,00€ bis 2,00€ freuen …