Den ersten Teil dieser Trilo­gie kön­nen Sie hier lesen.
Den zweit­en Teil dieser Trilo­gie kön­nen Sie hier lesen.

Die „Ochsentour”  😎 

  Wis­sens­stand: Lev­el 2 ⇒ Solides Basiswis­sen in PQ, etwas Erfahrung   

Ich nutze die Gele­gen­heit, Ihnen auch eine Meth­ode vorzustellen, die speziell in diesem Falle gewiss nicht anwen­den werde aber in anderen Sit­u­a­tio­nen dur­chaus hil­fre­ich sein kann. Ins­beson­dere dann, wenn Sie in einzel­nen Spal­tenwerte für Stun­den, Minuten Sekun­den vor­liegen haben und diese dann zu einem Feld mit dem Inhalt der Zeit kom­binieren sollen.

Aus­gangslage ist beispiel­sweise wieder die Datei Zeiten_1.xlsx  aus der gepack­ten Datei Zeiten-Sammler.zip, ide­al­er­weise erst ein­mal Tabelle1. Es han­delt sich hier ja schon um Text-Dat­en mit 4‑stelligen Zif­fer­n­fol­gen, also im hhmm-For­mat. Darum kön­nen Sie diese direkt in den Edi­tor importieren. Die 2. Zeile bei Angewen­dete Schritte (Geän­dert­er Typ) löschen und auch in der Abfrage sind dann die 4‑stelligen Zif­fer­n­fol­gen. Der Daten­typ ste­ht nun nicht mehr auf Ganze Zahl son­dern auf Beliebig. Um uner­wartete Neben­ef­fek­te zu ver­mei­den ändere ich diesen auf Text. Jet­zt teilen Sie die Spalte nach Anzahl von Zeichen, hier natür­lich 2.

In der Spalte Zeit.1 ste­ht nun der numerische Wert für die Stun­den, in Zeit.2 ist es der Wert für die Minuten. Bei­de Spal­ten sind automa­tisch als Ganze Zahl for­matiert. Natür­lich führen auch hier mehrere Wege zum Ziel (wie kön­nte es anders sein?), ich beginne mit dem Äquiv­a­lent zur Excel-Funk­tion ZEIT(). Reg­is­ter Spalte hinzufü­gen | Benutzerdefinierte Spalte und als Neuer Spal­tenname schreiben Sie beispiel­sweise Zeit. Als Formel geben Sie ein:
= #time([Zeit.1], [Zeit.2], 0)

Prinzip­iell ist damit das Ziel erre­icht. Die bei­den ersten Spal­ten löschen Sie und der Spalte Zeit wer­den Sie präven­tiv den Daten­typ Zeit zuweisen, dann ist es geschafft. Naturgemäß ist nun der Zeit­punkt gekom­men, diese Abfrage mit Schließen & laden zu „verewigen”.

Im Blatt Tabelle2 wür­den sie naturgemäß gle­icher­maßen vorge­hen; die Aus­nahme ist natür­lich, dass sie als 3. Argu­ment bei dem Menüpunkt Benutzerdefinierte Spalte keine 0 eingeben, son­dern die Spalte [Zeit.3] aus der Auflis­tung Ver­füg­bare Spal­ten übernehmen:
#time([Zeit.1], [Zeit.2], [Zeit.3])
… und dass Sie dann die ersten 3 Spal­ten löschen, bedarf kein­er Erwäh­nung.

Nun aber „Kom­man­do zurück”, denn ich wollte Ihnen ja einen weit­eren Weg aufzeigen, darum wer­den sie dieses Tabel­len­blatt ganz nor­mal importieren und wiederum den Angewen­de­ten Schritt Geän­dert­er Typ löschen. Es bleibt bei der einzi­gen Spalte Zeit, also nicht split­ten. Und jet­zt bauen wir doch ein­fach ein­mal die Excel-Funk­tion bzw.-Formel
=LINKS(A2; 2) & „:” & TEIL(A2; 3; 2) & „:” & RECHTS(A2; 2)
in der Pow­er Query Abfrage-Sprache M nach; allerd­ings wie vorher schon ein­mal in einem Rutsch jedoch mit anschließen­den detail­lierten Erk­lärun­gen, wo es hil­fre­ich 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 Über­sichtlichkeit wegen einige Leerze­ichen in die Formel einge­fügt, nor­maler­weise schreibe ich in einem Rutsch ohne (über­flüs­sige) Leer­stellen durch. – Das Ergeb­nis kann sich auch schon sehen lassen, der Men­sch erken­nt darin ganz klar eine Uhrzeit. Nur Pow­er Query „natür­lich” nicht, denn der Daten­typ ist hier Beliebig.

Schauen Sie sich jet­zt noch ein­mal die Ergeb­nisse genau an und über­prüfen Sie, ob wirk­lich alles richtig ist. Und nun eine ganz gemeine Frage: Warum ste­ht im Formel-Teil für die Minute Text.Middle([Zeit],2,2) und nicht Text.Middle([Zeit],3,2)? Schließlich soll doch begin­nend mit dem 3. Zeichen die Länge von 2 Zeichen extrahiert wer­den …

Ob die Antworten ein­fach ist, kann ich nicht beurteilen; aber sie ist aus mein­er Sicht logisch. In Pow­er Query begin­nt so gut wie alles, was hochgezählt wird, mit der Zahl 0. Das beste Beispiel dafür ist der Index, der auch automa­tisch 0‑basiert ist, solange sie keine andere Vor­gabe machen. Das ist zwar gewöh­nungs­bedürftig, wenn sie nicht aus der Pro­gram­mier­erei kom­men, aber „man” gewöh­nt sich dran. 🙄 

Natür­lich kön­nten Sie jet­zt per Mausklick den Daten­typ Zeit zuweisen, aber wir bewe­gen uns ja derzeit auf der M‑Schiene und darum zeige ich Ihnen auf, wie sie so etwas von vorn­here­in auss­chließlich per Funk­tion lösen kön­nen:
Time.From(Text.Start([Zeit], 2) & ":" & Text.Middle([Zeit],2,2) & ":" & Text.End([Zeit], 2))

Also, falls Sie (wahrschein­lich) dem ersten Vorschlag gefol­gt sind, wer­den Sie am besten bei Angewen­dete Schritte den unteren Punkt Hinzuge­fügte Benutzerdefinierte Spalte löschen und mit der einzi­gen Spalte Zeit starten. Alter­na­tiv kön­nen Sie natür­lich auch eine weit­ere Benutzerdefinierte Spalte erstellen.

Und jet­zt ste­ht das Ergeb­nis auch rechts in der Spalte, was auf einen numerischen Daten­typ hin­weist. Zugegeben, es ist immer noch der Daten­typ: Beliebig zugewiesen, aber das kön­nen Sie, müssen Sie aber nicht in jedem Fall ändern. Ich mache es eigentlich immer, der höheren Trans­parenz wegen und damit die in Excel erzeugte Tabelle gle­ich das gewün­schte Zahlen­for­mat hat.

▲ nach oben …

Spalte aus Beispielen

Wie im richti­gen Leben: Das Beste kommt sehr oft zum Schluss. 😎 Darum möchte ich Ihnen auch nicht eine wirk­lich geniale Meth­ode ver­heim­lichen, die im Laufe der Zeit als Ergänzung zum Pow­er Query inte­gri­ert wor­den ist. Keine Ochsen­tour, keine einzige Zeile Formel oder Code, alles recht easy … 💡 

Sta­tus: Direkt nach dem Import der Dat­en aus Tabelle2, jew­eils 6 Zif­fern als Text. Reg­is­ter Spalte hinzufü­gen | Spalte aus Beispie­len und sie Klick­en in den grün markierten Bere­ich rechts unter­halb der Über­schrift Spalte1. Jet­zt schreiben Sie dort die Uhrzeit in der Form, wie sie dann auch aus­gegeben wer­den soll: 17:23:16:

Die Uhrzeit ist im recht­en Bere­ich (Spalte1) im Ziel-For­mat eingegeben

Nach einem Klick in einen freien Bere­ich unter­halb oder Enter wird Pow­er Query die Über­schrift auf Zeit.1 ändern und Ihnen eine Vorschau bzw. Vorschläge der weit­eren Ergeb­nisse zeigen:

Vorschau der weit­eren zu berech­neten Zeilen mit der Uhrzeit

Ich finde: Schicke Sache! Nicht nur, dass alle Uhrzeit­en kor­rekt umgerech­net wer­den, auch der Daten­typ Zeit wird der Spalte gle­ich mit zugewiesen. Jet­zt kön­nen Sie nach einem Klick auf OK die 1. Spalte löschen und in der verbleiben­den Spalte die Über­schrift auf Zeit ändern. Das geht, weil nun ja keine Spalte mit dem Namen „Zeit” mehr existiert…  😉 

Auch wenn es noch weit­ere Möglichkeit­en mit Pow­er Query gibt, das sind dann mehr oder weniger Exoten, die hier nicht disku­tiert wer­den sollen. Ich bin davon überzeugt, dass Sie von den hier aufgezeigten Möglichkeit­en prof­i­tieren wer­den und Sie gewiss den für Sie gün­sti­gen Weg find­en wer­den.

Hin­weis: Es gibt da noch so eine Datum-Zeit-Darstel­lung ohne jegliche Tren­ner, die Unix-Time­stamps. Die kön­nen Sie (natür­lich) auch in Pow­er Query zu „nor­malen” Zeit­en kom­fort­a­bel umwan­deln. Mehr dazu hier im Blog. – Und den ersten Teil dieser Trilo­gie find­en Sie hier, den zweit­en Teil hier im Blog.

▲ nach oben …

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

Dann würde ich mich über einen Beitrag Ihrer­seits freuen …