Extrahieren aller übereinstimmenden (oder nahe beieinander liegenden) Werte zwischen den angegebenen START n-ten und END n-ten Werten / mithilfe von Power Query

Question

Erwägen Sie die Verwendung von zwei Tabellen. Tabelle1 (T1) enthält Spalten für Datum/Uhrzeit und Druckwerte und Tabelle2 (T2) enthält ebenfalls Spalten für Datum/Uhrzeit und Infowerte.

T1 zeigt die Druckänderung im Zeitverlauf, z. B.: 21.11.2019 19:25:15 mit 147 mbar und es ändert sich grundsätzlich pro Minute, 21.11.2019 19:26:15 mit 198 mbar und so weiter... T2 ist etwas anders, es kann als Tabelle von Paaren mit START- und END-Instanzen oder im Fall von Originaldateien (Start-/Endwerte) beobachtet werden. Diese Werte werden ursprünglich aus der Protokolldatei unseres Systems extrahiert, in der alle Datenprotokolle gespeichert sind. Es ist ein Protokoll, in dem Sie verfolgen können, was im System passiert/passiert ist.

Ausgabe: Was ich tatsächlich brauche, sind alle Werte (Datum/Uhrzeit und Druck (P7)), die zwischen diesen START- und END-Werten liegen und mit diesen Paaren aus T2 übereinstimmen.

Etwas wie das:

Wenn ich zusätzlich "START n-ter" und "END n-ter Wert" in die Kopfzeilenbeschreibung schreibe, dann sehe ichum einen Wert von 5 Minuten zu habenals START-Wert, z.B. wenn START am 21.11.2019 19:25 mit 147 mbar ist, brauche ich 21.11.2019 19:30 mit 198 mbar, und als ENDE nicht den letzten Wert, sondern einen vor dem Ende im selben Zeitraum, z.B. letzter Wert ist am 21.11.2019 13:00 und ich brauche einen am 21.11.2019 12:59...ich hoffe das ist für dich verständlich, ist etwas knifflig.

Anweisungen und was ich als „5. Minute“ benötige, finden Sie weiter unten in der SS.

Worauf Sie achten solltenund was meiner Meinung nach die korrekte Übereinstimmung zwischen Tabellen beeinträchtigt, sind die :Sekunden in der Datums-/Uhrzeitspalte (für T1 und T2). Solange es keine genauen (Datums-/Uhrzeit-)Übereinstimmungen zwischen zwei Tabellen gibt, stürzt die Abfrage ab … gibt es eine Möglichkeit, nach einer Minutenübereinstimmung und nicht nach einer genauen Sekunde zu suchen? Wenn nicht Sekunde, dann Minute … so etwas in der Art …

Das zweite Problem kann auftreten, wenn es an einem Tag mehrere „Paare“ gibt. Wie Sie vielleicht an T2 feststellen, ist es normalerweise ein Paar pro Tag, aber es gibt und wird Fälle geben, in denen es an einem Tag mehrere „Start-/End“-Instanzen gibt.

Als Drittes ist zu beachten, dass es möglich sein könnte, dass Sie zwar Paare in T2 haben, aber keine Werte vorhanden sind, die zwischen diese Daten aus T1 fallen (es handelt sich um ein internes Problem mit dem System selbst, bei dem wir die Daten in einem bestimmten Zeitraum nicht erhalten haben). In diesem Fall sollte die Abfrage jedoch ohne Fehlermeldung fortgesetzt werden und unterwegs weitere Übereinstimmungen bereitstellen.

Ich gebe die Abfragen an, die ich verwendet habe, aber sie liefern mir keine richtigen Ergebnisse:

Erster Codeist sehr langsam und lädt die Daten ewig, erfüllt aber trotzdem seinen Zweck nicht.

Tabellen: Parameter => T1 Log_Original => T2

let Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="Parameters"]}[Content]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date/Time", type datetime}, {"P7 [mbar]", Int64.Type}}),
//get start/stop times table
Source2 = Table.Buffer(Excel.CurrentWorkbook(){[Name="Log_Original"]}[Content]),
typeIt = Table.TransformColumnTypes(Source2, {"Date/Time", type datetime}),
#"Filtered Rows" = Table.SelectRows(typeIt, each ([#"Date/Time"] <> null)),
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1),
// shift Info up one row for comparison
    shiftedList = List.RemoveFirstN(  #"Added Index"[Info],1),
    custom1 = Table.ToColumns(  #"Added Index") & {shiftedList},
    custom2 = Table.FromColumns(custom1,Table.ColumnNames(  #"Added Index") & {"NextInfo"}),
#"Added Custom" = Table.AddColumn(custom2, "NextLineStart", each if Text.Contains([Info],"start", Comparer.OrdinalIgnoreCase)
            and  Text.Contains([NextInfo],"start", Comparer.OrdinalIgnoreCase)
        then "delete" 
        else null),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([NextLineStart] = null)),
#"Added Custom3" = Table.AddColumn(#"Filtered Rows1", "Type", each if Text.Contains(Text.Lower([Info]),"start") then "start" else if Text.Contains(Text.Lower([Info]),"finished") then "finished" else null),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom3",{"Info", "NextInfo", "NextLineStart"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Custom", each if [Type]="start" then [Index] else null),
#"Filled Down" = Table.FillDown(#"Added Custom1",{"Custom"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,"no data!",Replacer.ReplaceValue,{"Type"}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Replaced Value", {{"P7 [mbar]", null}}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Errors", List.Distinct(#"Replaced Errors"[Type]), "Type", "Date/Time"),
#"Added Custom2" = Table.AddColumn(#"Pivoted Column","Table",(i)=>Table.Sort(Table.SelectRows(Parameters, each [#"Date/Time"]>=i[start] and [#"Date/Time"]<=i[finished]),{{"Date/Time", Order.Ascending}}) , type table ),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"Zeit/d", "D/T_5min/End", "Log_D/T-End - 1min", "P7 [mbar]", "P_5min / P_End", "Zeit nach Äquilibrierung (5.min) bis Ende [h]", "dp/dt_mittel [Pa/min]", "Custom", "start", "finished", "no data!"})
in
    #"Removed Columns2"

Zweiter Codeist anders, schneller, erledigt aber die Hausaufgaben immer noch nicht vollständig:

 let

//Be sure to change the table names in the Source= and Source2= lines to be the actual table names from your workbook
    Source = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date/Time", type datetime}, {"P7 [mbar]", Int64.Type}}),
       
//get start/stop times table
    Source2 = Excel.CurrentWorkbook(){[Name="Log_Original"]}[Content],
    typeIt = Table.TransformColumnTypes(Source2, {"Date/Time", type datetime}),
    #"Filtered Rows" = Table.SelectRows(typeIt, each ([#"Date/Time"] <> null)),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1),

#"Added Custom" = Table.AddColumn(#"Added Index", "NextLineStart", each if Text.Contains([Info],"start", Comparer.OrdinalIgnoreCase) = true 
                and Text.Contains(#"Added Index"[Info]{[Index]+1},"start",Comparer.OrdinalIgnoreCase) = true 
            then "delete" 
            else null),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([NextLineStart] = null)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Index", "NextLineStart"}),

//create a list of all the relevant start/stop times
     filterTimes = List.Combine(
                        List.Generate(
                            ()=> [times = List.DateTimes(#"Removed Columns1"[#"Date/Time"]{0}, 
                                                        Duration.TotalSeconds(#"Removed Columns1"[#"Date/Time"]{1}-#"Removed Columns1"[#"Date/Time"]{0})+1,
                                                        #duration(0,0,0,1)), IDX = 0],
                            each [IDX] < Table.RowCount(#"Removed Columns1"),
                            each [times = List.DateTimes(#"Removed Columns1"[#"Date/Time"]{[IDX]+2}, 
                                                        Duration.TotalSeconds(#"Removed Columns1"[#"Date/Time"]{[IDX]+3}-#"Removed Columns1"[#"Date/Time"]{[IDX]+2})+1,
                                                        #duration(0,0,0,1)), IDX = [IDX]+2],
                            each [times]
                                    )
                            ),

//filter the table using the list
filterTimesCol = Table.FromList(filterTimes,Splitter.SplitByNothing()),
filteredTable = Table.Join(#"Changed Type","Date/Time",filterTimesCol,"Column1",JoinKind.Inner),
    #"Removed Columns" = Table.RemoveColumns(filteredTable,{"Column1"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom", each DateTime.ToText([#"Date/Time"],"dd-MMM-yy")),
    #"Filtered Rows2" = Table.SelectRows(#"Added Custom1", each [#"Date/Time"] > #datetime(2019, 01, 01, 0, 0, 0)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows2",{{"Date/Time", Order.Ascending}})
in
    #"Sorted Rows"

Als letztes lade ich die Testdaten in Google Drive hoch:

https://docs.google.com/spreadsheets/d/10AtLpQuRZriGKWz9vwAoVh7zp6kKKy5n/edit?usp=sharing&ouid=101738555398870704584&rtpof=true&sd=true

Answer 1

Ich bin sicher, dass dabei bestimmte Teile Ihrer Anforderungen nicht berücksichtigt werden, aber ich denke, der Ansatz ist sinnvoll und sollte Sie auf den richtigen Weg bringen.

Auf Tabelle1:

• Fügen Sie eine Spalte hinzu, um die Sekunden aus der Datums- und Uhrzeitangabe zu entfernen
• Fügen Sie eine Spalte hinzu, um nur das Datum anzuzeigen
• Andere Formatierungsänderungen wie Spaltenumbenennungen, Neuanordnung usw.

Am Ende sieht es so aus:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date/Time", type datetime}, {"P7 [mbar]", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Date/Time", "date_time"}, {"P7 [mbar]", "p7_mbar"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "date_time_no_seconds", each DateTime.From(DateTime.ToText([date_time],"yyyy-MM-dd HH:mm"))),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"date_time_no_seconds", type datetime}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"date_time", "date_time_no_seconds", "p7_mbar"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Reordered Columns", "date_time", "date_time - Copy"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Duplicated Column",{{"date_time - Copy", type date}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"date_time - Copy", "date_of_date_time"}})
in
    #"Renamed Columns1"

Auf Tabelle2:

• Fügen Sie eine Spalte hinzu, um die Sekunden aus der Datums- und Uhrzeitangabe zu entfernen
• Fügen Sie eine Spalte hinzu, um nur das Datum anzuzeigen
• Fügen Sie eine Spalte hinzu, die nur die Wörter "Start", "Fertig" und "Fehlgeschlagen" enthält (ich habe dies mit "Benutzerdefinierte Spalte aus Beispielen" gemacht, also war es ziemlich einfach
• Fügen Sie eine Spalte hinzu, um nur die C1/C2-Werte aufzulisten
• Erstellen Sie einen Index für jede Zeile innerhalb jedes Datums
• Erstellen Sie eine Gruppe von jeweils 2 Zeilen innerhalb jedes Datums
• Den C1/C2-Wert mit der oben erstellten Gruppe zusammenführen
• Pivotieren Sie die Spalte „Gestartet/Beendet/Fehlgeschlagen“ mit Datum und Uhrzeit ohne Sekunden und ohne Aggregation.

Am Ende sieht es so aus:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date/Time", type datetime}, {"Info", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Date/Time", "date_time"}, {"Info", "info"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "date_time_no_seconds", each DateTime.From(DateTime.ToText([date_time],"yyyy-MM-dd HH:mm"))),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"date_time", "date_time_no_seconds", "info"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Reordered Columns",{{"date_time_no_seconds", type datetime}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type4", "date_time", "date_time - Copy"),
    #"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"date_time - Copy", "date_of_date_time"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"date_of_date_time", type date}}),
    #"Added Custom Column" = Table.AddColumn(#"Changed Type1", "Custom", each let splitinfo = Splitter.SplitTextByDelimiter(" C", QuoteStyle.None)([info]), splitsplitinfo1 = Splitter.SplitTextByDelimiter(".", QuoteStyle.None)(splitinfo{1}?) in Text.Combine({Text.Middle(Text.Proper(splitsplitinfo1{0}?), 2), Text.Reverse(Text.Middle(Text.Reverse(splitinfo{0}?), 9))}), type text),
    #"Inserted Kept Characters" = Table.AddColumn(#"Added Custom Column", "Kept Characters", each Text.Select([info], {"0".."9", "C"}), type text),
    #"Renamed Columns2" = Table.RenameColumns(#"Inserted Kept Characters",{{"Custom", "start_finish"}, {"Kept Characters", "leaktest_name"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns2",{"info"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"date_of_date_time"}, {{"Count", each _, type table [date_time=nullable datetime, date_of_date_time=nullable date, start_finish=text, leaktest_name=text]}}),
    Indexed = Table.TransformColumns(#"Grouped Rows", {{"Count", each Table.AddIndexColumn(_,"date_index", 1, 1)}}),
    #"Expanded Count" = Table.ExpandTableColumn(Indexed, "Count", {"date_time", "date_time_no_seconds", "date_of_date_time", "start_finish", "leaktest_name", "date_index"}, {"date_time", "date_time_no_seconds", "date_of_date_time.1", "start_finish", "leaktest_name", "date_index"}),
    #"Changed Type5" = Table.TransformColumnTypes(#"Expanded Count",{{"date_time_no_seconds", type datetime}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type5",{"date_of_date_time.1"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "test_index", each Number.RoundUp([date_index]/2,0)),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"test_index", Int64.Type}, {"date_index", Int64.Type}, {"leaktest_name", type text}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type2", "Merged", each Text.Combine({[leaktest_name], "-", Text.From([test_index], "en-US")}), type text),
    #"Removed Columns2" = Table.RemoveColumns(#"Inserted Merged Column",{"leaktest_name", "date_index", "test_index", "date_time"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns2", List.Distinct(#"Removed Columns2"[start_finish]), "start_finish", "date_time_no_seconds"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Pivoted Column",{{"Start", type datetime}, {"Finished", type datetime}, {"Failed", type datetime}})
in
    #"Changed Type3"

Zum Schluss 1 und 2 zusammenführen:

• Zusammenführen am Datum
• Fügen Sie eine Spalte hinzu, um zu testen, ob die mbar-Datumszeit ohne Sekunden zwischen der Start- und Endzeit in dieser Zeile liegt (Ich habe nichts mit "Fehlgeschlagen" gemacht, aber Sie möchten vielleicht)
• Filtern Sie den Datensatz, bei dem der obige Test „true“ zurückgibt
• Entfernen Sie die Filterspalte
• Robert ist der Bruder deiner Mutter

Es sieht aus wie das:

let
    Source = Table.NestedJoin(Table2, {"date_of_date_time"}, Table1, {"date_of_date_time"}, "Table1", JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(Source, "Table1", {"date_time_no_seconds", "p7_mbar"}, {"date_time_no_seconds", "p7_mbar"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table1", "in_range", each [date_time_no_seconds] >= [Start] and [date_time_no_seconds] < [Finished]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([in_range] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"in_range"})
in
    #"Removed Columns"