我有以下数据
start stop status +-----------+-----------+-----------+ | 09:01:10 | 09:01:40 | active | | 09:02:30 | 09:04:50 | active | | 09:10:01 | 09:11:50 | active | +-----------+-----------+-----------+我想用被动"来填补空白
I want to fill in the gaps with "passive"
start stop status +-----------+-----------+-----------+ | 09:01:10 | 09:01:40 | active | | 09:01:40 | 09:02:30 | passive | | 09:02:30 | 09:04:50 | active | | 09:04:50 | 09:10:01 | passive | | 09:10:01 | 09:11:50 | active | +-----------+-----------+-----------+我怎样才能用 M Query 语言做到这一点?
How can I do this in M Query language?
推荐答案我想我可能有更好的解决方案.
I think I may have a better performing solution.
从您的源表(假设它已排序)中,添加从 0 开始的索引列和从 1 开始的索引列,然后将表与自身合并,执行索引列上的左外连接并展开 start 列.
From your source table (assuming it's sorted), add an index column starting from 0 and an index column starting from 1 and then merge the table with itself doing a left outer join on the index columns and expand the start column.
删除除stop、status 和start.1 之外的列并过滤掉空值.
Remove columns except for stop, status, and start.1 and filter out nulls.
将列重命名为 start、status 和 stop 并将 "active" 替换为 "被动".
Rename columns to start, status, and stop and replace "active" with "passive".
最后,将此表附加到您的原始表中.
Finally, append this table to your original table.
let Source = Table.RenameColumns(#"Removed Columns",{{"Column1.2", "start"}, {"Column1.3", "stop"}, {"Column1.4", "status"}}), Add1Index = Table.AddIndexColumn(Source, "Index", 1, 1), Add0Index = Table.AddIndexColumn(Add1Index, "Index.1", 0, 1), SelfMerge = Table.NestedJoin(Add0Index,{"Index"},Add0Index,{"Index.1"},"Added Index1",JoinKind.LeftOuter), ExpandStart1 = Table.ExpandTableColumn(SelfMerge, "Added Index1", {"start"}, {"start.1"}), RemoveCols = Table.RemoveColumns(ExpandStart1,{"start", "Index", "Index.1"}), FilterNulls = Table.SelectRows(RemoveCols, each ([start.1] <> null)), RenameCols = Table.RenameColumns(FilterNulls,{{"stop", "start"}, {"start.1", "stop"}}), ActiveToPassive = Table.ReplaceValue(RenameCols,"active","passive",Replacer.ReplaceText,{"status"}), AppendQuery = Table.Combine({Source, ActiveToPassive}), #"Sorted Rows" = Table.Sort(AppendQuery,{{"start", Order.Ascending}}) in #"Sorted Rows"这应该是 O(n) 复杂性,具有与 @chillin 相似的逻辑,但我认为应该比使用自定义函数更快,因为它将使用内置函数-in 合并,可能会高度优化.
This should be O(n) complexity with similar logic to @chillin, but I think should be faster than using a custom function since it will be using a built-in merge which is likely to be highly optimized.
更多推荐
用电源查询填补时间空白
发布评论