Search code examples
powerquerym

fill time gaps with power query

I have following data

   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   |
+-----------+-----------+-----------+

How can I do this in M Query language?

Solution

  • I think I may have a better performing solution.

    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.

    Self Merge

    Remove columns except for stop, status, and start.1 and filter out nulls.

    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"

    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.