PQ: Kreuztabelle (Datum | Zeit, numerische Werte) zu Liste

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

In einem Forum (Her­ber) wurde unter dem Titel „Matrix umwan­deln und erweit­ern” eine Frage aufge­wor­fen, die dur­chaus auch mit Formeln ganz gut lös­bar ist und dort auch auf diese Weise zum Erfolg geführt wurde. Die Ursprungs­dat­en liegen als Kreuzta­belle vor, wo noch eine (zu kor­rigierende) Beson­der­heit inte­gri­ert ist. Da in diesem Forum die Beiträge nur kurze Zeit im „aktiv­en” Teil sicht­bar sind, habe ich die Ein­gangs-Frage (im Sachen For­matierung leicht mod­i­fiziert)  hier als Zitat einge­fügt und natür­lich auch das ursprüngliche Excel-File:

Nach dem Öff­nen des Files wer­den Sie erken­nen, dass die Spal­ten D:E jew­eils die Uhrzeit 02:00:00 Uhr anzeigen. Das ist jedoch nicht ganz kor­rekt, denn in der Spalte D ste­ht tat­säch­lich 02:00:00, während in Spalte E der Ein­trag 02:00:002 zu find­en ist und nur die Spal­tenbre­ite dafür sorgt, dass die ange­hängte 2 nicht sicht­bar ist. 🙄 Auf Nach­frage eines Helfers, warum die bei­den Spal­ten denn iden­tisch seien erk­lärte der Fragesteller, dass die zweite 2:00 Uhr-Spalte nur an einem Tag im Jahr gebraucht wird: Bei der Umstel­lung von der Som­mer- auf die Win­terzeit, wo die Uhrzeit 2:00 Uhr ja dop­pelt vorkommt. Und die dort einge­tra­ge­nen Werte bzw. die kom­plet­ten Zeilen wer­den derzeit von Hand an den nicht rel­e­van­ten Tagen gelöscht.

Wie auch immer, diese Schreib­weise ist nicht in eine nor­male Uhrzeit unwan­del­bar. Das Ergeb­nis eines Ver­suchs wäre ein Fehler-Wert. Darum ist es auch hil­fre­ich, gle­ich nach dem Import in den Pow­er Query-Edi­tor die Über­schrift anzu­passen. Weit­er unten dazu mehr.

Falls Sie es noch nicht getan haben, laden Sie von unserem Serv­er nun die Datei mit den erfassten Dat­en. Wie eigentlich immer wer­den sie den Bere­ich der gegebe­nen Kreuzta­belle (A3:Z7) als Intel­li­gente Tabelle mit vorhan­de­nen Über­schriften for­matieren und anschließend in den Pow­er Query Abfrage-Edi­tor importieren. Der Edi­tor stellt sich danach so dar:

Die Daten­lage nach dem Import

Im ersten Schritt ändere ich die Über­schrift 02:00:002 dergestalt, dass ich vor der let­zten 2 ein Kom­ma ein­füge: 02:00:00,2; damit ist gewährleis­tet, dass es sich um eine Zeichen­folge han­delt, welche prob­lem­los in eine Uhrzeit umge­wan­delt wer­den kann. Die Zif­fer nach dem Kom­ma entspricht übri­gens tausend­s­tel Sekun­den. Gle­ich im Anschluss markiere ich Spalte1 (Klick in die Über­schrift) und ändere den Daten­typ von Datum/Uhrzeit zu (nur) Datum.

Jet­zt ein Recht­sklick in Spalte1 und im Kon­textmenü wählen Sie Andere Spal­ten ent­piv­otieren. Danach stellt sich das ganze so dar:

Nach dem Ent­piv­otieren ist fast schon das Ziel erre­icht

Sie erken­nen hier beispiel­sweise in Zeile 4, dass in der Spalte Attrib­ut der Wert kor­rekt in der geän­derten Nota­tion gespe­ichert ist. Und sie sehen auch, dass es eigentlich schon das gewün­schte Ergeb­nis ist. Das einzige, was noch zu tun ist: Das Datum und die Uhrzeit (Spalte1 und Attrib­ut) zu ein­er einzi­gen Spalte zu verbinden. Dazu Klick­en Sie erst ein­mal in die Über­schrift Attrib­ut und ändern den Daten­typ über das Menü oder über einen Recht­sklick in Zeit. Nun zuerst ein Klick in Spalte 1, Shift und Attrib­ut. Aktivieren Sie nun das Reg­is­ter Trans­formieren und in der Gruppe Datums-& Uhrzeitspal­ten ein Klick auf Datum. Hier ist nun nur ein einziger Ein­trag auswählbar: Datum und Uhrzeit Kom­binieren. Ein Klick darauf bringt den gewün­scht­en Erfolg. 💡 

Jet­zt in der Ziel­ger­aden wer­den Sie die Über­schrift Zusam­menge­führt naturgemäß zu Datum ändern. Nun Datei | Schließen & laden in… und wählen Sie im Dia­log das Options­feld Beste­hen­des Arbeits­blatt und anschließend im nun aktivierten Textfeld darunter jene Adresse, wo die Liste gespe­ichert wer­den soll. Ich habe mit dazu D17 aus­ge­sucht, also neben dem Muster-Wun­schergeb­nis. Dazu genügt ein Klick im nun sicht­baren Excel-Arbeits­blatt auf die Posi­tion.

Ihnen wird auf­fall­en, dass im Abfrage-Edi­tor Datum und Uhrzeit in der einen Spalte ganz pri­ma angezeigt wur­den und hier in der Excel Tabelle nur die numerischen Werte sicht­bar sind. Natür­lich kön­nten Sie hier im Arbeits­blatt das For­mat der entsprechen­den Spalte ändern. Das ist aber nicht wirk­lich zielführend, denn Prob­leme soll­ten an der Wurzel bekämpft wer­den. Darum gehen Sie diesen Weg:

• Im recht­en Seit­en­fen­ster ein Dop­pelk­lick auf die einzige Abfrage Tabelle1
• Im nun wieder geöffneten Edi­tor Klick in die Über­schrift Datum
• Ändern Sie den Daten­typ von Beliebig zu Datum/Uhrzeit
• Schließen & laden.

… und Jet­zt kann auch der Chef (oder die Chefin) zufrieden sein. 😉 Das einzige, was vielle­icht noch irri­tiert: Obwohl in der Spalte Wert bere­its im Pow­er Query-Edi­tor der Daten­typ: Dez­i­malzahl ver­merkt ist, zeigt Excel in der Ergeb­nis-Tabelle die Zahlen ohne die bei­den gewoll­ten Nachkom­mas­tellen. Das ist aber gewiss eine der leicht­esten Übun­gen und kann dauer­haft leicht angepasst wer­den. Ändern Sie also diese Spalte in Sachen For­matierung hier im Arbeits­blatt so, dass 2 Nachkom­mas­tellen sicht­bar sind. Aktivieren Sie nun im Menü-Reg­is­ter Tabel­len­tools | Entwurf und Klick­en dort im Menüband auf Eigen­schaften:

Ihr Reg­is­ter und Menüband wird vielle­icht etwas reduziert sein… 😉

Es öffnet sich ein Dia­log und hier sollte das Kästchen bei Zell­for­matierung beibehal­ten markiert wer­den, sofern es noch nicht so ist:

Dieses Häkchen wird Ihren Wun­sch erfüllen

Anschließend wird auch noch Aktu­al­isierung der Quell­dat­en und der entsprechen­den Abfrage diese Spalte in dieser Tabelle im vor­eingestell­ten For­mat bleiben, hier also mit den 2 Nachkom­mas­tellen.

Die Angele­gen­heit mit den dop­pel­ten 2:00 Uhr-Werten habe ich in der Lösung ganz bewusst nicht mit ange­sprochen. Der wohl prak­tik­a­bel­ste Weg wäre, in den Quell­dat­en alle Werte ab E4 kom­plett zu löschen; einzige Aus­nahme ist natür­lich der Tag der Zei­tum­stel­lung zur Win­terzeit. Naturgemäß ist auch ein Weg in Pow­er Query mach­bar, aber das über­lasse ich Ihrer Exper­i­men­tier­freude. 😎

▲ nach oben …

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

Dann würde ich mich über einen Beitrag (1,00€ bis 2,00€) Ihrer­seits freuen …