PQ - Gruppenzuordung über Index

[Jack_d]

  PQ_Index.xlsx (Größe: 20,54 KB / Downloads: 5)
Liebe Gemeinde ...

folgendes Verhalten in PQ bei dem mir der Kopf raucht und ich nicht weiter komme.


Ich habe eine Liste welche nach Raum, Datum und Start sortiert wird.
Dann soll die Differenz zum folgenden Datensatzes (am gleichen Tag im gleichen Raum) Ende bis  Beginn ermittelt werden

Ich die Idee - Gruppierung und erstellen eines Gruppenindex. Dann SelfJoin über den Index -1

-- bis hierhin klappt es auch --
wenn ich dann aber die Tabelle "aufklappe" (im letzten Schritt) dann verändert PQ die Zuordnung. Und ich versteh nicht warum das passiert ..

vielleicht weiss jemand Rat.- Hab sicher nur was ganz banales versaut.

Vielen Dank für eure Ideen =)
Jack

Code:

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    ChType_1 = Table.TransformColumnTypes(Quelle,{{"Start ", type time}, {"Ende", type time}}),
    SortierteDaten = Table.Sort(ChType_1,{{"Datum", Order.Ascending}, {"Raum ", Order.Ascending}, {"Start ", Order.Ascending}}),

    GruppierteDaten = Table.Group(SortierteDaten, {"Raum ", "Datum"}, {{"GruppenDaten", each Table.AddIndexColumn(_, "IndexInnerhalbGruppe", 1, 1, Int64.Type)}}),
    Exp_Group = Table.ExpandTableColumn(GruppierteDaten, "GruppenDaten", {"Start ", "Ende", "IndexInnerhalbGruppe"}, {"GruppenDaten.Start ", "GruppenDaten.Ende", "GruppenDaten.IndexInnerhalbGruppe"}),
    AddCol_IndexPlus1 = Table.AddColumn(Exp_Group, "IndexNächste", each [GruppenDaten.IndexInnerhalbGruppe]-1),
    Comb_TabInner = Table.NestedJoin(AddCol_IndexPlus1, {"Raum ", "Datum", "GruppenDaten.IndexInnerhalbGruppe"}, AddCol_IndexPlus1, {"Raum ", "Datum", "IndexNächste"}, "Hinzugefügte benutzerdefinierte Spalte", JoinKind.Inner),
    Exp_TabInner = Table.ExpandTableColumn(Comb_TabInner, "Hinzugefügte benutzerdefinierte Spalte", {"GruppenDaten.Start "}, {"GruppenDaten.Start .1"}),
    CHType_2 = Table.TransformColumnTypes(Exp_TabInner,{{"Raum ", type text}, {"Datum", type date}, {"GruppenDaten.Start ", type time}, {"GruppenDaten.Ende", type time}, {"GruppenDaten.IndexInnerhalbGruppe", Int64.Type}, {"IndexNächste", Int64.Type}, {"GruppenDaten.Start .1", type time}})
in
    CHType_2

[shift-del]

Moin

Versuche mal Table.Buffer() vor und/oder nach Table.Sort().

[Jack_d]

MoinDetlef,

Das klappt - zwar erst im dritten durchlaufen aber wird schon so richtig sein. 

Danke dir für den Hinweis. 

Jack

[Ralf A]

Moin,

wenn ich das richtig sehe, willst Du lediglich den Folgetermin für den gleichen Raum in die nächste Spalte bringen? Na ja... das ist schon problematisch, einfach mal in einer Zeile weiter unten nachzuschauen...
Vermutlich haust Du aber durch das (zudem unnötige) Gruppieren und Joinen einiges durcheinander. 
Das geht auch ohne...
Außerdem werden in Deiner Lösung 2 Termine unterschlagen... oder willst Du die Termine ohne Folgetermin nicht sehen? Dann filtere noch die null Werte aus der letzten Spalte.

Was ist denn bei Datumswechsel? Oder ist das uninterressant und es geht nur um die Tagesbelegung?

[Luschi]

Hallo Jack,

hier mal meine PQ-Variante. Dabei wird der wichtige Teil in der gruppierten Spalte 'GruppenDaten' erledigt.

Gruß von Luschi
aus klein-Paris

[Ralf A]

Hier noch eine Variante mit Pivot und Umrüstzeiten zwischen den Terminen...

@Luschi... 

[Jack_d]

Hallo Ralf, 
Hallo Luschi

Vielen Dank für eure Beiträge.

@Ralf. Es ist letztlich genau so, wie du es in deinem 2. Beitrag gelöst hast (mit Pivot). Es geht mir um die Wechsel bzw Rüstzeiten
und die gibt es am Ende nicht. Daher "fallen" die bei mir (richtigerweise) raus.

Deine PQ Lösung ist indes nich so ideal, weil sie .. wenn ich es recht verstehe sequentiell arbeitet.
ICh hatte zuerst auch eine sequentielle Lösung welche aufgrund der schieren Datenmenge aber ewig gebraucht hat
der sah im Prinzip ähnlich aus.

Code:

let
    Quelle = Excel.CurrentWorkbook(){[Name="DeineTabelle"]}[Content],
    SortierteDaten = Table.Sort(Quelle, {{"Ende", Order.Ascending}}),
    MitIndex = Table.AddIndexColumn(SortierteDaten, "Index", 1, 1, Int64.Type),
    VerschobeneDaten = Table.AddColumn(MitIndex, "Abstand", each
        if [Index] < Table.RowCount(MitIndex) then
            let
                nächsteZeile = MitIndex{[Index]},
                endeAktuelleZeile = [Ende],
                anfangNächsteZeile = nächsteZeile[Anfang],
                differenz = Duration.From(anfangNächsteZeile - endeAktuelleZeile)
            in
                differenz
        else null),

    Ergebnis = Table.RemoveColumns(VerschobeneDaten,{"Index"})
in
    Ergebnis

 @ Luschi

Danke auch dir. Leider steig ich intelektuell aus. Der "Spaß" liegt ja in der Zeile.
Kannst du mir die Zeile mal didaktisch zerlegen?

Code:

#"vorheriges Ende ermitteln" = Table.TransformColumns(GruppierteDaten,
        {{"GruppenDaten", (k)=> Table.AddColumn(k, "Ende_zuvor", (j)=>
            let a = k[Ende],
                b = try a{j[IndexInnerhalbGruppe]-2} otherwise null
            in  b)
        }}),

Viele Grüße und Vielen Dank

Jack

[Ralf A]

ICh hatte zuerst auch eine sequentielle Lösung welche aufgrund der schieren Datenmenge aber ewig gebraucht hat...

Was bedeutet denn "schiere Datenmenge"? Selbst wenn Ihr 100 Räume habt, die Ihr täglich 8 Stunden lang stündlich vermietet (jeweils 50 min. Nutzung, 10 min. Umrüstzeit), wären das gerade mal 800 Datensätze.
Es sei denn, Du willst Dir alle Daten seit der französischen Revolution anzeigen lassen...

Du brauchst doch wahrscheinlich maximal den Zeitraum von einer Woche? Das kann man doch filtern. Eine Parametertabelle "von - bis" Einrichten, den Zeitraum entsprechend filtern und gut isses...
Oder gibt s da noch mehr, uns unbekannte Dinge zu berücksichtigen?

[Jack_d]

Moin Ralf ..

Im Konkreten geht es um 12 Räume und alle Events im Jahresverlauf .. dies sind rund 12.500 (Grob 4 mal je Tag x 12 Räume x 250 Arbeitstage)
... deine 800 Datensätze sind dann nämlich auch nur 1 Tag ;-D

sei denn, Du willst Dir alle Daten seit der französischen Revolution anzeigen lassen

Das kommt dann im nächsten Schritt

 

Du brauchst doch wahrscheinlich maximal den Zeitraum von einer Woche? Das kann man doch filtern. Eine Parametertabelle "von - bis" Einrichten, den Zeitraum entsprechend filtern und gut isses...
Oder gibt s da noch mehr, uns unbekannte Dinge zu berücksichtigen?

Ich schau mir erstmal ein Jahr an und bewerte die Wechselzeiten. (MW;Median; Normalverteilung etc.)
Und im Sinne einer stabilen Statistik ist N immer gern größer

Oder gibt s da noch mehr, uns unbekannte Dinge zu berücksichtigen?

Da gibt es immer mehr .. aber ich versuche das Problem in der komplexität zu reduzieren, damit Antwortende eine 
realistische Möglichkeit haben zu helfen und nicht 2 Wochen einführung in meinen wirren Kopf benötigen ;-D

Zudem wächst die Abfragetiefe mit zunehmender "Schärfung".. in meinem Datensatz konnte ich beispielsweise feststellen das die 
Wechselzeit mit zunehmender Tageszeit auch zunimmt. Hier muss ich jetzt weiter schauen und schaue welche Nutzer in dem Raum waren und ob
das möglicherweise einen Rückschluss darauf lässt.

Deswegen ist es auch so wichtig das ich verstehe wie die Lösung funktioniert, weil ich sie sehr wahrscheinlich (auf zukünftige Fragen) anpassen muss.

Grüße Jack

[Ralf A]

...ok... hab das jetzt mal ein wenig abgewandelt.
  PQ_Index_mit_Pivot2.xlsx (Größe: 269,07 KB / Downloads: 4)  
Kannst ja mal mit den 8k Datensätzen ausprobieren, ob das so besser ist...
Ich vermute mal, dass Power Pivot kein unbekanntes Terrain für Dich ist und Du Dir die fehlenden Wünsche (Median etc.) damit selbständig erfüllen kannst. Der Grundstein ist ja gelegt.