PQ: Auswertung im 30-Minuten-Rhythmus

Xtract: Zeitwerte (TimeStamps) von maschinenbezogenen Fehlern sollen im Halbstunden-Rhythmus gezählt werden. Hier: 2 gut nachvollziehbare Möglichkeiten.

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

Fehlermeldungen im halbstündigen Rhythmus auswerten

In dieser Beispiel-Datei sind in 3 Tabellenblättern auf einen bestimmten Zeitraum begrenzte Auszüge von Fehlerprotokollen je einer Maschine aufgelistet. Das Ziel einer Excel-Auswertung (PQ) soll sein, die Anzahl der Fehler je halbe Stunde (beginnend mit dem Start einer Stunde, also ##:00 Uhr) zu berechnen. Es sollen einerseits die Werte je Maschine als andererseits auch aller Maschinen gemeinsam errechnet werden. Für die Lösung gibt es verschiedene Möglichkeiten; angefangen bei plain Excel über VBA bis zu Power Query. Ich stelle Ihnen hier zwei von mehreren denkbaren PQ-Lösungen vor, beide sind aus meiner Sicht gut nachvollziehbar. Und bedenken Sie bitte, dass solche Listen meist sehr viel umfangreicher sind und darum VBA oder PQ aus Gründen der Performance eher in Betracht gezogen werden sollten.

Zum Einstieg habe ich aber eine etwas besser händelbare „Tabelle“ erstellt, es ist das erste Tabellenblatt mit dem Namen Zusammengefügt. Und „wie im richtigen Leben“ sind auch leere Spalten zwischen den einzelnen Listen (was ja im Sinne einer ordentlichen Datenhaltung prinzipiell nicht vorkommen sollte). Und ja, ein typischer Anwender wird die über alle Spalten gehende Überschrift als auch alle drei Datenblöcke gemeinsam markieren (von A1:E43), und genau das werden Sie für die erste Übung auch tun.

Importieren Sie nun bei bestehender Markierung A1:E43 die Daten in den Power Query-Editor. Da Excel bei diesem Vorgang den markierten Bereich in eine „Intelligente“ Tabelle formatiert wird in einem Dialog gefragt, ob die Tabelle Überschriften hat oder nicht. Ausnahmsweise ist das in diesem Fall egal, denn die eigentlichen, künftigen und dann relevanten Überschriften stehen ja sowieso in Zeile 2. – Per Default wird angegeben, dass hier keine Überschrift(en) existieren und dabei belassen Sie es auch.

In der Excel-Tabelle als auch im Power Query Editor sind automatische Spalten-Überschriften Spalte1 bis Spalte5 vergeben worden. (Ab hier beziehen sich meine Anmerkungen stets auf den PQ-Editor.) Die erste Datenzeile enthält ausschließlich in Spalte1 Daten, nämlich die ursprüngliche, zentriere Überschrift und in der zweiten Zeile stehen die eigentlichen Überschriften. Bevor irgendwelche Korrekturen an den Daten vorgenommen werden, gebe ich der Abfrage erst einmal einen „sprechenden“ Namen. Typischerweise ist dieses RohDaten oder (meistens wie auch hier) die englische Bezeichnung RawData.

Aus prinzipiellen Erwägungen versuche ich, die Performance so optimal zu gestalten wie möglich. Darum wäre im Normalfall der nächste Schritt, die beiden leeren Spalten (Spalte2 und Spalte4) zu löschen. Aus zwei Gründen tue ich das hier nicht: Erstens ist das bei deutlich mehr Spalten ziemlich aufwändig und zweitens ist das beibehalten der leeren Spalten prima für die Demonstration einer Automatik bei der weiteren Verarbeitung geeignet. Im folgenden Schritt gehen Sie im Menüband über Zeilen verringern | Zeilen entfernen und löschen nur die erste Zeile. Jetzt stehen in der ersten Daten-Zeile die eigentlichen Überschriften. Im Menüband ein Klick auf Erste Zeile als Überschriften verwenden und ein wichtiges Zwischenziel ist erreicht.

Im folgenden Schritt könnte es darum gehen, den Datentyp anzupassen. Aber muss das unbedingt schon zu diesem Zeitpunkt sein? Nein, denn es ist im Prinzip „nur“ eine Frage des Aussehens; es sollen ja mit den als Dezimalzahl dargestellten Zeiten keine speziellen Time-Operationen der Sprache M vogenommen werden. 💡

Okay, im nächsten Schritt sollen die ganzen numerischen Werte untereinander geschrieben und der jeweiligen Maschine zugeordnet werden. Also in der ersten Spalte steht dann der Name der Maschine und in der Folgespalte der numerische Wert, welcher ja die Uhrzeit spiegelt. Das Stichwort dafür heißt „Entpivotieren“. Zwei mögliche, beides relativ einfache Wege führen zum prinzipiell identischen Ziel:

  1. Sie markieren alle Spalten, entweder Transformieren | Spalten entpivotieren oder Rechtsklick in eine der Überschriften und im Kontextmenü Spalten entpivotieren wählen.
  2. Alternativ gehen Sie über Spalte hinzufügen | Indexspalte, Rechtsklick in die Spalte Index und Andere Spalten entpivotieren. Das ist übrigens meine Lieblings-Vorgehensweise, da ich bei Abfragen mit vielen Spalten diese Hin- und Herschieberei der Ansicht nicht so gerne mag. 😐 Im Anschluss lösche ich die Spalte Index gleich wieder. Aunahme: Ich lösche sie nicht, wenn ich später im Verlauf wieder die Original-Reihenfolge der Daten herstellen möchte.

Und siehe da, es sind über diesen Weg ausschließlich die Zeilen mit Werten erfasst worden, Zellen der leeren Spalten, die ja ausschließlich den Inhalt null haben, fehlen vollkommen. Und genau das meinte ich weiter oben mit dem Hinweis auf die Automatik. 😉 Und nun brauche ich auch nur in 1 Spalte (Wert) den Datentyp von Beliebig auf Zeit zu ändern und nicht in 3 (oder mehr) Spalten! Zugegeben, das sieht auch besser aus! 😎

Damit habe ich eine solide Basis für die beiden kommenden Auswertungen geschaffen. Und bis hierhin war das alles auch noch Basiswissen in Sachen PQ. Nun kommt die Überlegung zum tragen, dass jeder Uhrzeit ein Halbstundenbereich zugeordnet werden muss. Da ich ja die Zeiten vergeben habe weiß ich auch, dass der früheste Eintrag um 12:00 Uhr ist. Der erste Zeitbereich wären also die 30 Minuten von 12:00 bis 12:29. Hinweis: Wenn Sie die Spalte Wert erweitern, werden die darin enthaltenen Zeiten automatisch aufsteigend angezeigt; der oberste Wert ist also immer die este Zeitangabe für die protokollierten Fehler. Weiter geht es dann mit 12:30 bis 12:59, und so weiter. Auch wenn der erste Eintrag nicht zur vollen Stunde sein sollte, der zu verwendende Zählbereich beginnt stets um ##:00 oder ##:30. Und Sie behalten bitte im Hinterkopf, dass 2 Auswertungen erstellt werden sollen. Einerseits die Gesamtzahl der Fehler aller Maschinen im jeweiligen Halbstundenbereich und andererseits die Anzahl der Fehler jeder einzelnen Maschine im entsprechenden Zeitraum.


Viele Wege führen nach Rom. Ich zeige Ihnen hier für die Zuordnung der einzelnen Zeiten einen Weg auf, der gewiss nicht alle Möglichkeiten der Sprache M nutzt; dafür aber sind die einzelnen Schritte für Sie gewiss besser nachvollziehbar, weil sie an die Vorgehensweise in plain Excel angelehnt sind. Folgende Überlegung liegt meinen Schritten zugrunde:

  • Die Angabe der Stunde bleibt für jede Fehlermeldung in jedem Fall erhalten, sie verändert sich nicht.
  • Für die jeweilige Minute gilt, dass sie auf :00 heruntergebrochen wird, wenn sie kleiner ist als 30, ansonsten wird die Anzeige der Minute auf :30 abgerundet.

Markieren Sie dazu als Erstes die Spalte Wert, wo ja die Uhrzeiten der Fehler eingetragen sind. Spalte hinzufügen, im Menüband ein Klick auf Zeit | Stunde | Stunde. Noch einmal Wert markieren, Zeit | Minute. Nun haben Sie je eine neue Spalte, wo die dezimalen numerischen Werte der Stunde und der jeweiligen Minute eingetragen sind. Markieren Sie nun die Spalte Minute. Spalte hinzufügen | Bedingte Spalte und füllen Sie im Dialog jeweils diese Werte ein bzw. wählen Sie aus:

Neuer SpaltennameStartMinute
(Bei Wenn)
SpaltennameMinute
Operatorist kleiner als
Wert30
Ausgabe0
Sonst30

Eine visuelle Kontrolle wird Ihnen rasch zeigen, dass die Ergebnisse stimmen. Die Spalten Wert und Minute können Sie jetzt löschen. Markieren Sie nun Stunde und StartMinute (beachten Sie die Reihenfolge), Rechtsklick in eine der beiden Überschriften und Spalten zusammenführen. Als Trennzeichen wählen Sie: Doppelpunkt und bei Neuer Spaltenname (optional) schreiben Sie Zeitbereich ab hinein. Auf beliebigem Wege ändern Sie nun den Datentyp der Spalte Zeitbereich ab zum Typ Zeit. Damit ist die erste Episode der Auswertung fast abgeschlossen. Um die beiden Berechnungen „sauber“ durchzuführen, erstellen Sie 2 neue Abfragen. Sie könnten diese Abfrage zwar Duplizieren (Klick im linken Seitenfenster oder über Start | Verwalten), ich ziehe es vor, einen Verweis auf RawData zu erstellen; dadurch habe ich in dieser Query wesentlich weniger Schritte im rechten Seitenfenster sichtbar (und das hat in anderen Situationen weitere Vorteile). Bei der Gelegenheit erstelle ich gleich noch einen zweiten Verweis, es sollen ja zwei unterschiedliche Berechnungen erstellt werden.

Hinweis für Fortgeschrittene/Spezis/Cracks, … Ja, es geht deutlich „schlanker“, manch einer wird auch „eleganter“ sagen. Das Wunschergebnis steht mit einer einzigen if – Formel der Sprache M in der dritten Spalte. Das ist naturgemäß meine Vorgehensweise. 😎 Mailen Sie mich an und ich schreibe Ihnen, wie Sie gegen eine Spende von 1,00 € den Code-Schnipsel, welchen Sie in eine Benutzerdefinierte Spalte einfügen können, von mir erhalten. Dieser Euro wird dann ausschließlich für die Kosten dieses Blogs verwendt. Versprochen!

▲ nach oben …

Anzahl Fehlermeldungen aller Maschinen je Zeitraum

Wechseln Sie zu Abfrage RawData (2) und benennen Sie diese idealerweise gleich um. Ich schlage Ihnen alle Maschinen vor. Das hat zwar keinen Einfluss auf die Arbeitsweise von Power Query sorgt aber für deutlich mehr Transparenz. Löschen Sie erst einmal idealerweise die Spalte Attribut, sie wird für diese Berechnung nicht benötigt. Markieren Sie Zeitbereich ab, Rechtsklick in die Überschrift und wählen Sie Gruppieren nach… Eine kurze Analyse wird Ihnen zeigen, dass die Vorgaben genau dem entsprechen, was sie als Ergebnis erwarten. Darum ein Klick auf OK und es ist gut. 😉 Bleibt nur noch Schließen & laden oder Schließen & laden in… und im Excel-Workbook ist das Ergebnis als formatierte Tabelle eingetragen worden.

▲ nach oben …

Anzahl Fehlermeldungen jeder Maschine je Zeitraum

Im ersten Schritt werden Sie der Abfrage RawData (3) auch einen anderen Namen geben, beispielsweise einzelne Maschinen. Der Form halber könnten bzw. sollten Sie die Spalte Attribut zu Maschine umbenennen. Markieren Sie nun nacheinander Maschine und Zeitbereich ab, Rechtsklick in eine der beiden Überschriften und Gruppieren nach… Auch hier ist das durch Power Query berechnete Ergebnis erst einmal korrekt.

„Erst einmal“, weil die Anordnung gewiss nicht optimal ist. Die Daten sollten noch sortiert werden. Und nun stellt sich die Frage, welche Form von Überblick Sie gerne hätten. Die eine Möglichkeit stellt sich so dar, dass erst alle Zeiten der Maschine 1 jeweils nach Uhrzeiten zusammengefasst dargestellt werden und darunter die gleiche Logik für die Maschine 2 und folgende. – Die zweite von mir angedachte Darstellungsweise wäre so, dass die Sortierung erst nach Zeitblöcken aufsteigend erfolgt und innerhalb der Zeitbereiche die Maschinen in aufsteigender Reihenfolge. Beide Sichtweisen lassen sich durch die Reihenfolge der Sortierung der Ergebnis-Abfrage realisieren. Schauen Sie gerne in der Musterdatei im Blattregister Auswertungen nach, dort sind auch diese beiden per PQ erstellten Ergebnisse gezeigt. Natürlich hätte auch eine der beiden Sortierungn als Ergebnis gereicht und User würden dann in Excel die Sortierung ändern können. 😎 

Möchten Sie bei dieser Form der Berechnung auch noch zusätzlich die Gesamtzahl aller (100) Fehler berechnet haben, könnte dieses zwar auch in Power Query (typischerweise einer getrennten Abfrage) geschehen; ich schlage Ihnen vor, die Ergebnis-Tabelle in Excel durch eine automatische Berechnung der Summe unterhalb der letzten Datenzeile in einer Ergebniszeile durchzuführen.

Es bleibt ihrer Experimentierfreude unbenommen, die Anzeige des Zeitbereichs genauer zu definieren; das wäre dann beispielsweise der Text 14:00 – 14:29. Und natürlich können Sie als fortgeschrittener Anwender in Sachen Power Query die Berechnung des Zeitintervalls mit unterschiedlichen M-Funktionen durchführen. Das bietet sich gewiss immer dann an, wenn sie beispielsweise einen 5-Minuten-Rhythmus generieren wollen oder Sie lieber mit reinem Code als mit diversen Mausklicks arbeiten.

▲ nach oben …

3 Maschinen, 3 *.CSV

In vielen Fällen wird von der Maschinen-Steuerungssoftware jeder einzelne Maschine auch ein getrennter Fehlerbericht erstellt. Typischerweise wird dann je Maschine für 1 Kalender-Tag (bis zu 24 Stunden Einsatzzeit) eine einzelne CSV-Datei erstellt. Ich habe für Sie die gleichen Daten wie zuvor in jeweils eine CSV abgespeichert und Ihnen hier als gepackte ZIP-Datei zur Verfügung gestellt. Und in diesen (entpackten) Files sind auch Spalten-Überschriften. Idealerweise werden Sie die drei Files in 1 getrennten Ordner entpacken, wo vorzugsweise auch nur diese Daten enthalten sind. Das erleichtert den Einstieg … 😛 

Apropos „Einstieg“: Dieser gestaltet sich naturgemäß deutlich anders als in der vorherigen Übung. Erstens sind es ja keine Excel- sondern es sind reine Text-Dateien und zweitens ist es nicht nur ein einziges File sondern es sind mehrere Dateien, die zu einer einzigen Abfrage zusammengefasst werden sollen und müssen.

Ich gehe davon aus, dass die entpackten Dateien bereits in dem angesprochenen getrennten Folder liegen. In Excel gehen Sie über Daten | Daten abrufen | Aus Datei | Aus Ordner und wählen Sie dann im Dialog den entsprechenden Ordner aus. Nach einem Klick auf Öffnen erweitern Sie die Schaltfläche Laden und wählen Laden in… Idealerweise klicken Sie auf Nur Verbindung erstellen und schließen dann auch diesen Dialog. Öffnen Sie nun die (noch) einzige Abfrage, welche typischerweise den Namen des Verzeichnisses hat.

Sie erkennen eine Query, wo in der zweiten Spalte (Name) der Name nebst Erweiterung aller Dateien des entsprechenden Verzeichnisses angezeigt wird. Wenn ausschließlich die drei Files mit den Fehlermeldungen enthalten sind, brauchen Sie an dieser Stelle nichts weiter zu unternehmen. Andernfalls nutzen Sie die Möglichkeit, die gewünschten Dateien nach den erforderlichen Kriterien zu filtern.

Erweitern Sie nun die erste Spalte Content durch einen Klick auf die zwei nach unten zeigenden Pfeile und überprüfen Sie im Dialog Daten kombinieren, ob die Vorschau prinzipiell dem entspricht, was Sie für die Auswertung als Basisdaten brauchen. Dass PQ ohne Ihr Zutun erkennt, dass es sich um Semikola als Trennzeichen handelt und auch die Codierung (Dateiursprung) korrekt zuordnet ist eine feine Sache… 😉 

Nach dem Schließen des Dialogs per OK  werden sie erkennen, dass im rechten Seitenfenster diverse Schritte eingefügt wurden und bei den Abfragen im linken Seitenfenster auch verschiedene Abfragen automatisch erstellt worden sind. – In Stichworten hier die weitere Vorgehensweise, wie ich sie Ihnen empfehle:

  • Löschen Sie die erste Zeile der Abfrage.
  • Wählen Sie Start | Erste Zeile als Überschrift verwenden.
  • Ändern Sie die Überschrift der ersten Spalte auf (nur) Maschine.
  • Ändern Sie den Datentyp der Spalte Date auf Datum.
  • Der gewünschte Erfolg ist, dass jene Zellen dieser Spalte, die kein Datum enthalten, nun als Fehler (Error) deklariert sind.
  • Die Spalte Date ist markiert; Zeilen verringern | Zeilen entfernen | Fehler entfernen.
  • In der Spalte Maschine die Endung .csv löschen und eventuell den _ durch ein Leerzeichen ersetzen lassen. Beides geht beispielsweise über das Menü Transformieren.
  • Die Spalte Time entweder in den Datentyp Zeit konvertieren und dann nach dem Muster wie oben beschrieben weitermachen oder die Spalte nach dem Zeichen : teilen (Beim äußersten linken Trennzeichen) und die beiden Spalten in Stunde und Minute umbenennen; der Datentyp muss dann natürlich noch auf Ganze Zahl angepasst werden.

Im Grunde genommen haben Sie jetzt eine Datenlage, die Sie in praktisch gleicher Form in der ersten Übung wiederfinden können. Die restlichen Schritte sind dann auch so gut wie identisch. Was da anders ist: Das durch die Maschine erfasste Datum ist hier noch in der Query enthalten und kann bei Bedarf mit gespeichert und ausgewertet werden.

Insbesondere für Monats-Auswertungen, wo dann ja die CSV-Dateien vieler Tage im Verzeichnis erfasst sind, kann das eine Übersicht recht komfortabel ermöglichen. – Bei der Gelegenheit meine Anmerkung: Sie können natürlich bei Schließen & laden in… auch gleich die Möglichkeit wählen, die letztendliche Auswertung über eine PivotTable mit all den schönen Möglichkeiten zu machen.

▲ nach oben …


Rückmeldungen / Feedback gerne per Mail an mich (G.Mumme@Excel-ist-sexy.de)

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

Dann würde ich mich über einen Beitrag Ihrerseits z.B. 2,50 € freuen … (← Klick mich!)

Dieser Beitrag wurde unter c) Aufsteiger, Datentyp anpassen, Datum & Zeit, Datum und Zeit, File-Handling, Foren-Q&A, Power Query abgelegt und mit , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.