用电源查询填补时间空白

本文介绍了用电源查询填补时间空白的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有以下数据

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.