지정된 START n번째 값과 END n번째 값 사이에서 일치하는(또는 가까운) 모든 값 추출/파워 쿼리 사용

Question

두 개의 테이블을 고려하십시오. Table1(T1)에는 날짜/시간 및 압력 값 열이 있고 Table2(T2)에도 날짜/시간 및 정보 값 열이 있습니다.

T1은 시간에 따른 압력 변화를 보여줍니다. 예를 들어 147mbar의 경우 21.11.2019 19:25:15이고 기본적으로 분당 변경되고 21.11.2019 19:26:15의 경우 198mbar 등입니다. T2는 약간 다르며, START 및 END 인스턴스가 있는 쌍의 테이블로 관찰되거나 원본 파일의 경우(시작/완료 값)로 관찰될 수 있습니다. 해당 값은 원래 모든 데이터로그가 저장되는 당사 시스템의 로그 파일에서 추출됩니다. 시스템 내에서 무슨 일이 일어나고 있는지 추적할 수 있는 로그입니다.

산출: 실제로 필요한 것은 T2의 쌍과 일치하는 START 및 END 값 사이에 있는 모든 값(날짜/시간 및 압력(P7))을 갖는 것입니다.

이 같은:

추가적으로 헤더 설명에 "START n번째 값"과 "END n번째 값"이라고 썼는데, 찾아보니5분 값을 갖기 위해예를 들어 START 값이 2019년 11월 21일 19시 25분에 147mbar인 경우 2019년 11월 21일 19시 30분에 198mbar가 필요하며 END는 마지막 값이 아니라 같은 기간에 끝나기 전의 값입니다. 인스턴스 마지막 값은 2019년 11월 21일 13:00이고 2019년 11월 21일 12:59에 하나가 필요합니다. 이 내용이 이해되셨기를 바랍니다. 약간 까다롭습니다.

지침과 "5분"으로 필요한 사항은 아래 SS를 확인하세요.

주의할 점테이블 간의 올바른 일치에 영향을 미치는 것은 날짜/시간 열(T1 및 T2 모두에 대해)의 :초입니다. 두 테이블 사이에 정확한(날짜/시간) 일치가 없는 한 쿼리가 충돌합니다. 정확히 초가 아닌 분 일치를 찾도록 만드는 방법이 있습니까? 두 번째가 아니라면 분... 그런 것..

두 번째 문제는 하루 내에 더 많은 "쌍"이 있을 때 발생할 수 있습니다. T2에서 알 수 있듯이 일반적으로 하루에 한 쌍이지만, 내에서 더 많은 "시작/종료" 인스턴스가 발생하는 경우가 있으며 앞으로도 그럴 것입니다. 어느 날.

세 번째로 주목해야 할 점은 T2에 쌍이 있지만 T1의 해당 날짜 사이에 해당하는 값이 없을 수도 있다는 것입니다(특정 기간에 데이터를 받지 못한 시스템 자체의 내부 문제입니다). 하지만 이 경우 쿼리는 오류 없이 계속 진행되어야 하며 도중에 또 다른 일치 항목을 제공해야 합니다.

내가 사용한 쿼리를 제공하고 있지만 적절한 결과를 제공하지 않습니다.

첫 번째 코드매우 느리고 데이터를 영원히 로드하지만 여전히 의무를 다하지 못합니다.

테이블: 매개변수 => 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"

두 번째 코드다르고 빠르지만 여전히 숙제를 완전히 수행하지는 않습니다.

 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"

마지막으로 google.drive에 테스트 데이터를 업로드합니다.

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

Answer 1

이렇게 하면 귀하의 요구 사항 중 특정 부분을 놓치게 될 것입니다. 그러나 저는 이 접근 방식이 건전하며 귀하가 올바른 길로 갈 수 있도록 해야 한다고 생각합니다.

테이블1에:

• 날짜 시간에서 초를 제거하는 열을 추가하세요.
• 날짜만 표시하려면 열을 추가하세요.
• 열 이름 바꾸기, 재정렬 등과 ​​같은 기타 형식 변경

마지막에는 다음과 같습니다.

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"

Table2에:

• 날짜 시간에서 초를 제거하는 열을 추가하세요.
• 날짜만 표시하려면 열을 추가하세요.
• "Start", "Finished" 및 "Failed"라는 단어만 포함된 열을 추가합니다. (저는 "예제의 사용자 정의 열"을 사용하여 이 작업을 수행했기 때문에 꽤 쉬웠습니다.
• C1/C2 값만 나열하려면 열을 추가하세요.
• 각 날짜 내의 각 행에 대한 인덱스 생성
• 각 날짜 내에서 각 2개의 행으로 구성된 그룹을 만듭니다.
• 위에서 생성한 그룹과 C1/C2 값을 병합합니다.
• 집계 없이 초 없이 날짜 시간을 사용하여 시작/완료/실패 열을 피벗합니다.

마지막에는 다음과 같습니다.

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"

마지막으로 1과 2를 병합합니다.

• 날짜에 병합
• 초가 없는 mbar 날짜 시간이 해당 행의 시작 시간과 종료 시간 사이에 있는지 테스트하는 열을 추가합니다(나는 "실패"에 대해 아무 것도 하지 않았습니다. 하지만 당신이 원할 수도 있습니다)
• 위 테스트가 true를 반환하는 데이터 세트를 필터링합니다.
• 필터 열 제거
• 로버트는 네 엄마의 동생이야

다음과 같습니다:

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"