考虑有两个表。表 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 中的那些对相匹配。
像这样：
此外，当我在标题描述中写下“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 分钟”。
注意事项我意识到影响表之间正确匹配的是日期/时间列中的 :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：
答案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"