PQ: Ziffernfolgen (ohne Trennzeichen) zu Datum, Uhrzeit, IP, … konvertieren (2)

Xtract: Ziffernfolgen, beispielsweise Uhrzeiten, kalendarische Daten, IP-Adressen, … die ohne Trennzeichen als Text eingegeben oder importiert worden sind,  mit Power Query zu Uhrzeit, Datum, IP, … mit den Trennzeichen konvertieren.
Hier: Datums-Werte & IPs.

  Wissensstand: Level 2 ⇒ Solides Basiswissen in Excel, etwas Erfahrung in PQ   

Teil 2: Datumswerte konvertieren, IP-Adressen

Mit schöner Regelmäßigkeit werde ich gefragt, wie folgendes realisiert werden kann:

  • Eingabe von beispielsweise kalendarischen Daten, Uhrzeiten, IP-Adressen oder ähnlichen Ziffernfolgen, jedoch ohne die Trennzeichen (Punkt, Schrägstrich oder Minuszeichen bei einem Datum, Doppelpunkt bei einer Uhrzeit oder auch den Punkt bei IP-Adressen) mit eingeben zu müssen.

Die Motivation als auch die Sinnhaftigkeit will ich hier nicht diskutieren, der Wunsch ist gegeben. In erster Linie bietet sich dabei natürlich ein selbst definiertes Zahlenformat an. Das Ergebnis sieht dann zwar wie gewollt aus, aber Excel „sieht“ dennoch die ursprüngliche Eingabe und macht unter typischen Umständen keineswegs ein Datum, eine Uhrzeit oder eine auswertbare IP daraus. Für die Uhrzeit wird hier im Blog als auch beispielsweise hier eine Lösung angeboten, die Eingabe direkt zu „korrigieren“. Wenn es bei der Dateneingabe aber auf Geschwindigkeit ankommt, dann ist das gewiss nicht der beste Weg.

Mit der Nutzung des Power Query bietet sich die Möglichkeit, eine komplette Spalte rasch umzuformatieren bzw. eine neue Spalte mit den korrekten (gewünschten) Werten und Darstellungen zu generieren.

▲ nach oben …

Datum (Kalendarische Daten)

Ihr erster Gedanke könnte nun sein, dass das mit dem Datum genauso funktioniert wie mit der Zeit (siehe hier im Blog). Nun ja, in gewissen Grenzen ist das gewiss richtig. Aber eben nicht immer. Am ehesten werden sie zum gewünschten Ziel kommen, wenn die Norm der Ziffernfolge mit landesüblicher Darstellung (und der Vorgabe dieser Übung entsprechend ohne die trennenden Punkte) übereinstimmt: TTMMJJJJ (ddMMyyyy). Und wenn die Dateneingabe konsequent 6- bzw. 8-Stellig ist, dann wird beispielsweise Neujahr 2020 derart als Text eingegeben: 010120 oder 01012020, wie hier in Deutschland üblich bzw. Standard in Sachen Reihenfolge.

Aber leider ist es ja so, dass gerade bei kalendarischen Daten eine ungemeine Vielfalt möglich ist und Excel in der Lage sein soll, all diese Formate richtig zu deuten. Ich werde mich hier im Wesentlichen dennoch auf die Eingabe des deutschen Datumsformats beschränken, fremde Formate (USA oder ANSI) werden ja meist aus externen Quellen übernommen und sind dort schon in einem „vernünftigen“ Format vorhanden, also mit Trennzeichen zwischen den Einheiten. Die einzige Ausnahme ist ANSI ohne Trennzeichen, worauf ich weiter unten auch noch eingehen werde.

Datum TTMMJJJJ

Erst einmal ein Hinweis meinerseits: Ich werde im folgenden Text fast ausschließlich die korrekte Mehrzahl-Form des Wortes „Datum“ verwenden: „Daten“. Okay, das kann im normalen deutschen Sprachgebrauch auch missverständlich sein, darum verwende ich der Eindeutigkeit wegen in meinen Beiträgen auch sehr oft „kalendarische Daten“. In diesem Beitrag möchte ich mir diese Konstrukte weitgehend „verkneifen“ und Sie wissen gewiss auch so in jedem Fall, was gemeint ist. 😎 

Grundsätzlich gilt auch für die in Excel von Hand eingegebenen Daten, dass die Eingabe explizit als Text erfolgt ist. Wichtig ist dabei, dass die Spalte vor der Eingabe der Ziffernfolgen als Zahlenformat Text eingerichtet (formatiert) worden ist oder diese Daten-Eingabe immer mit einem Auslassungszeichen ' beginnt. Wenn Sie beispielsweise aus einem ERP-Programm exportierte CSV-Files in Power Query importieren, sollte das Original-Format auch führende Nullen enthalten und diese Daten können dann nach dem Import in PQ gut in die gewünschte Form gebracht werden. Die CSV-Daten stellen sich dann beispielsweise so dar (gerne hier zum Download „nur“ als Übungsplattform):

Da.tum;(Datum)
01.01.2000;01012000
10.01.2000;10012000
01.01.00;010100
10.01.00;100100
17.03.1919;17031919
17.03.19;170319
29.02.2020 14:50;29022020 1450
1-1-00;1100

Wie auch im ersten Beitrag dieser Trilogie habe ich eine 2-spaltige Musterdatei erstellt, wo in der ersten Spalte das Wunsch-Datum (Wunschergebnis) dargestellt wird und in der zweiten, vorher als Text formatierten Spalte die händischen Text-Daten, welche gleich im Anschluss zu einem korrekten Datum konvertiert werden sollen. Laden Sie dieses File hier herunter; es sind die gleichen Daten wie in der CSV, nur bereits im Arbeitsblatt nach meinen Vorgaben formatiert. Nach dem Import in den Power Query-Editor¿ stellt sich das dann so dar:

Direkt nach dem Import


Das sieht doch schon einmal sehr gut aus. Die Spalte (Datum) ist sogar schon als Text und mit führenden Nullen formatiert. Wobei es mich schon irritiert, dass Power Query gar nicht versucht hat, diese Spalte zumindest teilweise in den Typ Ganze Zahl umzuwandeln (also insgesamt als Datentyp: Beliebig). Und die Spalte Da.tum ist sogar auch vom Datentyp: Text😐 

Auch wenn sie es sich nicht vorstellen können, solch ein „Kuddelmuddel“ von unterschiedlichen Daten gibt es tatsächlich in der Praxis, wenn auch sehr selten. Das passiert schon einmal, wenn mehrere Nutzer in einer Tabelle Daten eingeben oder unterschiedliche eigenständige Arbeitsblätter zusammengefügt wurden.  – Ich nutze die Gelegenheit mit Ihnen gemeinsam zu erarbeiten, wie sie da eine gewisse Logik hineinbringen können und Sie es schaffen, Schritt für Schritt die Spalte (Datum) zu einem „echten“ Datumswert umzuwandeln bzw. in einer weiteren Spalte darzustellen.

Analyse

Stellen sich zuerst einmal die Frage, welche Daten gleichartig, gleichförmig sind. Weiterhin sollten Sie erkennen, ob dort auch wirklich grobe „Ausrutscher“ dabei sind. Ich komme bei meiner Analyse auf 3 bzw. 4, Gruppen. Prinzipiell wären dieses:

  • Grundsätzlich korrektes Datum (ohne Trenner), wobei Sie noch zwischen 2-oder 4-stelliger Jahreszahl unterscheiden könnten (also eine oder zwei Gruppen).
  • Datum mit Uhrzeit
  • Prinzipiell fehlerhaftes Datum (Zeile 8).

Ich beginne einfach einmal mit dieser fehlerhaften Zeile 8, die auch von/in plain Excel in dieser Form beim besten Willen nicht als Datum interpretiert werden kann. Auch wenn das der 1.1.2000 (oder vielleicht sogar 1900?) sein soll, es könnte ja auch der Monat 11 des Jahres 2000 (oder 1900) sein (was dann als der 1.11.00 interpretiert werden würde). Die aus meiner Sicht einzig logische Folgerung: Löschen Sie diese Zeile entweder in Excel oder aber hier im Power Query-Editor. Alternativ können Sie es naturgemäß bei dieser Ziffernfolge belassen und darauf vertrauen, dass PQ in vielen Fällen einen Fehler erzeugt, der dann entsprechend in der Auswertung als leeres Feld oder als Fehler erkennbar ist. Eine Korrektur in Excel kommt gewiss nur dann infrage, wenn Sie ganz genau wissen, welches Datum dieses sein soll.

Tripelschritte, aber verständlich

Sie werden es sich beim Lesen der Überschrift denken können, dass hier viele kleine Schritte zum gewünschten Erfolg führen. Das hat in jedem Fall den Vorteil, dass nicht so versierte User die Vorgehensweise gewiss besser verstehen und auch nachvollziehen können. Das Grundprinzip basiert auf folgender Überlegung: In den Text sollen ja auf jeden Fall nach den ersten zwei und dann wiederum nach den nächsten zwei Ziffern ein Punkt eingefügt werden, damit zumindest erst einmal aus optischer Sicht ein Datum erkennbar ist. Gehen Sie dazu so vor:

  • Markieren Sie die Überschrift (Datum).
  • Spalte teilen | Nach Anzahl von Zeichen | Anzahl von Zeichen 2 | Einmal, so weit links wie möglich.
  • Markieren Sie nun die Überschrift (Datum).2.
  • Auch hier das gleiche Vorgehen wie eben, also ein Mal nach dem zweiten Zeichen (was MM entspricht) teilen.
  • Markieren Sie die Spalte (Datum).1 und anschließend mit Shift oder Strg in die Spalte (Datum). 2.1 klicken.
  • Im Menüband (Start) Gruppe Transformieren den Datentyp: Text auswählen.
  • Nun alle drei restlichen Spalten ab (Datum).1 markieren, Rechtsklick in eine der markierten Überschriften und Spalten zusammenführen.
  • Bei Trennzeichen wählen Sie –Benutzerdefiniert– und schreiben dann in das leere Textfeld darunter nur einen Punkt . .
  • Neuer Spaltenname (optional) sollte Datum sein.

Vielleicht fällt Ihnen jetzt ja auf, dass die Daten mit den Tagen vor dem 10. des Monats auch nur einstellig dargestellt werden. Das liegt daran, dass sie nach dem trennen der Spalten die Schrittumwandlung zu Text für die Tages- und Monatsspalte gemeinsam durchgeführt haben. Wenn Sie auch an dieser Stelle schon beispielsweise die Darstellung 01.01.2000 haben möchten, dann hätten sie vorher die Spalten für den Tag und den Monat einzelnen, also nacheinander in den Typ Text konvertieren müssen. Und ach ja, wie auch zuvor schon angemerkt ist die Zeile 8 nicht wirklich als Datum erkennbar. 😆 

Wechseln Sie nun erforderlichenfalls zum Menü Transformieren, markieren Sie die Spalte Datum und wählen in der Gruppe Datums- & Uhrzeitspalte das Symbol Datum. Im Dropdown ist der einzig wählbare Punkt Analysieren und genau da genau darauf werden sie auch klicken.

Die ersten 6 Zeilen sehen ja nun sehr vernünftig aus und sind auch offensichtlich im korrekten Format. Und Zeile 8 ist schon so gut wie klar, dass hier eine Fehlermeldung kommt. Bei der Zeile darüber ist das aber nicht so ganz ersichtlich. Aber im Gegensatz zu Excel nimmt es Power Query sehr genau. Das was dort im letzten Schritt stand ist nicht der Datentyp Datum sondern Datum/Uhrzeit. Und darum „meckert“ Power Query auch diese Zelle an.

Sonderfall Zeile 7

Noch einmal vorab angemerkt: In eine Spalte einer ordentlichen Tabelle gehört immer nur der gleiche Datentyp. Wird diese Regel nicht eingehalten, wird es voraussichtlich immer wieder irgendwo zu Komplikationen (wie in diesem Fall) kommen. Aber es gibt durchaus Möglichkeiten, das Ziel zu erreichen. Hier ist es ja nicht das „reine“ Datum sondern auch die Uhrzeit, welche den Unterschied zu den darüber liegenden Zellen ausmacht.

Beginnen Sie damit, dass sie den letzten Schritt im rechten Seitenfenster (hier: Analysiertes Datum) löschen. Jetzt sind wieder alle Daten eischließlich Zeile 7 vorhanden. Die gewiss einfachste und konsequenteste Lösung wäre, die Uhrzeit schlichtweg zu ignorieren, nicht zu berücksichtigen. Dann bleibt es auch bei einer einzigen Spalte mit dem Datum und dem immer gleichen Datentyp Datum.

Um das zu erreichen werden sie Datum markieren, Rechtsklick und Spalte teilen | Nach Trennzeichen | Leerzeichen und Power Query teilt die Spalte Datum entsprechend. Sie erkennen zweierlei Änderungen: Es gibt 2 Datum-Spalten, wo in der 2. Spalte die abgetrennte Uhrzeit steht aber auch in der 1. Spalte wieder eine Umwandlung in Ganze Zahl vorgenommen worden ist. Der letzte (automatisch eingefügte) Schritt ist Geänderter Typ3. Löschen Sie hier im rechten Seitenfenster diesen Schritt und die Spalte Datum. 1 enthält wieder ein Datum.

Löschen Sie nun Spalte Datum.2. Benennen Sie Datum.1 wiederum zu Datum um. Nun können Sie den Datentyp beispielsweise über das Menü Home oder per Rechtsklick ändern oder aber sie wählen wie bereits vorher den Weg über Transformieren und der automatischen Analyse des Datums. Der Effekt ist jeweils identisch.

Das Ziel ist erreicht, auch wenn beim irregulären Datum der Zeile 8 der Wert Error eingetragen ist. Schließen & laden oder Schließen & laden in… und sie haben ein gutes Ergebnis. Dieses „glänzt“ auch nicht durch eine Fehlermeldung in der letzten Zeile, hier ist der Wert einfach nur leer geblieben. 😎 

ANSI ohne Trennzeichen, yyyyMMdd

Ein echtes ANSI-Datum ist immer 8-stellig und auch immer diese Reihenfolge: Jahr (4 Stellen), danach jeweils 2-stellig Monat und Tag. Die vielfach verwendete offizielle Bezeichnung dafür ist ISO 8601. Und wenn es nach der Norm geht, gilt dieses Format auch in Deutschland, Österreich, Schweiz und weitere Staaten. Das Format gilt zumindest, wenn kein Trennzeichen zwischen den Elementen verwendet wird. Ein Bindestrich wird in den meisten Fällen beim „internationalen Datumsformat“ sehr oft verwendet und dann erkennt Power Query sogar automatisch das korrekte Datum.

Heute (wo ich gerade diesen Text verfasse) ist der 19. Juni 2020, was entsprechend ohne Trenner nach der ANSI-Norm 20200609 geschrieben werden würde.  Die erste, denkbare Möglichkeit lehnt sich stark an die weiter oben gezeigte, allererste Lösungsmöglichkeit an. Zuerst einmal werden Sie Sorge dafür tragen, dass der Inhalt der Spalte vom Datentyp Text ist. Normalerweise sind das ja reine Zahlen. Dann Spalte teilen nach 4 Zeichen und die verbleibenden (letzten) 4 Ziffern/Zeichen erst in den Datentyp Text umwandeln oder die letzte Zeile bei Angewendete Schritte (Geänderter Typ) löschen (sonst steht in diesem Beispiel nur die Zahl bzw. Ziffernfolge 609 dort) und dann den 4-stelligen String nach 2 Stellen teilen. Auch wenn das Ergebnis nun wieder alles Datentyp: Zahl ist, das macht in diesem Fall nichts und kann so bleiben.

Wenn es Ihnen hilft, können Sie den Spalten auch die Überschriften Jahr, Monat, Tag geben; das ist weniger abstrakt. Klicken Sie nun zuerst in die Überschrift Tag, um die Spalte zu markieren. Strg und dann Monat markieren und anschließend auch noch Jahr. Rechtsklick in eine der markierten Überschriften und Spalten zusammenführen. Als Trennzeichen gehen Sie wiederum über –Benutzerdefiniert– und tragen den Punkt. ein. Alternativ könnten Sie auch den Bindestrich (Minuszeichen) verwenden. In jedem Fall werden Sie anschließend den Datentyp: Datum direkt wählen und das Datum steht wie gewünscht dort.

▲ nach oben …

Möchten Sie den Weg über eine neue Spalte und den Funktions-Editor gehen, dann wechseln Sie zum Menü Spalte hinzufügen und wählen dort den Eintrag Spalte aus Beispielen. Ich habe einmal für die Demonstration 2 weitere Daten eingefügt und das stellt sich dann so dar:

Eine Spalte aus Beispielen hinzufügen, das Start-Bild

Im rechten Teil des inneren Fensters ist eine leere Tabelle mit der Überschrift Spalte2 eingefügt worden. Dort ist in einem dunkleren Grün die erste Zeile markiert, in einem helleren Grün die erste Zeile der Spalte1. Schreiben Sie das gewünschte Ergebnis:

Power Query schlägt auch sinnvolle Einträge vor…

… und wenn Sie dann in der zweiten Zeile den 11.03.2019 von Hand eintragen, wird PQ automatsch die restlichen verbleibendn Zeilen nach diesem Muster ausfüllen. Bestätigen Sie mit OK und die Daten werden als Text die Abfrage übernommen. Die erforderliche Zuweisung des Datenformats sollte Ihnen nun geläufig sein.

▲ nach oben …

IP-Adresse

Auf deutschen Tastaturen ist es wirklich unbequem, Kolonnen von IP-Adressen einzugeben. Die 1 bis 3 Ziffern liegen alle so schön auf dem Nummernblock rechts und der Punkt ist dann außerhalb dieses Bereichs. Natürlich könnten Sie statt des Punktes einfach das Komma vom Zehnerblock nehmen und nach Abschluss der Eingabe in der entsprechenden Spalte das Komma durch den Punkt ersetzen. Aber na ja, schön ist etwas anderes, oder? Ach ja, wenn Sie die Trenn-Methode anwenden wollen ist es natürlich ausgesprochen wichtig, dass bei der Eingabe der Ziffern immer auf 3 Stellen je Block durch führende Nullen aufgefüllt wird. Sonst klappt das ganze Vorhaben nicht oder die Methode mit einem beliebigen Trenn-Zeichen der Zehnertastatur (Ziffernblock) ist doch die bessere. 🙄 

Um beim bekannten Prinzip zu bleiben gilt auch hier, die eingegebenen Ziffern erst einmal wegen eventuell führender Nullen in das Text-Format umzuwandeln. Anschließend über Start | Spalte teilen | Nach Anzahl von Zeichen eine 3 eingeben und Wiederholt markiert lassen. Die 4 Ergebnis-Spalten werden dann Zahlen enthalten, das kann aber so bleiben. Denn standardmäßig werden die IP-Adressen ohne führende Nullen dargestellt. Also von links nach rechts markieren, Spalten zusammenführen und den Punkt als Trenner verwenden. Bei einer alternativen Eingabe-Form entfällt natürlich das Zusammenführen und das hilfsweise genutzte Zeichen muss nur durch den Punkt ersetzt werden.

▲ nach oben …

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag Ihrerseits z.B. 2,50  freuen …

Dieser Beitrag wurde unter Datum & Zeit, Datum und Zeit, Ohne Makro/VBA, Power Query, PQ-Formeln (Sprache M) abgelegt und mit , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.