我有一个数据集,包括日期/时间、压力和自定义列。这代表了时间数据的压力,我想知道我的起点(5分钟后)和-before最后值的结束点(行)在一个月内。为了给你一点帮助,通常测量的时间大约是30-40分钟,你可以在这个例子中看到。因此,这意味着数据量可能会有所不同。
时间列使用以下方法计算:
=([@[Date/Time]]-I5)*1440+L5这个数据集代表整个数据和所有有值的月份,我需要与屏幕截图中的这些起始点/结束点分隔(过滤)月份。我经常使用Power来处理数据,但可能还有另一种方法来获取这些values...and,使它们在可能的情况下成为未来数据的动态查询。

我也会上传我的虚拟工作簿与整个数据集(所有的月份),过滤表与月份,如果需要为您的信息和测试。
发布于 2022-01-19 20:13:06
Power查询中的:
根据您的共享工作簿和您所写的内容,似乎在任何一个月,您
编辑:算法中的小改动
的函数即可。
E 221列H 222f 223开始。算法
monthYear
monthYear 的月份子表。
如果只想查看给定月份的结果,可以在结果Excel表中筛选结果。
M码
请阅读评论并检查应用步骤,以更好地理解算法
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date/Time", type datetime}, {"P7 [mbar]", Int64.Type}}),
//add month/year column for grouping
#"Added Custom" = Table.AddColumn(#"Changed Type", "month Year",
each Number.ToText(Date.Month([#"Date/Time"]),"00") & Number.ToText(Date.Year([#"Date/Time"]),"0000")),
#"Grouped Rows" = Table.Group(#"Added Custom", {"month Year"}, {
//elapsed minutes column
{"Elapsed Minutes", (x)=> let
//remove first and last rows from table
t=Table.RemoveColumns(Table.RemoveFirstN(Table.RemoveLastN(x)),"month Year"),
//add a column with the elapsed minutes
TableToFilter = Table.FromColumns(
Table.ToColumns(t)
& {List.Generate(
()=>[em=null, idx=0],
each [idx]< Table.RowCount(t),
each [em=Duration.TotalMinutes(t[#"Date/Time"]{[idx]+1} - t[#"Date/Time"]{0}), idx=[idx]+1],
each [em])}, type table[#"Date/Time"=datetime, #"P7 [mbar]"=number, elapsed=number]),
//filter for last entry (which would be next to last in the month
maxMinute = List.Max(TableToFilter[elapsed]),
//filter for first entry in the 5th minute
fifthMinute = List.Select(TableToFilter[elapsed], each Number.IntegerDivide(_,1)>=5){0},
//select the 5th minute and the last row
FilteredTable = Table.SelectRows(TableToFilter, each [elapsed]=fifthMinute or [elapsed]=maxMinute)
in FilteredTable,type table[#"Date/Time"=datetime, #"P7 [mbar]"=number, elapsed=number]}
}),
//remove uneeded column and expand the others
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"month Year"}),
#"Expanded Elapsed Minutes" = Table.ExpandTableColumn(#"Removed Columns", "Elapsed Minutes", {"Date/Time", "P7 [mbar]"}, {"Date/Time", "P7 [mbar]"})
in
#"Expanded Elapsed Minutes"来自共享工作簿数据的结果

In Office/Excel365
过滤柱(如2020年1月)
E4: 1/1/2020
E5: 1/1/2020结果
F4 (date/time 5th minute): =IF(COUNTIFS(Table1[Date/Time],">="&E4,Table1[Date/Time],"<" & EDATE(E4,1))=0,"",
LET(x,FILTER(Table1[Date/Time],(Table1[Date/Time]>=E4)*(Table1[Date/Time]<EDATE(E4,1))),
y, (x-INDEX(x,2))*1440,
z, XMATCH(5,y,1),
INDEX(x,z,1)))
G4: (Pressure 5th minute): =IF(F4="","",
LET(x,FILTER(Table1,(Table1[Date/Time]>=E4)*(Table1[Date/Time]<EDATE(E4,1))),
y, (INDEX(x,0,1)-INDEX(x,2,1))*1440,
z, XMATCH(5,y,1),
INDEX(x,z,2)))
F5: (Date next to last): =IF(COUNTIFS(Table1[Date/Time],">="&E5,Table1[Date/Time],"<" & EDATE(E5,1))=0,"",
LET(x,FILTER(Table1[Date/Time],(Table1[Date/Time]>=E5)*(Table1[Date/Time]<EDATE(E5,1))),
INDEX(x,COUNT(x)-1)))
G5: (Pressure next to last):=IF(F5="","",
LET(x,FILTER(Table1,(Table1[Date/Time]>=E5)*(Table1[Date/Time]<EDATE(E5,1))),
INDEX(x,COUNT(INDEX(x,0,1))-1,2)))

https://stackoverflow.com/questions/70769294
复制相似问题