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: Uhrzeiten
Wissensstand: Level 2 ⇒ Solides Basiswissen in Excel, etwas Erfahrung in PQ
Teil 1: Uhrzeiten konvertieren
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 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.
Uhrzeit
Grundsätzlich stellt sich zu Beginn die Frage, wie die Daten eingegeben oder woher sie importiert worden sind. Gemeint ist damit, dass es durchaus einen Unterschied ausmacht, ob die Eingabespalte als Standard oder als Text formatiert ist. Nehmen Sie als Beispiel einmal 7:30 Uhr in der Frühe. Bei einer händischen Eingabe 730 ist es egal, ob die Spalte als Text oder Standard formatiert ist. Es wird immer die Zahl 730 in der Zelle stehen. Anders sieht es aus, wenn immer ein 4‑stelliger Wert als 0730 eingetragen wird. Dann wird beim Standard-Format die führende Null entfernt, bei einer Text-formatierten Spalte wird dann wunschgemäß 0730 erfasst sein.
Text, 4‑stellig
Am einfachsten ist es, wenn die Zeitangaben immer 4‑stellig eingegeben und auch wegen der vorher eingerichteten Spalten-Formatierung Text in genau dieser Form in die einzelnen Zellen übernommen worden sind. Dann steht fest, dass an der dritten Stelle der Zeichenfolge ein : eingetragen werden muss. Das lässt sich mit Power Query auf verschiedenen Wegen realisieren…
„Altbacken” 😉 (konservativ)
- Markieren Sie nach dem Import der Daten die entsprechende Spalte(n).
- Ändern Sie den entsprechenden Datentyp der Spalten bei Bedarf auf Text.
- Spalte teilen | Nach Anzahl von Zeichen | 2.
- Das Datenformat beider neu erstellter Spalten bei Bedarf wiederum auf Text ändern.
- Die beiden Spalten nacheinander (erst Stunden, dann Minuten) markieren.
- Menü Transformieren | Spalten zusammenführen | Trennzeichen: Doppelpunkt | Neuer Spaltenname kann beliebig eingegeben werden, gerne auch wieder der ursprüngliche Name (z.B. Spalte1).
- Gleiche Vorgehensweise bei weiteren betroffenen Spalten.
- Alle derart „behandelten” Spalten als Zeit formatieren.
An dieser Stelle noch einmal der Hinweis, dass das über diesen Weg nur funktioniert, wenn die Werte in Excel als „echter” Text vorliegen. Also auch in der Excel-Tabelle beispielsweise 0730 zu sehen ist.
Als M-Funktion
Etwas „schlanker” (ich meide bewusst den Begriff „eleganter”) lässt sich das mit der Funktionalität der Sprache M lösen. Bei gleicher Ausgangslage (1 Spalte mit den zu formatierenden Ziffern) gehen Sie so vor:
- Menü Spalte hinzufügen | Benutzerdefinierte Spalte
- Im Dialog…
- Neuer Spaltenname: nach Belieben
- Benutzerdefinierte Spaltenformel:
Text.Insert([Spalte1],2,":")
- Diese Spalte als Zeit formatieren
- Die ursprüngliche Spalte (die ohne den Doppelpunkt) löschen.
Dass im Power Query-Editor die als Zeit formatierten Werte stets mit Sekunden dargestellt werden, ist normal und korrekt. In der anschließend gespeicherten Excel-Tabelle kann dann eine wunschgemäße Formatierung vorgenommen werden.
Text, Standard oder Zahl, 1 bis 4‑stellig
Ist die Excel-Spalte als Standard oder Zahl formatiert, dann wird die Eingabe von 0000 (statt eigentlich 00:00) zum Ergebnis 0 führen, soweit (der Vereinbarung entsprechend) kein Doppelpunkt als Trenner bei der Eingabe verwendet worden ist. Also muss in Power Query irgendwie dafür gesorgt werden, dass das Zwischenergebnis (Ziffern ohne Doppelpunkt) vierstellig ist, um dann die hierüber beschriebenen Vorgehensweisen durchführen zu können.
Einige Muster-Daten sind rasch in die Zeilen einer Spalte eingegeben, die in Sachen Zahlenformat als Standard oder Zahl eingerichtet ist. Beginnend mit der Eingabe 0000 gerne mehrere Zeiten unter 10:00 Uhr (natürlich ohne den Doppelpunkt) bis beispielsweise 1000 oder später. Sie erkennen selber die geänderte Darstellung in Excel und das spiegelt sich naturgemäß auch nach einem Import in Power Query wieder. Ich habe eine kleine Muster-*.xlsx erstellt, welche Sie hier herunterladen können. Nach dem Import sieht das dann so aus:
Die Daten nach dem Import
An den Symbolen links in den Überschriften erkennen Sie bereits das Format der Spalte. Die Spalte Zeit dient einzig dem Vergleich zum endgültig zu erzielenden Ergebnis!
Im ersten Schritt werden Sie dafür sorgen, dass jede Zelle der Spalte (Zeit) aus 4 Ziffern besteht. Und das ist naturgemäß nur machbar, wenn der Datentyp nicht Zahl sondern Text ist. Und ja, es gibt natürlich viele Wege, um das zu erreichen. Um nicht nur Einsteigern gerecht zu werden, stelle ich Ihnen zwei der Möglichkeiten vor. 😉
Zahl zu 4‑stelligem Text (Einsteiger)
Da ja mindestens 1 Ziffer in der Zelle steht, müssen 0 bis 3 Nullen vorangestellt werden, um eine 4‑stellige Ziffernfolge zu erlangen. In jeder Power Query – Version funktioniert diese Vorgehensweise:
- Menü Spalte hinzufügen | Benutzerdefinierte Spalte
- Bei Neuer Spaltenname können Sie die Vorgabe belassen, ich verwende Auffüllen.
- In das Feld Benutzerdefinierte Spaltenformel geben Sie
"000"(mit den Anführungszeichen) ein. - Wechseln Sie zum Menü Transformieren
- Die Spalte Auffüllen ist markiert, Strg und ein Klick in die Überschrift (Zeit). Achten Sie auf die Reihenfolge! 💡
- Rechtsklick in eine der beiden markierten Überschriften, im Kontextmenü ein Klick auf Spalten zusammenführen und es im Feld Trennzeichen bei –Keine– belassen. OK
- Zusammengeführt ist markiert, Home bzw. Start (oder auch Rechtsklick), Spalte teilen | Nach Anzahl von Zeichen… | Anzahl von Zeichen: 4 | Einmal, so weit rechts wie möglich, dann OK.
- Löschen Sie sie Spalte Zusammengeführt.1 und benennen Sie die Spalte Zusammengeführt.2 der Transparenz wegen wieder zu (Zeit) um.
- Normalerweise wurde nun seitens Power Query (2 Zeilen weiter oben im rechten Seitenfenster) automatisch ein Schritt Geänderter Typ1 erstellt. Dadurch wurde aus dem 4‑stelligen Text wieder eine (gekürzte) Zahl gemacht. Löschen Sie im rechten Seitenfenster diese Zeile. ¿
Durch diese Schritte ist das Interims-Ziel einer 4‑stelligen Ziffernfolge mit erforderlichenfalls führenden Nullen erreicht und die Spalte hat auch einen „vernünftigen” Namen. 😉
Zahl zu 4‑stelligem Text (neuere Excel-Versionen)
In etwas knapperer Form der durchaus auch kürzere Weg, der Sie mit weniger Schritten zum Ziel bringt, falls Ihre PQ-Version (derzeit ab 2019) dieses Vorgehen bereits unterstützt. Ausgangslage ist wieder direkt nach dem Import, die Spalte (Zeit) ist markiert …:
- Transformieren | Textspalte | Format | Präfix hinzufügen | 000, dann OK.
- Textspalte | Extrahieren | Letzte Zeichen | 4
… und schon steht in der Spalte (Zeit) der gewünschte, 4‑stellige Text mit führenden Nullen, falls erforderlich. In der folgenden Schrittfolge soll aus dieser Ziffernfolge dann eine Uhrzeit generiert werden.
Zahl zu 4‑stelligem Text (Fortgeschrittene)
Wiederum in allen Excel- genauer gesagt: Power Query-Versionen gibt es den Weg über eine Formel in der Sprache M. Ausgangslage ist (natürlich) die unbearbeitete, im PQ-Editor frisch importierte Datei. Auch hier erstellen Sie erst einmal eine neue Spalte:
- Spalte hinzufügen, Benutzerdefinierte Spalte.
- Neuer Spaltenname können Sie erst einmal so belassen, Sie werden später sowieso eine Anpassung vornehmen.
- Bei Benutzerdefinierte Spaltenformel geben Sie erst einmal diese Formel ein:
"000" & Text.From([#"(Zeit)"]) - Nach einem OK erkennen Sie, dass fast alle Texte zu lang sind. Darum öffnen Sie per Doppelklick auf die letzte Zeile bei Angewendete Schritte im rechten Seitenfenster noch einmal den Editor und ergänzen die vorhandene Formel so:
Text.End("000" & Text.From([#"(Zeit)"]), 4) - OK, und das Ergebnis stimmt. Lesen Sie in der Hilfe zu den Funktionen gerne einmal die Erklärungen zu den beiden Funktionen nach.
- Löschen Sie die Spalte (Zeit), nachdem Sie stichprobenartig die Ergebnisse mit den Ausgangswerten verglichen haben.
- Benennen Sie die letzte Spalte nach Ihren Wünschen um, gerne wieder als (Zeit).
Generierung der Uhrzeit
Auch hier werde ich Ihnen zwei unterschiedliche Wege aufzeigen. Welchen der beiden Abläufe Sie nutzen, bleibt Ihrem eigenen Geschmack überlassen. Allerdings ist der zuerst vorgestellte Weg deutlich verständlicher und gewiss leichter nachzuvollziehen.
Klassisch und einfach
Da jetzt ja in jeder Zelle des entsprechenden Spaltenbereichs eine 4‑stellige Ziffernfolge steht stimmt in jedem Fall die Aussage, dass nach genau 2 Stellen ein Doppelpunkt als Trennzeichen eingefügt werden muss. Dadurch wird (zumindest erst einmal optisch) die Ziffernfolge zu einer Uhrzeit. Der einfachste Weg stellt sich aus meiner Sicht so dar:
- Markieren Sie die Spalte mit den geänderten Daten, wenn Sie meiner Empfehlung gefolgt sind ist das die Spalte (Zeit).
- Menü Home (oder Rechtsklick), Spalte teilen | Nach Anzahl von Zeichen… | 2.
- Im rechten Seitenfenster bei Angewendete Schritte den letzten, automatisch hinzugefügten Schritt (Geänderter Typ1) löschen ¿, damit in den beiden neu generierten Spalten statt der Zahl wieder ein 2‑stelliger Text steht.
- Selbst wenn die beiden Spalten schon markiert sein sollten, der Vorsicht halber: Klicken Sie zuerst in die Überschrift (Zeit).1, Strg oder Shift und dann ein Klick in die Überschrift (Zeit).2.
- Menü Transformieren und in der Gruppe Textspalte Klicken Sie auf Spalten zusammenführen. Als Trennzeichen wählen Sie Doppelpunkt. Bei Neuer Spaltenname können Sie wieder (Zeit) eingeben, da es diese Bezeichnung derzeit ja nicht mehr gibt.
- Ein Rechtsklick in (Zeit), dann Typ ändern | Zeit bewirkt, dass nun korrekte Zeitangaben in der Spalte stehen. Hinweis: Wenn Sie das nicht machen, wird Excel nach Schließen & laden die Uhrzeit nicht wie gewünscht erkennen und vermutlich die (korrekte) serielle Zahl der Zeitangabe in die Zellen schreiben.
Wie bereits erwähnt zeigt der Power Query-Editor automatisch auch die Sekunden mit an. Nun können Sie auch die Spalte Zeit löschen, sie wird nicht mehr gebraucht. Das Format der Uhrzeit werden Sie wahrscheinlich nach dem Speichern in ein Arbeitsblatt auf hh:mm anpassen.
Fortgeschritten mit der Sprache M
Die Ausgangslage ist in diesem Fall, dass in der zu bearbeitenden Spalte (Zeit) 4‑stellige Ziffernfolgen als Text stehen. Das Ziel ist das gleiche, nämlich an dritter Stelle einen Doppelpunkt einzufügen und die Spalte anschließend als Datentyp Zeit zu formatieren. Diese Schritte sind zielführend:
- Falls noch nicht geschehen löschen Sie idealerweise gleich zu Beginn die erste Spalte Ze:it mit den Uhrzeiten, damit nur noch die 4‑stelligen Ziffernfolgen in der Abfrage enthalten sind. Das schon Ressourcen (auch wenn das bei diesen wenigen Datensätzen nicht spürbar ist).
- Wechseln Sie zum Menü Spalte hinzufügen.
- Benutzerdefinierte Spalte und geben Sie bei Neuer Spaltenname beispielsweise Zeit (also ohne die einschließenden Klammern) ein.
- In das große Textfeld Benutzerdefinierte Spaltenformel schreiben Sie nun diese Formel:
= Text.Insert([#"(Zeit)"], 2, ":")
und bestätigen Sie mit OK. - Weisen Sie der Spalte Zeit auf beliebige Weise den Datentyp Zeit zu.
- Hinweis: Sie könnten die Formel auch ‑ähnlich wie schon weiter oben dargestellt- noch einmal ändern, um in 1 Schritt ein Zeit-Format zu generieren:
= Time.From(Text.Insert([#"(Zeit)"], 2, ":"))
Für die ursprüngliche Formel bin ich Ihnen noch eine Erklärung schuldig. 😉 Der Name der Funktion Text.Insert sollte klar sein, sofern Sie der englischen Sprache einigermaßen mächtig sind. Nach der öffnenden runden Klammer folgt in eckige Klammern eingefasst der Spaltenname. Falls Sie den Spaltennamen per Doppelklick eingefügt haben, wurden die Raute # und die Anführungszeichen " automatisch hinzugefügt, weil die Runden Klammern im Namen der Überschrift dieses erforderlich machen.
Es folgt ein Komma und anschließend die Position, an welcher das folgende Argument (der Doppelpunkt) eingefügt werden soll. Für Excel-Anwender ist es gewiss gewöhnungsbedürftig, dass in Power Query die Zählweise so gut wie immer mit 0 und nicht mit 1 beginnt. Darum wird aus der real dritten Position in diesem Fall die 2 als Argument verwendet. Und Sie sehen, das Ergebnis stimmt.
Immer 4- oder 6‑stellig (typischerweise als Text)
Wenn die Ziffernfolgen garantiert (gerne durch Ihre Vorarbeit 😛 ) in 4- bzw. 6‑stelliger Länge vorliegen, dann gibt es Power Query eine ausgesprochen „schicke” Möglichkeit. In den Abbildungen dieses Beispiels habe ich die eingegebene Uhrzeit einmal ganz bewusst auf einen 6‑stelligen Text erweitert. Vorgehensweise wäre in diesem Fall so:
Menü Spalte hinzufügen | Spalte aus Beispielen:
Erst einmal hier Klicken …
Der Power Query-Editor stellt sich nun etwas ungewohnt dar. In der linken Tabelle erkennen sie ihre importierten und entsprechend erweiterten Daten, rechts eine leere Tabelle mit der Überschrift Spalte1. Jeweils die erste Zeile der beiden Tabellen ist markiert:
Ein vollkommen ungewohntes Bild… Ganz rechts werden Sie aktiv 😉
Entfernen Sie erst einmal das Häkchen in der Überschrift der ersten Spalte Zeit. Dann ein Doppelklick in der rechten Tabelle in die erste Zeile und es tut sich ein Kontextmenü auf. Sie erkennen, dass bereits mehrere mögliche neue Formatierungen vorgeschlagen werden:
Der rote Pfeil ist von mir als (hilfreicher) Hinweis eingefügt worden
Auch hier ein Doppelklick in jene Zeile, die das künftige Wunschformat darstellt (ich habe mit dem roten HinwE•I•SPfeil die Position gekennzeichnet). Ruck zuck werden die restlichen Zeilen der rechten Tabelle mit vorgeschlagenen Werten (in grau) dargestellt:
Eine Überprüfung ist hier durchaus sinnvoll
Bei diesen wenigen Zeilen ist es durchaus möglich und zumutbar, einen einzelnen Abgleich der vorgeschlagenen und der gewünschten Werte vorzunehmen. Bei größeren Datenmengen werden sie sich zuerst mit einigen Stichproben begnügen. In jedem Fall Klicken Sie bei bestandene Prüfung rechts oben auf die Schaltfläche OK. Die Daten werden dann in die „normale” Abfrage übernommen. Wenn es dann doch für eine Prüfung zu viele berechnete Zeitwerte sind und die ursprüngliche Spalte Ze:it noch in der Abfrage existiert, empfehle ich Ihnen folgendes Vorgehen: Spalte hinzufügen, Benutzerdefinierte Spalte und tragen Sie als Benutzerdefinierte Spaltenformel ein:
= [#"Ze:it"]=[Zeit]
wobei sie die Spaltennamen natürlich per Doppelklick aus dem rechten Kasten übernehmen. Das Ergebnis ist in jedem Falle eine Logik-Auswertung. Erweitern Sie die Überschrift durch Klick auf 
und es sollte ausschließlich der Wert TRUE zur Auswahl stehen. Ist ein FALSE dabei, dann entspricht mindestens eine Zeile nicht dem Wunschergebnis.
Abschluss
Die Aufgabe ist damit praktisch erledigt. In der Abfrage existiert nur noch die Spalte mit den korrekten Zeiten, nachdem Sie die nicht erforderlichen Spalten gelöscht haben. Die Überschrift kann an dieser Stelle noch so angepasst werden, wie sie später im Arbeitsblatt stehen soll. Schließen & laden oder Schließen & laden in… und die Abfrage wird entweder in ein neues Arbeitsblatt oder an eine von Ihnen zu bestimmende Position geschrieben.
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 3,00 € freuen …