Wis­sens­stand: Lev­el 1 ⇒ Ein­steiger in PQ, keine/kaum Erfahrung   

Das The­ma Zeit­en und Excel ist fast schon ein Dauer­bren­ner in Foren. Eine der „Lieblings­fra­gen”: Zeit­en wer­den oder wur­den als reine Zif­fer­n­fol­gen ohne eine Tren­nung eingeben (was meis­tens der Fall ist) oder in dieser Form seit­ens eines anderen Pro­gramms so erstellt und in Excel in dieser Form importiert. – Wie kön­nen diese Werte (in diesem Fall bzw. Beitrag) durch Pow­er Query in durch Excel auswert­bar „echte” Zeit­en umge­wan­delt wer­den? Hin­weis: An dieser Stelle gibt es zum gle­ichen The­ma eine Lösung in Plain Excel.  😉 

Auch wenn das Prinzip immer gle­ich ist hängt die Vorge­hensweise doch deut­lich davon ab, in welch­er Form die Zif­fer­n­fol­gen (welche ja eine Uhrzeit darstellen und ergeben sollen) in den Basis­dat­en vor­liegen. Haupt­säch­liche Unter­schei­dungsmerk­male sind:

• Text oder Zahl,
• immer die gle­iche Anzahl von Zif­fern oder nicht,
• mit oder ohne Sekun­den.

▲ nach oben …

Als Text, immer gleiche Länge

In dieser *.zip-gepack­ten Datei find­en Sie 2 Arbeitsmap­pen und 2 csv-Dateien:
Zeiten_1.xlsx und Zeiten_1a.csv sowie Zeiten_1b.csv. Das File Zeiten_2.xlsx kommt später zum tra­gen. Es sind jew­eils nur wenige Zeilen (sprich Dat­en), denn es geht hier nur um das Prinzip und nicht um große Men­gen. Grund­sät­zlich wer­den Sie mir zus­tim­men, dass bei der Vor­gabe „immer gle­iche Anzahl von Zif­fern” die For­matierung als Text zwin­gend erforder­lich ist. Son­st wür­den ja Zeit­en vor 10:00 Uhr immer eine Stelle weniger haben, da beim Zahlen­for­mat Zahl eine führende Null seit­ens Excel ja automa­tisch ent­fer­nt wird. Bei ein­er csv-Datei lässt sich da im Pow­er Query-Umfeld gewiss noch etwas ret­ten, aber für „saubere” Arbeit sind hier die Werte auch in Gänse­füßchen einge­fasst und somit ein­deutig als Text deklar­i­ert.

Öff­nen Sie nach dem ent­pack­en im ersten Schritt das File Zeiten_1.xlsx und sie wer­den erken­nen, dass es 2 Arbeits­blät­ter gibt; Tabelle1 mit 4‑stelligen „Zeit­en”, Tabelle2 mit 6‑stelligen Zei­tangaben (also mit Sekun­den). Es sollte sie auch nicht wun­dern, dass die Zahlen rechts­bündig aus­gerichtet sind. Das Zahlen­for­mat wird Ihnen aber den deut­lichen Hin­weis geben, dass es sich hier­bei (den­noch) um Text han­delt. Zugegeben, eine kleine Gemein­heit von mir aber in der Prax­is öfter ein­mal zu find­en. 🙄 Ver­lassen Sie sich also nicht in jedem Fall auf die Aus­rich­tung der dargestell­ten Dat­en, mitunter täuscht der Schein.

Begin­nen Sie mit Tabelle1. Fol­gende Werte sollen im End­ef­fekt kor­rekt dargestellt wer­den: 17:23 Uhr, 09:15 Uhr, 23:59 Uhr, 18:00 Uhr 06:00 Uhr und 00:00 Uhr. Selb­stre­dend ohne den Text „Uhr”. Und das ganze soll natür­lich nicht in Plain Excel son­dern in Pow­er Query geschehen. Also wer­den sie im ersten Schritt die Dat­en als Tabelle for­matieren und dann in Pow­er Query importieren. Aber Pow­er Query „denkt mit” und passt die Dat­en so an, dass sie schein­bar opti­mal sind:

Die Texte wur­den automa­tisch zu Zahlen umfor­matiert

Im recht­en Seit­en­fen­ster erken­nen sie bei Angewen­dete Schritte als 2. Ein­trag Geän­dert­er Typ. Löschen Sie ein­fach diese Zeile, beispiel­sweise durch einen Klick auf das vor­angestellte rote . Als Effekt ste­ht dann in der Spalte Zeit in jed­er Zeile ein 4‑stelliger, aus Zif­fern beste­hen­der links­bündi­ger Text:

4‑stelliger Text

Alter­na­tiv kön­nen Sie sofort nach dem Import, also vor jedem weit­eren Schritt, den Daten­typ der Spalte auf Text ändern. Ich werde Ihnen in diesem Beitrag diverse Möglichkeit­en vorstellen, wie sie zum gewün­scht­en Ziel gelan­gen kön­nen. Die Rei­hen­folge stellt keine Wer­tung dar. Wichtig ist nur, dass ihnen der Weg gefällt und sie ihn nachvol­lziehen kön­nen und auch wollen.

▲ nach oben …

Der einfache Weg: Spalte teilen

Mit recht ein­fachen Mit­teln und nur eini­gen Mausklicks kön­nen Sie in Pow­er Query zum Ziel gelan­gen. Vol­lziehen Sie nacheinan­der diese Schritte:

• Klick­en Sie nach dem Import in die Über­schrift Zeit.
• Start | Spalte teilen | Nach Anzahl von Zeichen.
• Geben Sie in das Feld Anzahl von Zeichen die Zahl 2 ein und belassen Sie es bei Wieder­holt.
• Bestäti­gen Sie mit OK.
• Markieren Sie bei­de der nun getren­nten Spal­ten durch einen Klick in die Über­schriften (Shift oder Strg ver­wen­den).
• Recht­sklick in eine der bei­den Über­schriften, Typ ändern | Text.
• Bestäti­gen Sie, dass alle Werte geän­dert wer­den.
• Reg­is­ter Trans­formieren | (Gruppe Textspalte) Spal­ten zusam­men­führen.
• Leg­en Sie als Trennze­ichen den Dop­pelpunkt : fest.
• Geben Sie bei Neuer Spal­tenname den Text  Zeit ein.
• Schließen Sie das Fen­ster mit OK.
• Geben Sie dieser Spalte den Daten­typ:  Zeit.
• Schließen & laden oder Schließen & laden in… und die Dat­en wer­den entwed­er in einem neuen Blatt oder an gewün­schter Posi­tion in ein Excel Tabel­len­blatt geschrieben.

Lassen Sie sich nicht dadurch irri­tieren, dass nun automa­tisch die Sekun­den mit dem jew­eili­gen Wert :00 ange­hängt wer­den, das ist bei Pow­er Query nun ein­mal so. In jedem Falle ist die Auf­gabe für Tabelle1 damit erfüllt. In Tabelle2 ist das Vorge­hen abso­lut iden­tisch. Nur wer­den Sie hier naturgemäß alle 3 durch die Tren­nung erzeugten Spal­ten markieren und dann durch den Dop­pelpunkt zusam­men­führen. Natür­lich ste­ht es Ihnen frei, in Excel die gener­ierten Dat­en nach ihrem Geschmack zu for­matieren.

▲ nach oben …

csv-Dateien

Die entsprechende csv-Datei wer­den sie naturgemäß nicht (per Dop­pelk­lick oder ähn­lich) öff­nen, weil Excel unter den gegebe­nen Umstän­den damit ein­fach nicht fachgerecht umge­ht. Sie wer­den entwed­er ein neues Blatt in der bish­eri­gen Mappe ver­wen­den oder vorzugsweise eine neue Mappe erstellen. Anschließend über den Menüpunkt Dat­en (ab Ver­sion ²⁰¹⁶⁄₃₆₅) oder Pow­er Query (2010/2013) Neue Abfrage | Aus Datei | Aus csv und Sie wählen ver­mut­lich zuerst Zeiten_1.csv. Sie erken­nen, dass Pow­er Query wieder „mit­denkt”. Obwohl die Anführungsze­ichen in der Orig­i­nal-Datei die Werte ganz klar als Text ausweisen, sind das nach dem Import ein­deutig Zahlen. Aber immer­hin hat PQ erkan­nt, dass die 1. Zeile die Über­schrift enthält. 😛 

Es wird Sie nicht erstaunen, dass das Löschen des let­zten Ein­trages bei Angewen­dete Schritte den gewün­scht­en Erfolg zeigt. Nun ste­ht in jed­er Zelle wieder eine 4‑stellige Zif­fer­n­folge, wie es bere­its in der Excel-Datei der Fall war. Und aus diesem Grunde ist die Vorge­hensweise hier ab dem Punkt abso­lut iden­tisch zu der zuvor eingeübten Art und Weise.

Dieses war eine kleine Leseprobe, ein Appetithap­pen, ein Ein­stieg. Sie haben gese­hen, dass es mit Pow­er Query dur­chaus möglich ist, eine Zif­fer­n­folge in eine für Excel les­bare Zeit umzuwan­deln. Aber bekan­nter­maßen führen viele Wege nach Rom, sprich zum gewün­scht­en Ziel. Mehr zum The­ma in weit­eren 2 Beiträ­gen.

▲ nach oben …

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