Office: (Office 365) Kombination von zwei Abfragen in PQ mit Abhängigkeiten

Hallo zusammen,

ich habe folgende Ausgangssituation. Ich habe einen Datensatz "Zeitpunkt 1" und einen Datensatz "Zeitpunkt 2". In Zeitpunkt 1 wird der Produnktionsstart eines Produktes auf einer gewissen Platznummer registriert. Zeitpunkt 2 markiert das Ende der Produktion des Produkts. Hier werden dann zusätzlich Information A und B registriert. Ich suche nun nach einer Lösung in PQ, die es mir möglich macht eine Tabelle/Abfrage zu generieren, die mir die Daten "Info A und B" aus Zeitpunkt 2 auch zum Zeitpunkt 1 angibt. Dies ist natürlich immer von der Nummer abhängig. Beide Datensätze haben zwar immer die entsprechenden Nummern, aber eben einen Zeitversatz. Weil sie eben Start und Ende der Produktion markieren. Ich hänge einen Beispieldatensatz mit an. Zeitpunkt 1 und Zeitpunkt zwei liegen bei mir beide auch als Abfragen in PQ vor.

Besten Dank.

 

Moin Hannes,

wenn ich Dich richtig verstanden habe, dann so wie im Anhang?

 

Hallo Ralf,
genau so soll die Lösung aussehen. Ich habe auch versucht diese bei mir zu implementieren. Nur leider ist die Datenmenge so groß, dass dies nicht die geeignete Lösung ist. Die Datei aktualisiert seit über 1h...ich habe es nun abgebrochen. (Fehler: Expression.Error: Auswertung: nicht genügend Arbeitsspeicher. Der Vorgang kann nicht fortgesetzt werden.) Es handelt sich hierbei um mehrere 10tsd Zeilen.
Gibt es noch eine andere Möglichkeit?

 

Hallo,

meine Lösung besteht in einer reinen Formellösung, im Arbeitsblatt "Ergebnis" in Zelle G3:
=LET(tbA; SORTIEREN(Tabelle1); tbE; SORTIEREN(Tabelle2); kpf; Tabelle2[#Kopfzeilen]; idA; SEQUENZ(ZEILEN(tbA)); r; REDUCE(""; idA; LAMBDA(Akk;i; LET(dt; INDEX(tbA; i; 1); nr; INDEX(tbA; i; 2); fn; FILTER(tbE; INDEX(tbE; 0; 2) = nr; "--"); fd; XVERWEIS(dt; INDEX(fn; 0; 1); fn; ""; 1; 1); VSTAPELN(Akk; fd)))); rr; SPALTENWAHL(WEGLASSEN(r; 1); 3; 4); VSTAPELN(kpf; HSTAPELN(tbA; WENNFEHLER(rr; ""))))
oder in strukturierter Darstellung:
Den Anhang 37147 betrachten
Allerdings unterscheidet sich mein Resultat vom Resultat von Ralf. Ich weiß nicht, was von dir wirklich gewünscht wird.

 

Also, mehrere 10tsd Zeilen sind normalerweise kein Problem. Kann es sein, dass da noch andere Abfragen im Hintergrund aktualisiert werden?
Die Lösung von @Exl121150 ist sehr schön! Allerdings hab ich auch noch eine andere Lösung mit noch anderem Ergebnis. Hab das Prozedere abgekürzt und nehme hier Bezug auf die fertigen Teile. Musst Du halt mal schauen, welches Deinen Anforderungen entspricht.

PS: Hatte die falsche Datei hochgladen. Upload der richtigen: 16:17

 

...hab mir das gerade nochmal angesehen... mit diesen Daten, unter diesen Bedingungen kannst Du auf kein korrektes Ergebnis kommen, da die Datensätze nicht eindeutig zuzuordnen sind. Normalerweise müssten die Datensätze von Z1 und Z2 eine eindeutige ID haben, über die sie verknüpft werden könnten. Haben sie aber nicht. Könnte man noch austricksen, indem man versucht, die Zeiten in einen sinnvollen Zusammenhang zu bringen, aber da Du unterschiedlich viele Datensätze in Z1 und Z2 zu den Nummern hast, schlägt das auch fehl.
Bsp.: Du hast in Z1 11 Datensätze für Nummer 1, aber nur 10 in Z2, Für Nummer 3 hast 13 Datensätze in Z1, dafür 14 in Z2. Du holst also mehr raus, als Du reingesteckt hast.... Das nenne ich mal Effizienz... da freut sich der Chef... Für Nummer 9 das gleiche Spiel...
Schau Dir mal die Spalte Abweichung in der Abfrage Beendet an. Da siehst Du das. Ihr habt Eure Daten falsch eingegeben.
Meine beiden Abfragen: Vereint und VereintMitFehlerinfo liefern genau aus diesem Grund auch falsche Ergebnisse.
Aber selbst das könnte man sicher noch ausbügeln, besser ist jedoch, das Übel an der Wurzel zu packen!
Mein Vorschlag: gebt jedem Teil, dass Ihr in die Maschine stopft in Z1 eine eindeutige ID und verwendet die für die fertigen Teile in Z2.
Oder... Du schaust Dir die Datensätze mit den Fehlern an und korrigierst die in den Quellen. Darum wärst Du aber wirklich nicht zu beneiden...
Oder Du überlegst Dir einen Algorithmus, der die abweichenden Anzahlen berückichtigt. Dazu hab ich jetzt aber keine Lust. Vielleicht wer anderes?

 

Für den jeweils ersten Fehlenden Produktionsbeginn bei Platz 5 und 6 habe ich Dummywerte eingetragen. Bei deinen echten Daten sollte dies vermutlich nicht vorkommen. Andernfalls müsste das in meiner PQ-Lösung noch berücksichtigt werden. Ansonsten sind meine Ergebnisse identisch zu denen von Exl121150.

 

Interessante Lösung, allerdings scheint mir das Ergebnis immer noch falsch zu sein. Die durchschnittliche Bearbeitungsdauer liegt bei ca. 22 Min. Deshalb hatte ich die ja auch zum Vergleich mit eingeführt. Und wie man sieht, bei Nummer 6 gibt es einige z. T. erhebliche Überschreitungen, bis zu über 3 Stunden. Das kann doch nicht stimmen... es sei denn, durch Maschinenausfall o. ä .verursacht. Oder liegt das nun an den Beispieldatensätzen oder an der Auswertung?

 

Hallo WS-53,

die PQ-Funktion Table.Group(...) kann mehr als das Editor-Oberflächen-Menü zuläßt:
- so sind Gruppierung und Indexerstellung in 1 PQ-Schritt möglich

Code:

    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Nummer"},
         {{"alle", each Table.AddIndexColumn(_, "Index", 1, 1),
                   type table [#"Datum/Uhrzeit"=nullable datetime, Nummer=nullable number,
                               Info A=nullable text, Info B=nullable number,
                               Index = Int64.Type]
         }}),

- damit erspart man sich 1 zusätzliche Spalte in der Haupttabelle
- welche dann doch wieder gelöscht werden muß
- hier sollte M$ unbedingt nacharbeiten

Gruß von Luschi
aus klein-Paris

 

Ich dachte, hieraus wird sofort klar, dass sich dadurch auch falsche Produktionszeiten ergeben!

 

Hi Luschi,

du hattest mir das schon einmal erklärt. Aber da ich mir damals das Beispiel nicht gespeichert hatte unc ich die Syntax nicht aus dem Gedächtnis hinbekomme, gehe ich halt den etwas umständlicheren Weg. Aber jetzt werde ich es mir mit einem sprechenden Titel speichern, damit ich es zukünftig nutzten kann.

 

nee... eigentlich enstehen keine falschen Produktionszeiten. Excel denkt sich die ja nicht aus...
Aber man erkennt die Fehler in der Datenbasis, dort liegt der Hund begraben...

 

Hallo ws-53,

ich glaube, meine damalige Version zum Erzeugen eines Indizes in einer gruppierten Spalte sah so aus:

Code:

    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Nummer"},
         {{"alle", each _, type table [#"Datum/Uhrzeit"=nullable datetime, Nummer=nullable number,
                               Info A=nullable text, Info B=nullable number]
         }}),
    #"interner Index" = Table.TransformColumns(#"Grouped Rows",
        {{"alle", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}}),

Gruß von Luschi
aus klein-Paris

 

Das ganze geht auch ohne Table.Join und ohne Dummy Datensätze für fehlende Startwerte. Das könnte somit bei einem größerem Datenvolumen auch Vorteile in der Laufzeit haben. Die Ergebnisse sind dabei immer noch identisch zu der Formelvariante von Exl121150.

 

Hallo ws-53,

in diesem Video von Goodly zeigt der PQ-Experte Geert Delmulle 4 Methoden, 2 PQ-Abfragen zu verknüpfen (Beispieldatei: GDE's_LPOSO_GDE002.xlsx)
- List-Accumulate, List.Generate, List.PositionOf und Table.Combine
- mit entsprechenden Zeitmessungen
- und Table.Combine zieht da eindeutig den Kürzeren

Power Query Tricks Battle 4 - Goodly


Gruß von Luschi
aus klein-Paris

Beispieldateien: Power Query Tricks Battle 4 - Goodly