☰

使用 Power Query 提取给定 START 第 n 个值和 END 第 n 个值之间的所有匹配（或接近）值

tag-icon • tag-icon microsoft-excel worksheet-function vba power-query microsoft-excel-365
使用 Power Query 提取给定 START 第 n 个值和 END 第 n 个值之间的所有匹配（或接近）值

考虑有两个表。表 1(T1) 有日期/时间和压力值列，表 2(T2) 也有日期/时间和信息值列。

T1 显示压力随时间的变化，例如：2019 年 11 月 21 日 19:25:15 为 147 mbar，基本每分钟变化一次，2019 年 11 月 21 日 19:26:15 为 198 mbar，等等……T2 略有不同，可以将其视为具有 START 和 END 实例的对表，或者在原始文件的情况下（开始/完成值）。这些值最初是从我们系统中的日志文件中提取的，所有数据日志都保存在该日志文件中。这是一个日志，您可以在其中跟踪系统内正在/已经发生的事情。

输出： 我真正需要的是获得介于 START 和 END 值之间的所有值（日期/时间和压力（P7）），并与 T2 中的那些对相匹配。

像这样： T2

在此处输入图片描述

此外，当我在标题描述中写下“START n-th”和“END n-th value”时，我正在寻找具有第 5 分钟的价值作为 START 值，例如如果 START 是在 2019 年 11 月 21 日 19:25，压力为 147 mbar，我需要 2019 年 11 月 21 日 19:30，压力为 198 mbar，END 不是最后一个值，而是在同一时期结束之前的值，例如最后一个值是 2019 年 11 月 21 日 13:00，我需要 2019 年 11 月 21 日 12:59 的值......我希望这对您有意义，这有点棘手。

查看下面的 SS 以了解说明以及我需要的“第 5 分钟”。

解释第 5 分钟

注意事项我意识到影响表之间正确匹配的是日期/时间列中的 :seconds（对于 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

答案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"

在表2上：

  • 添加一列以从日期时间中删除秒数
  • 添加一列仅显示日期
  • 添加一个仅包含单词“开始”、“完成”和“失败”的列（我使用“示例中的自定义列”完成了此操作，因此非常简单
  • 添加一列以仅列出 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"

相关内容