!ZEIT! – Zeiteingaben ohne Trennzeichen (3)

073827 in 07:38:27 als Zeit umwandeln Teil 3

Den ersten Teil dieser Trilogie können Sie hier lesen.
Den zweiten Teil dieser Trilogie können Sie hier lesen.

Die „Ochsentour“  😎 

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

Ich nutze die Gelegenheit, Ihnen auch eine Methode vorzustellen, die speziell in diesem Falle gewiss nicht anwenden werde aber in anderen Situationen durchaus hilfreich sein kann. Insbesondere dann, wenn Sie in einzelnen Spaltenwerte für Stunden, Minuten Sekunden vorliegen haben und diese dann zu einem Feld mit dem Inhalt der Zeit kombinieren sollen.

Ausgangslage ist beispielsweise wieder die Datei Zeiten_1.xlsx  aus der gepackten Datei Zeiten-Sammler.zip, idealerweise erst einmal Tabelle1. Es handelt sich hier ja schon um Text-Daten mit 4-stelligen Ziffernfolgen, also im hhmm-Format. Darum können Sie diese direkt in den Editor importieren. Die 2. Zeile bei Angewendete Schritte (Geänderter Typ) löschen und auch in der Abfrage sind dann die 4-stelligen Ziffernfolgen. Der Datentyp steht nun nicht mehr auf Ganze Zahl sondern auf Beliebig. Um unerwartete Nebeneffekte zu vermeiden ändere ich diesen auf Text. Jetzt teilen Sie die Spalte nach Anzahl von Zeichen, hier natürlich 2.

In der Spalte Zeit.1 steht nun der numerische Wert für die Stunden, in Zeit.2 ist es der Wert für die Minuten. Beide Spalten sind automatisch als Ganze Zahl formatiert. Natürlich führen auch hier mehrere Wege zum Ziel (wie könnte es anders sein?), ich beginne mit dem Äquivalent zur Excel-Funktion ZEIT(). Register Spalte hinzufügen | Benutzerdefinierte Spalte und als Neuer Spaltenname schreiben Sie beispielsweise Zeit. Als Formel geben Sie ein:
= #time([Zeit.1], [Zeit.2], 0)

Prinzipiell ist damit das Ziel erreicht. Die beiden ersten Spalten löschen Sie und der Spalte Zeit werden Sie präventiv den Datentyp Zeit zuweisen, dann ist es geschafft. Naturgemäß ist nun der Zeitpunkt gekommen, diese Abfrage mit Schließen & laden zu „verewigen“.


Im Blatt Tabelle2 würden sie naturgemäß gleichermaßen vorgehen; die Ausnahme ist natürlich, dass sie als 3. Argument bei dem Menüpunkt Benutzerdefinierte Spalte keine 0 eingeben, sondern die Spalte [Zeit.3] aus der Auflistung Verfügbare Spalten übernehmen:
#time([Zeit.1], [Zeit.2], [Zeit.3])
… und dass Sie dann die ersten 3 Spalten löschen, bedarf keiner Erwähnung.

Nun aber „Kommando zurück“, denn ich wollte Ihnen ja einen weiteren Weg aufzeigen, darum werden sie dieses Tabellenblatt ganz normal importieren und wiederum den Angewendeten Schritt Geänderter Typ löschen. Es bleibt bei der einzigen Spalte Zeit, also nicht splitten. Und jetzt bauen wir doch einfach einmal die Excel-Funktion bzw.-Formel
=LINKS(A2; 2) & „:“ & TEIL(A2; 3; 2) & „:“ & RECHTS(A2; 2)
in der Power Query Abfrage-Sprache M nach; allerdings wie vorher schon einmal in einem Rutsch jedoch mit anschließenden detaillierten Erklärungen, wo es hilfreich ist. Spalte hinzufügen | Benutzerdefinierte Spalte und diese Formel: 
= Text.Start([Zeit], 2) & ":" & Text.Middle([Zeit] ,2, 2) & ":" & Text.End([Zeit], 2)

Ich habe hier der Übersichtlichkeit wegen einige Leerzeichen in die Formel eingefügt, normalerweise schreibe ich in einem Rutsch ohne (überflüssige) Leerstellen durch. – Das Ergebnis kann sich auch schon sehen lassen, der Mensch erkennt darin ganz klar eine Uhrzeit. Nur Power Query „natürlich“ nicht, denn der Datentyp ist hier Beliebig.

Schauen Sie sich jetzt noch einmal die Ergebnisse genau an und überprüfen Sie, ob wirklich alles richtig ist. Und nun eine ganz gemeine Frage: Warum steht im Formel-Teil für die Minute Text.Middle([Zeit],2,2) und nicht Text.Middle([Zeit],3,2)? Schließlich soll doch beginnend mit dem 3. Zeichen die Länge von 2 Zeichen extrahiert werden …

Ob die Antworten einfach ist, kann ich nicht beurteilen; aber sie ist aus meiner Sicht logisch. In Power Query beginnt so gut wie alles, was hochgezählt wird, mit der Zahl 0. Das beste Beispiel dafür ist der Index, der auch automatisch 0-basiert ist, solange sie keine andere Vorgabe machen. Das ist zwar gewöhnungsbedürftig, wenn sie nicht aus der Programmiererei kommen, aber „man“ gewöhnt sich dran. 🙄 

Natürlich könnten Sie jetzt per Mausklick den Datentyp Zeit zuweisen, aber wir bewegen uns ja derzeit auf der M-Schiene und darum zeige ich Ihnen auf, wie sie so etwas von vornherein ausschließlich per Funktion lösen können:
Time.From(Text.Start([Zeit], 2) & ":" & Text.Middle([Zeit],2,2) & ":" & Text.End([Zeit], 2))

Also, falls Sie (wahrscheinlich) dem ersten Vorschlag gefolgt sind, werden Sie am besten bei Angewendete Schritte den unteren Punkt Hinzugefügte Benutzerdefinierte Spalte löschen und mit der einzigen Spalte Zeit starten. Alternativ können Sie natürlich auch eine weitere Benutzerdefinierte Spalte erstellen.

Und jetzt steht das Ergebnis auch rechts in der Spalte, was auf einen numerischen Datentyp hinweist. Zugegeben, es ist immer noch der Datentyp: Beliebig zugewiesen, aber das können Sie, müssen Sie aber nicht in jedem Fall ändern. Ich mache es eigentlich immer, der höheren Transparenz wegen und damit die in Excel erzeugte Tabelle gleich das gewünschte Zahlenformat hat.

▲ nach oben …

Spalte aus Beispielen

Wie im richtigen Leben: Das Beste kommt sehr oft zum Schluss. 😎 Darum möchte ich Ihnen auch nicht eine wirklich geniale Methode verheimlichen, die im Laufe der Zeit als Ergänzung zum Power Query integriert worden ist. Keine Ochsentour, keine einzige Zeile Formel oder Code, alles recht easy … 💡 

Status: Direkt nach dem Import der Daten aus Tabelle2, jeweils 6 Ziffern als Text. Register Spalte hinzufügen | Spalte aus Beispielen und sie klicken in den grün markierten Bereich rechts unterhalb der Überschrift Spalte1. Jetzt schreiben Sie dort die Uhrzeit in der Form, wie sie dann auch ausgegeben werden soll: 17:23:16:

Die Uhrzeit ist im rechten Bereich (Spalte1) im Ziel-Format eingegeben

Die Uhrzeit ist im rechten Bereich (Spalte1) im Ziel-Format eingegeben

Nach einem Klick in einen freien Bereich unterhalb oder Enter wird Power Query die Überschrift auf Zeit.1 ändern und Ihnen eine Vorschau bzw. Vorschläge der weiteren Ergebnisse zeigen:

Vorschau der weiteren zu berechneten Zeilen mit der Uhrzeit

Vorschau der weiteren zu berechneten Zeilen mit der Uhrzeit

Ich finde: Schicke Sache! Nicht nur, dass alle Uhrzeiten korrekt umgerechnet werden, auch der Datentyp Zeit wird der Spalte gleich mit zugewiesen. Jetzt können Sie nach einem Klick auf OK die 1. Spalte löschen und in der verbleibenden Spalte die Überschrift auf Zeit ändern. Das geht, weil nun ja keine Spalte mit dem Namen „Zeit“ mehr existiert…  😉 

Auch wenn es noch weitere Möglichkeiten mit Power Query gibt, das sind dann mehr oder weniger Exoten, die hier nicht diskutiert werden sollen. Ich bin davon überzeugt, dass Sie von den hier aufgezeigten Möglichkeiten profitieren werden und Sie gewiss den für Sie günstigen Weg finden werden.

Hinweis: Es gibt da noch so eine Datum-Zeit-Darstellung ohne jegliche Trenner, die Unix-Timestamps. Die können Sie (natürlich) auch in Power Query zu „normalen“ Zeiten komfortabel umwandeln. Mehr dazu hier im Blog.

▲ nach oben …

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

Dann würde ich mich über einen Beitrag Ihrerseits freuen …

Dieser Beitrag wurde unter Datum & Zeit, Foren-Q&A, Power Query, Text-Behandlung abgelegt und mit , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.