Office: Auslesen von Daten via PowerQuery

Hallo zusammen,

ich habe Rohdaten bestehend aus Datum/Uhrzeit, ID, Parameter 1 und Parameter 2.
Aus diesen Rohdaten möchte ich automatisiert (mittels PowerQuery) bestimmte Daten auslesen. Ich habe die Daten grafisch dargestellt um besser zu zeigen was ich erreichen möchte. Die gelb markierten Stellen sollen ausgelesen werden.
In den Beispieldaten im Anhang möchte ich von der blauen Tabelle (Rohdaten) mittels PowerQuery zur grünen Tabelle gelangen.

Zu den Daten:
- Datum/Uhrzeit ist fortlaufend
- Die ID ist für einen "Zyklus" eindeutig, nach Abschluss des Zyklus wird eine neue ID generiert (aufsteigend)
- Parameter 1 wechselt zwischen 0 und 165 (egal welche ID)
- Parameter 2 wechselt zwischen 0 und 166 (egal welche ID)

Ich hoffe ihr könnt mir helfen. Vielen Dank vorab.

Viele Grüße

 

Hallo Hannes,

mich würde eine PQ-Lösung hierfür auch interessieren, denn ich musste leider feststellen, dass es mir trotz relativ großen Zeitaufwand leider nicht gelungen ist, eine solche herzustellen.
Eine Formellösung dafür hatte ich dagegen innerhalb nur ca. 1 Minute erstellt. Aber eine solche ist ja von Dir nicht gesucht, oder?

 

Hallo

Mein Versuch
Es könnte sein das ich die Ziellösung nicht ganz verstanden habe, denn die Datei mit PQ

 

Hallo PIVPQ,

leider ist das nicht die Lösung. In deiner Variante sind zum Beispiel die Parameter Start- und Endzeiten falsch.
Die Ziellösung ist, dass meine grüne Lösungstabelle automatisiert (auch für 100 oder 1000 Zyklen bzw. IDs) erstellt wird und jeweils Start- und Endzeit eines Zyklus (einer ID) sowie die Start- und Endzeiten von Parameter 1 und 2 innerhalb des jeweiligen Uyklus (ID) wiedergibt.

Hallo neopa C,
nur interessehalber, wie lautet die Formellösung? Allerdings wird mir diese auf Dauer wahrscheinlich nichts bringen, da die blaue Rohdatentabelle aus PQ generiert wird und mmer wieder mit neuen Daten befüllt wird.

Danke euch. Vielleicht kann noch jemand anderes helfen.

 

Hallo Hannes,

hier meine Formellösung. Diese Formeln bedürfen keiner Anpassung wenn sich die Daten in Deiner Datentabelle durch PQ ändern /erweitern. Allerdings sollten diese soweit nach unten ziehend (vor)kopiert sein, damit bei Datenerweiterung alle Ergebniswerte auch dargestellt werden:

Arbeitsblatt mit dem Namen 'Beispieldaten'
	H	I	J	K	L	M	N
1	ID	ID Start	ID Ende	Parameter 1 Start	Parameter 1 Ende	Parameter 2 Start	Parameter 2 Ende
2	717	16.02.23 14:22:01	19.02.23 17:52:01	16.02.23 21:54:01	19.02.23 8:21:01	17.02.23 11:22:01	19.02.23 8:21:01
3	724	20.02.23 2:38:01	22.02.23 14:07:01	20.02.23 2:40:01	22.02.23 9:34:01	20.02.23 18:35:02	22.02.23 9:34:01
4

Zelle	Formel
H2	=WENNFEHLER(AGGREGAT(15;6;Tabelle1[ID]/(ZÄHLENWENN(H$1:H1;Tabelle1[ID])=0)/(Tabelle1[Parameter 1]>0);1);"")
I2	=WENNFEHLER(AGGREGAT(15;6;Tabelle1[Datum/Uhrzeit]/(Tabelle1[ID]=[@ID]);1);"")
J2	=WENNFEHLER(AGGREGAT(14;6;Tabelle1[Datum/Uhrzeit]/(Tabelle1[ID]=[@ID]);1);"")
K2	=WENNFEHLER(AGGREGAT(15;6;Tabelle1[Datum/Uhrzeit]/(Tabelle1[ID]=[@ID])/(Tabelle1[Parameter 1]>0);1);"")
L2	=WENNFEHLER(AGGREGAT(14;6;Tabelle1[Datum/Uhrzeit]/(Tabelle1[ID]=[@ID])/(Tabelle1[Parameter 1]>0);1);"")
M2	=WENNFEHLER(AGGREGAT(15;6;Tabelle1[Datum/Uhrzeit]/(Tabelle1[ID]=[@ID])/(Tabelle1[Parameter 2]>0);1);"")
N2	=WENNFEHLER(AGGREGAT(14;6;Tabelle1[Datum/Uhrzeit]/(Tabelle1[ID]=[@ID])/(Tabelle1[Parameter 1]>0);1);"")

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

 

Hallo neopa C,

danke für deine Antwort. Die Lösung funktioniert, allerdings muss ich sehen wie praktikabel die Lösung im Alltag mit sich immer wieder aktualisierenden Daten bewährt. Ein PowerQuery-Lösung bei der ich mit einem Klick alles aktualisiere und noch weitere Daten verknüpfen kann, wäre mir am liebsten. Mal sehen ob noch ein weiterer Vorschlag kommt.

 

Hallo Hannes,

wie ich schon schrieb. Die Formeln sind so definiert, dass diese aktualisierte und erweiterte Quelldaten [Tabelle1] automatisch berücksichtigt.

 

Hallo Hannes,

in der beiliegenden Excel-Datei habe ich dir im Arbeitsblatt "Beispieldaten" im Bereich H7:N9 eine mit PowerQuery erstellte strukturierte Tabelle namens "Tabelle1ID" eingefügt. Mittels Rechtsklick und Aktualisieren kann eine aktualisierte Abfrage erstellt werden mit der strukturierten Tabelle "Tabelle1" als Datenquelle.

Die in PowerQuery enthaltenen Abfragen habe ich nicht gezielt optimiert, da kannst du sicherlich noch einiges verbessern, wenn du willst. Vom Prinzip her habe die Datenquelle in PowerQuery verdreifacht, dann jeweils eine gruppierte Abfrage erstellt und diese 3 gruppierten Abfragen mittels ID-Feld zu einer einzigen Abfrage zusammengefügt.

 

Hallo Anton.

ich danke Dir hierfür.

Deine Gruppierung der Zeiten nach MIN und MAX war/ist für mich die entscheidende Idee zur Lösung mit PQ, die ich nicht im Focus hatte. Im Nachgang betrachtet ist dies zwar naheliegend, aber ich hatte mich wohl darin "verrannt", das Ergebnis mit einer Abfrage lösen zu wollen.

Habe in beigefügter Datei Deine Lösung für Hannes noch etwas optimiert. Wobei man möglicherweise diese mit Einsatz einer Parameterfunktion noch weiter optimieren könnte.
Allerdings mir reicht als PQ-Lösung schon die nun vorliegende.

Persönlich würde mich aber nun interessieren, ob und wenn ja, die PQ-Lösung bei einer Massendatenauswertung wie sie Hannes erwartet, schneller als die Formellösung ist.

 

Hallo Werner,

ich habe keine da leider keine Erfahrung, was das Abschätzen des Laufzeitverhaltens solcher Abfragen betrifft. Ich war in der Regel in der Vergangenheit zufrieden, wenn etwas 1) funktionierte und 2) in passabler Zeit ablief.
Im Allgemeinen heißt es, sind Excel-Zellzugriffe gegenüber Arrayformel-Zugriffen langsam. Wie es sich im Vergleich dazu bei Zugriffen in/auf PowerQuery-Tabellen verhält - ich weiß es leider nicht, insbesondere da ja doch in PowerQuery eine Reihe von Abfrageschritten ausgeführt werden müssen.

 

Hallo Anton,

danke für Deine Aussagen. Die abschließende Frage in meinem vorherigen Beitrag würde ich hiermit an Hannes richten. Er könnte einen Vergleich mit seinen Originaldaten vornehmen.

 

Hallo

Sorry da ich Termin bedingt nicht früher Antworten konnte, sind ja zwischenzeit andere Lösungen eingegangen.
Hoffe das jetzt die Ergebnisse stimmen.
Es ist auch möglich alles in einer Abfrage zu realisieren, Beweis als Datei.

 

Hallo PIVPQ,

Dein Ergebnis bzgl. der Startwerte des Parameter 2 ist noch nicht korrekt.
In der von Dir eingestellten Datei kann ich übrigens die einzelnen PQ-Schritte in meiner XL2016-Version nicht wie sonst bisher immer nachvollziehen, sondern kann mir lediglich Deinen M-Code im erweiterten PQ-Editor ansehen. Wieso das?

 

Hallo PIVPQ,

ich hab jetzt in etwa "verstanden", dass Du Deinen M-Code wohl komplett direkt im erweiterten PQ-Editor definiert hast, da diese so interaktiv nicht erstellt werden können und möglicherweise deswegen auch nur in diesem editierbar sind. Aus diesem meine ich auch zu erkennen, dass Du die Datenwerte für Parameter 2 zwar ermittelst aber diese noch nicht korrekt den Ergebnisspalten zugewiesen hast. Da ich aber sehr wenig Ahnung vom M-Code-Syntax habe, sind meine Korrekturversuche gescheitert.
Ich nutze PQ bisher im wesentlichen nur mit dessen interaktiv zu erstellten M-Code.

 

Hallo Werner

Danke für die Info, in der Tat hatte ich noch einen glitze keinen Fehler übersehen.
Fehler ausgebessert.