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

  Wissensstand: Level 1 ⇒ Einsteiger in PQ, keine/kaum Erfahrung   

In einem Forum (Herber) wurde unter dem Titel „Matrix umwandeln und erweitern“ eine Frage aufgeworfen, die durchaus auch mit Formeln ganz gut lösbar ist und dort auch auf diese Weise zum Erfolg geführt wurde. Die Ursprungsdaten liegen als Kreuztabelle vor, wo noch eine (zu korrigierende) Besonderheit integriert ist. Da in diesem Forum die Beiträge nur kurze Zeit im „aktiven“ Teil sichtbar sind, habe ich die Eingangs-Frage (im Sachen Formatierung leicht modifiziert)  hier als Zitat eingefügt und natürlich auch das ursprüngliche Excel-File:

Betrifft: Matrix umwandeln und erweitern
von: Andreas
Geschrieben am: 18.05.2019 11:47:37

Hallo Forum,
ich verwende täglich eine Matrix in Excel.

In der ersten Spalte ist ein Datum hinterlegt, in Zeile 1 eine Uhrzeit. In den Schnittpunkten stehen Werte.

Ich muss diese Matrix(Quelle) nun in eine neue Zielmatrix überführen:
In Spalte 1 das Datum mit jeweils einer Uhrzeit aus Zeile 1 der Quelle erhöht.
In Spalte 2 jeweils die zugehörigen Werte aus der Quelle.

Hier eine Beispieldatei: http://www.herber.de/bbs/user/129863.xlsx

Die jetzige Quelle besteht aus vier bis fünf Zeilen. Für diesen Fall ist die manuelle Überführung echt zeitrauben. Künftig werden es wohl bis zu 35 Zeilen!Gibt es für diese Aufgabenstellung eine VBA-Lösung?

Vielen Dank und Grüße
Andreas

Nach dem Öffnen des Files werden Sie erkennen, dass die Spalten D:E jeweils die Uhrzeit 02:00:00 Uhr anzeigen. Das ist jedoch nicht ganz korrekt, denn in der Spalte D steht tatsächlich 02:00:00, während in Spalte E der Eintrag 02:00:002 zu finden ist und nur die Spaltenbreite dafür sorgt, dass die angehängte 2 nicht sichtbar ist. 🙄 Auf Nachfrage eines Helfers, warum die beiden Spalten denn identisch seien erklärte der Fragesteller, dass die zweite 2:00 Uhr-Spalte nur an einem Tag im Jahr gebraucht wird: Bei der Umstellung von der Sommer- auf die Winterzeit, wo die Uhrzeit 2:00 Uhr ja doppelt vorkommt. Und die dort eingetragenen Werte bzw. die kompletten Zeilen werden derzeit von Hand an den nicht relevanten Tagen gelöscht.

Wie auch immer, diese Schreibweise ist nicht in eine normale Uhrzeit unwandelbar. Das Ergebnis eines Versuchs wäre ein Fehler-Wert. Darum ist es auch hilfreich, gleich nach dem Import in den Power Query-Editor die Überschrift anzupassen. Weiter unten dazu mehr.


Falls Sie es noch nicht getan haben, laden Sie von unserem Server nun die Datei mit den erfassten Daten. Wie eigentlich immer werden sie den Bereich der gegebenen Kreuztabelle (A3:Z7) als Intelligente Tabelle mit vorhandenen Überschriften formatieren und anschließend in den Power Query Abfrage-Editor importieren. Der Editor stellt sich danach so dar:

Die Datenlage nach dem Import

Im ersten Schritt ändere ich die Überschrift 02:00:002 dergestalt, dass ich vor der letzten 2 ein Komma einfüge: 02:00:00,2; damit ist gewährleistet, dass es sich um eine Zeichenfolge handelt, welche problemlos in eine Uhrzeit umgewandelt werden kann. Die Ziffer nach dem Komma entspricht übrigens tausendstel Sekunden. Gleich im Anschluss markiere ich Spalte1 (Klick in die Überschrift) und ändere den Datentyp von Datum/Uhrzeit zu (nur) Datum.

Jetzt ein Rechtsklick in Spalte1 und im Kontextmenü wählen Sie Andere Spalten entpivotieren. Danach stellt sich das ganze so dar:

Nach dem Entpivotieren ist fast schon das Ziel erreicht

Sie erkennen hier beispielsweise in Zeile 4, dass in der Spalte Attribut der Wert korrekt in der geänderten Notation gespeichert ist. Und sie sehen auch, dass es eigentlich schon das gewünschte Ergebnis ist. Das einzige, was noch zu tun ist: Das Datum und die Uhrzeit (Spalte1 und Attribut) zu einer einzigen Spalte zu verbinden. Dazu klicken Sie erst einmal in die Überschrift Attribut und ändern den Datentyp über das Menü oder über einen Rechtsklick in Zeit. Nun zuerst ein Klick in Spalte 1, Shift und Attribut. Aktivieren Sie nun das Register Transformieren und in der Gruppe Datums-& Uhrzeitspalten ein Klick auf Datum. Hier ist nun nur ein einziger Eintrag auswählbar: Datum und Uhrzeit Kombinieren. Ein Klick darauf bringt den gewünschten Erfolg. 💡 

Jetzt in der Zielgeraden werden Sie die Überschrift Zusammengeführt naturgemäß zu Datum ändern. Nun Datei | Schließen & laden in… und wählen Sie im Dialog das Optionsfeld Bestehendes Arbeitsblatt und anschließend im nun aktivierten Textfeld darunter jene Adresse, wo die Liste gespeichert werden soll. Ich habe mit dazu D17 ausgesucht, also neben dem Muster-Wunschergebnis. Dazu genügt ein Klick im nun sichtbaren Excel-Arbeitsblatt auf die Position.


Ihnen wird auffallen, dass im Abfrage-Editor Datum und Uhrzeit in der einen Spalte ganz prima angezeigt wurden und hier in der Excel Tabelle nur die numerischen Werte sichtbar sind. Natürlich könnten Sie hier im Arbeitsblatt das Format der entsprechenden Spalte ändern. Das ist aber nicht wirklich zielführend, denn Probleme sollten an der Wurzel bekämpft werden. Darum gehen Sie diesen Weg:

  • Im rechten Seitenfenster ein Doppelklick auf die einzige Abfrage Tabelle1
  • Im nun wieder geöffneten Editor Klick in die Überschrift Datum
  • Ändern Sie den Datentyp von Beliebig zu Datum/Uhrzeit
  • Schließen & laden.

… und Jetzt kann auch der Chef (oder die Chefin) zufrieden sein. 😉 Das einzige, was vielleicht noch irritiert: Obwohl in der Spalte Wert bereits im Power Query-Editor der Datentyp: Dezimalzahl vermerkt ist, zeigt Excel in der Ergebnis-Tabelle die Zahlen ohne die beiden gewollten Nachkommastellen. Das ist aber gewiss eine der leichtesten Übungen und kann dauerhaft leicht angepasst werden. Ändern Sie also diese Spalte in Sachen Formatierung hier im Arbeitsblatt so, dass 2 Nachkommastellen sichtbar sind. Aktivieren Sie nun im Menü-Register Tabellentools | Entwurf und klicken dort im Menüband auf Eigenschaften:

Ihr Register und Menüband wird vielleicht etwas reduziert sein… 😉

Es öffnet sich ein Dialog und hier sollte das Kästchen bei Zellformatierung beibehalten markiert werden, sofern es noch nicht so ist:

Dieses Häkchen wird Ihren Wunsch erfüllen

Anschließend wird auch noch Aktualisierung der Quelldaten und der entsprechenden Abfrage diese Spalte in dieser Tabelle im voreingestellten Format bleiben, hier also mit den 2 Nachkommastellen.


Die Angelegenheit mit den doppelten 2:00 Uhr-Werten habe ich in der Lösung ganz bewusst nicht mit angesprochen. Der wohl praktikabelste Weg wäre, in den Quelldaten alle Werte ab E4 komplett zu löschen; einzige Ausnahme ist natürlich der Tag der Zeitumstellung zur Winterzeit. Naturgemäß ist auch ein Weg in Power Query machbar, aber das überlasse ich Ihrer Experimentierfreude. 😎

▲ nach oben …

Dieser Beitrag wurde unter Allgemein, Verschiedenes, Datentyp anpassen, Datum & Zeit, Foren-Q&A, Power Query, PQ-Quickies, Transponieren abgelegt und mit , , , , , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.