用电源查询填补时间空白 [英] fill time gaps with power query

问题描述

我有以下数据

   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.

这篇关于用电源查询填补时间空白的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！