073827 in 07:38:27 als Zeit umwandeln, Teil 2
Den ersten Teil dieser Trilogie können Sie hier lesen.
Den dritten Teil dieser Trilogie können Sie hier lesen.
Wissensstand: Level 2 ⇒ Solides Basiswissen in PQ, etwas Erfahrung
Als Zahl, naturgemäß unterschiedliche Länge
In dieser Aufgabe auf der Basis des Files Zeiten_2.xlsx habe ich mich auf die Excel-Version der Daten beschränkt. Es sind die gleichen Werte wie in den vorherigen Übungen. Nur sind „natürlich” alle führende Nullen durch Excel entfernt worden, denn der Datentyp ist hier Zahl und nicht Text. Nach der Formatierung als Tabelle und dem Import in Power Query stellt sich der Editor so dar:
Gleiche Zeitangaben wie gehabt, nur als Zahl statt als Text
Das Ziel vor Augen, dass hier die gleichen „echten” Zeiten generiert werden sollen wie in den vorherigen Übungen im ersten Teil sollte sie vielleicht etwas nachdenklich machen. Offensichtlich müssen ja je nach Länge der Ziffernfolge keine (also 0) bis 3 bzw. 5 Nullen vor die dargestellte Zahl platziert werden, damit der Weg über das Teilen der Spalte erfolgreich sein wird. Und genau so ist es auch.
Versuchen Sie gerne, wiederum die Zeile mit dem geänderten Typ bei den angewendeten Schritten zu entfernen. Es nutzt nichts, die Zahlen bleiben im derzeitigen Format und der aktuellen Länge. 😕
Der einfachste und wohl auch am ehesten zu verstehende Weg besteht darin, die Zahlen in Text umzuwandeln und anschließend mindestens die benötigte Anzahl von Nullen davor zu schreiben. Und auch wenn es einige Schritte mehr sind als bei einer Formel-Lösung, das Ganze ist auch hier erfreulicherweise in der GUI (in der grafischen Benutzer-Oberfläche) mit einigen Mausklicks machbar.
Ändern Sie zu Beginn den Datentyp der einzigen Spalte in Text, soweit noch nicht geschehen. Überlegen Sie, wie die maximale Anzahl von voranzustellen Nullen ist, unabhängig von der jeweiligen Zahl in der Zelle. Ich denke wir sind uns einig, dass es maximal 3 bzw. 5 Nullen sind um zum Ergebnis zu kommen, damit 4 bzw. 6 Zeichen in der Spalte sein werden. Im Register Spalte hinzufügen wählen Sie in der Gruppe Allgemein das Symbol Benutzerdefinierte Spalte. Geben Sie im großen Textfeld Benutzerdefinierte Spaltenformel nach dem bereits eingefügten und nicht zu löschenden = diesen Text genau so ein: "00000":
Diese Formel füllt eine Spalte mit 5 Nullen
In der ersten der beiden Dateien werden Sie wahrscheinlich nur 3 Nullen verwenden, aber selbst wenn Sie hier die 5 Nullen eingeben ist das nicht schädlich oder gar falsch. Mehr dazu gleich. – Durch die beiden äußeren mit eingegebenen Anführungszeichen " wird aus den 3 bzw. 5 Ziffern ein Text und die scheinbar überflüssigen Nullen werden nicht automatisch gelöscht. Nach einem Klick auf OK wird eine neue Spalte erzeugt, wo ausschließlich diese Zeichenfolge in voller Länge enthalten ist.
Jetzt werden sie vielleicht zu Recht sagen, dass das ja je nach Größe der Zahl zu viele Nullen sind. Richtig, aber das macht rein gar nichts. Klicken Sie nun zuerst auf die Überschrift Benutzerdefiniert und anschließend mit Strg oder Shift in die Überschrift Zeit. Sie befinden sich ja noch im Menü Spalte hinzufügen, hier in der Gruppe Aus Text ein Klick auf Spalten zusammenführen. Da sich Power Query die Reihenfolge des Markierens der jeweiligen Spalte merkt, werden auch erst die Nullen und dann die Zahlen in einer neuen Spalte zusammengeführt. Ausnahmsweise respektiert es PQ, dass in der Spalte Zeit Zahlen und keine Texte stehen; in den meisten Fällen ist das nicht möglich. Das Ergebnis sieht nun so aus:
Die führenden Nullen und die Zahlen sind zusammengeführt
Die Spalte Zusammengeführt enthält nun als Text jene Daten, die anschließend zu einer korrekten Uhrzeit umgewandelt werden sollen. In Excel würden sie nun mit der Formel =RECHTS(C2, 4) die 4 rechten Zeichen der Zelle extrahieren. In Power Query wechseln Sie zum Register Start, markieren die Spalte Zusammengeführt und anschließend Spalte teilen. Wählen Sie Nach Anzahl von Zeichen und geben ‑wie gehabt- eine 4 in das Textfeld ein. Allerdings aktivieren Sie jetzt die Auswahl Einmal, so weit rechts wie möglich und erst danach OK. Löschen Sie den automatisch angefügten letzten angewendeten Schritt (Geänderter Typ), und erinnern Sie sich, dass sie genau diese Situation, diese Darstellung vorher schon einmal hatten. Sie könnten also auch die Spalte Zusammengeführt.2 nach 2 Zeichen teilen und dann wieder über das Menü Transformieren zu einer einzigen Spalte mit dem Doppelpunkt als Trennzeichen zusammenfügen.
Wieso eigentlich „könnten”? Das ist ein legitimer Weg, der auch gut nachvollziehbar ist. Also tun Sie das. Das restliche Vorgehen kennen Sie aus den ersten beiden Übungen des vorherigen Beitrags. Bleibt nur noch eines: Markieren Sie die ersten 3 Spalten jeweils durch einen Klick in die Überschrift und löschen Sie die Spalten auf beliebige Weise. Damit ist das Ziel erreicht.
In der Tabelle mit den Sekunden (Tabelle2) werden sie erst einmal den gleichen Weg gehen, dieses Mal aber garantiert mit den 5 Nullen in der 2. Spalte. Dann werden sie wiederum die 6 letzten Zeichen/Ziffern separieren, wenn auch auf eine andere Weise. Wechseln Sie zum Register Transformieren und dort in der Gruppe Textspalte Extrahieren | Letzte Zeichen und bei Anzahl geben Sie die 6 ein. Nach dem OK bleibt es bei 1 Spalte mit 6 Ziffern. Klicken Sie nun in die Überschrift Zusammengeführt, dann F2 um dort Zeit als neue Überschrift zu schreiben. Und belassen Sie es erst einmal dabei.
Und jetzt stelle ich Ihnen ein Trick vor, den ein Helfer-Kollege aus einem Forum (danke Jörg) herausgefunden hat. Wechseln Sie zum Register Transformieren, Gruppe Textspalte | Format | Bereinigen. Sie sehen, dass sie nichts sehen. Weisen Sie diese Spalte nun den Datentyp: Zeit zu. Bingo! Einer aus meiner Sicht treffliche Methode, die auch mit kalendarischen Daten funktioniert (da hatte es Jörg nämlich gefunden und erprobt).
Nun werden Sie in dieser Abfrage die ersten beiden Spalten markieren und beispielsweise mit Entf löschen, in der Abfrage Tabelle1 markieren Sie die ersten 3 Spalten und nach einem Klick in eine der markierten Überschriften bietet im Kontext-Menü die Auswahl Spalten entfernen an.
Wenige Schritte, aber „programmiert”
Für „normale” Anwender sind die bisher gezeigten Wege gewiss gut geeignet. Cracks, die Formeln lieben oder für das Programmieren schwärmen, werden an der Lösung in der Formel-Sprache „M” gewiss ihre Freude haben. Nehmen Sie zum nachvollziehen dieser Lösung gerne eine der beiden unbearbeiteten Excel-Arbeitsblätter als Grundlage. Wie gehabt, als Tabelle formatieren und in Power Query importieren. Mein Ziel ist es, mit möglichst wenigen Mausklicks in der grafischen Oberfläche auszukommen, sich also auf eine Formel zu konzentrieren und damit das Ganze etwas „schlanker” zu gestalten. Ich selbst ziehe es dabei aber vor, die Formel nicht auf ein unlesbares Maß zu schrumpfen sondern lieber die eine oder andere Funktion mehr zu verwenden und dafür auch später klar nachvollziehen zu können, was da warum geschehen ist. 💡
Nach dem Import sieht Ihr Editor ja so aus:
Gleiche Zeitangaben wie gehabt, nur als Zahl statt als Text
Register Spalte hinzufügen, Benutzerdefinierte Spalte. Als Neuer Spaltenname können Sie gerne wieder Zeit eingeben. Ich werde jetzt mit Ihnen schrittweise die Formel entwickeln, begreifen ist hier aus meiner Sicht wichtiger als (nur) abschreiben. Und ich werde auch Fehler aufzeigen, die ich in der Anfangszeit gemacht habe. Dann sehen Sie auch gleich, welcher Ausweg in diesen Fällen sinnvoll und hilfreich ist.
Ich beginne damit, diesen Text in das Feld Benutzerdefinierte Spaltenformel zu schreiben: = "00000" & [Zeit]. Den [Feldnamen] füge ich per Doppelklick im rechten Kasten (Verfügbare Spalten) in die Formel ein. Da ja ganz unten neben einem grünen Häkchen steht dass keine Syntaxfehler erkannt worden ist, sollte ein Klick auf OK einen Erfolg zeitigen. Naja, in jeder Zeile der neuen Spalte steht ein Wert. Nur nicht erwarte; 😕 hier steht Error, also „Fehler”. Klicken Sie auf das Error der Zeile 1 und ihnen wird eine Erklärung eingeblendet:
Die eingeblendete, „schlanke” Erklärung des Fehlers
Als erstes wirklich alles lesen, denn verstehen ist sehr wichtig; und anschließend den zuletzt eingefügten angewendeten Schritt (Zeit 1) im rechten Seitenbereich löschen. Damit ist dann auch die Fehlerbeschreibung verschwunden. Sie haben gelesen, dass Power Query nicht so einfach Zahlenwerte mit Texten verbinden kann. Und Sie erinnern sich, dass ich weiter oben von einer Ausnahme gesprochen hatte, als Text und Zahl verbunden worden sind. Darum Klicken Sie auf das Zahnrad 
im rechten Seitenfenster bei Hinzugefügt Benutzerdefinierte … und sie befinden sich wieder im Formel-Editor. Als erstes werde ich den abgeänderten Spaltennamen wieder auf Zeit ändern. Anschließend sorge ich per Formel dafür, dass der Inhalt der Spalte Zeit in einen Text umgewandelt wird. Ich ändere die Funktion nun so ab:
"00000" & Text.From([Zeit])
Alle Funktionen in Power Query sind „case sensitive”, die Groß- Kleinschreibung ist also exakt zu beachten! Und nach der Ergänzung der Formel sieht das Ergebnis schon erheblich besser aus. Kein Fehler, sondern ähnlich wie in der letzten Aufgabe eine lange Ziffernfolge mit führenden Nullen. Und diese Spalte muss nun noch geteilt bzw. gekürzt werden. Und die Überschrift dieser Spalte ist Zeit.1, weil in einer Liste jede Überschrift einmalig sein muss.
Wenn Sie (noch) nicht so firm sind, werden sie nun eine weitere Benutzerdefinierte Spalte erstellen, um diese Ziffernfolgen auf 6 bzw. 4 Zeichen zu kürzen. Das würde dann in der Sprache M so gehen:
= Text.End([Zeit.1], 6)
… Wobei sie naturgemäß bei den Zeiten ohne Sekunden nach dem Komma eine 4 statt der 6 verwenden würden. Wenn Sie es nun genau wissen wollen, können Sie diesen Formel-Teil natürlich auch die bisherige Formel einbauen. Das würde sich dann so darstellen:
= Text.End("00000" & Text.From([Zeit]), 6)
… und sie sehen, selbst das ist auch ganz übersichtlich und kein „Bandwurm”. Jetzt stellt sich die Frage, wie pragmatisch sie sind, ob sie eventuell diese erarbeiten Daten noch auf eine andere Weise weiterverarbeiten oder lieber doch mit einigen Mausklicks weitermachen wollen. Diesen letztgenannten Weg kennen Sie bereits, der braucht nicht noch einmal diskutiert zu werden. Sie teilen die Spalte nach gewünschter Länge und löschen die überflüssige Spalte links des gewünschten Ergebnisses oder Sie gehen den Weg über Transformieren, Textspalte | Extrahieren | Letzte Zeichen, wo dann keine „überflüssige” Spalte erzeugt wird.
Wenn Sie für weitere Fälle des Lebens mit Power Query gewappnet sein wollen und auch gerne experimentieren, bietet sich eine Formel-Lösung auf diesem Wege an: Erst einmal natürlich wieder eine neue Benutzerdefinierte Spalte (und wenn es zu Testzwecken ist) und dann diese Formel:
= Time.FromText([Zeit.1])
Im Prinzip auch hier: kurz und knackig. Und trauen Sie sich nun zu, das Ganze in eine einzige Formel/Funktion zu packen? Versuchen Sie es gerne, sie können mit so einer Übung nur gewinnen!
Nur wenn Sie ganz wissensdurstig sind, hier Klicken …
= Time.FromText(Text.End("00000" & Text.From([Zeit]), 6))Wie gesagt, ein kleines Experiment. Und ich überlasse es Ihnen, die Sinnhaftigkeit, den Hintergrund zu erkunden. So viel kann ich Ihnen hier aber schon verraten: Solch eine Formel werde ich selten in der Realität einsetzen, für Anwender im Lern-Stadium ist sie aus meiner Sicht nicht transparent genug. Aber als Option biete ich das meinen Kunden natürlich an. Für Sie kann die Formel allerdings ein echtes Bonbon sein.
Und noch ein Hinweis: Die Funktionen, welche hier in der Sprache M verwendet worden sind, können Sie hier direkt an der Quelle nachlesen. Und wenn Sie Google Chrome als Browser verwenden, dann wird der englische Text auch recht ordentlich übersetzt. Auch wenn das Wort „table” ab und zu einmal zu „Tisch” statt „Tabelle” übersetzt wird, wer der englischen Sprache nicht so ganz mächtig ist, kann hier eine gute Hilfestellung erfahren.
Den ersten Teil der Trilogie können Sie hier ansehen und den dritten Teil finden Sie hier im Blog.