Forum Discussion

karim2026's avatar
karim2026
Regular Visitor
4 months ago
Solved

Insert row for missing repeating sequence in a table

Hello

I have a time stamp table with a repeating sequence column (90, 91, 92, 93 and repeat), some of the sequences are missing.

I am trying to find the missing value and insert a new row with that value in the column using power query

any help would be appreciated

 

DateTime Machine# Mode Sequence
2026-01-05T07:23:00.261GSB0193
2026-01-05T07:23:39.479GSB0190
2026-01-05T07:25:03.896GSB0191
2026-01-05T07:37:14.519GSB0192
2026-01-05T07:40:43.126GSB0193
2026-01-06T09:49:12.376GSB0191
2026-01-06T10:00:01.121GSB0192
2026-01-06T10:02:01.825GSB0193
2026-01-06T10:12:41.143GSB0190
2026-01-06T10:14:41.883GSB0191
2026-01-06T10:26:42.770GSB0192
 
the mode sequence should be 90, 91, 92, 93 and repeats
 

I am trying to find the missing mode sequence and insert a row with the missing sequence value

 
DateTime Machine# Mode Sequence
2026-01-05T07:23:00.261GSB0193
2026-01-05T07:23:39.479GSB0190
2026-01-05T07:25:03.896GSB0191
2026-01-05T07:37:14.519GSB0192
2026-01-05T07:40:43.126GSB0193
  90
2026-01-06T09:49:12.376GSB0191
2026-01-06T10:00:01.121GSB0192
2026-01-06T10:02:01.825GSB0193
2026-01-06T10:12:41.143GSB0190
2026-01-06T10:14:41.883GSB0191
2026-01-06T10:26:42.770GSB0192

 

Thanks,

  • Hello, I would go over the rows of your table with List.Generate and calculate next sequence number each time with a help of Number.Mod. If sequence number is missing then generate a new row with datetime + 1 sec. I didn't know what to do with Machine# so I left it blank. 

    let
        Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
        dt_type = Table.TransformColumns(Source, {{"DateTime", DateTime.From}, {"Machine#", Text.From}, {"Mode Sequence", Int64.From}}),
        seq = {90, 91, 92, 93},
        fx = (optional x) => [
            go = if x = null then true else x[i] + 1 < List.Count(rows),
            s = if x = null then rows{0}{2} else x[s] + 1,
            si = Number.Mod(s - 90, 4),
            sr = seq{si},
            next_row = if x = null then true else rows{x[i] + 1}{2} = sr,
            i = if x = null then 0 else if next_row then x[i] + 1 else x[i],
            dt = if next_row then rows{i}{0} else x[dt] + #duration(0, 0, 0, 1)
        ],
        rows = List.Buffer(Table.ToList(dt_type, (x) => x)),
        gnr = List.Generate(fx, (x) => x[go], fx, (x) => if x[next_row] then rows{x[i]} else {x[dt], null, x[sr]}),
        tbl = Table.FromList(gnr, (x) => x, Value.Type(dt_type))
    in
        tbl

     

4 Replies

  • Hi karim2026, just to undersand do you want the inserted rows to have DateTime = null (as in your example), or do you want a calculated DateTime (for example, evenly distributed between the previous and the next timestamp)?

     

     

    • karim2026's avatar
      karim2026
      Regular Visitor

      Hi Zanqueta,

      i was going to do the previous value + 1 sec

      this was easy for me to do, the difficult part was to find missing sequence and insert row

      thanks,

  • Hello, I would go over the rows of your table with List.Generate and calculate next sequence number each time with a help of Number.Mod. If sequence number is missing then generate a new row with datetime + 1 sec. I didn't know what to do with Machine# so I left it blank. 

    let
        Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
        dt_type = Table.TransformColumns(Source, {{"DateTime", DateTime.From}, {"Machine#", Text.From}, {"Mode Sequence", Int64.From}}),
        seq = {90, 91, 92, 93},
        fx = (optional x) => [
            go = if x = null then true else x[i] + 1 < List.Count(rows),
            s = if x = null then rows{0}{2} else x[s] + 1,
            si = Number.Mod(s - 90, 4),
            sr = seq{si},
            next_row = if x = null then true else rows{x[i] + 1}{2} = sr,
            i = if x = null then 0 else if next_row then x[i] + 1 else x[i],
            dt = if next_row then rows{i}{0} else x[dt] + #duration(0, 0, 0, 1)
        ],
        rows = List.Buffer(Table.ToList(dt_type, (x) => x)),
        gnr = List.Generate(fx, (x) => x[go], fx, (x) => if x[next_row] then rows{x[i]} else {x[dt], null, x[sr]}),
        tbl = Table.FromList(gnr, (x) => x, Value.Type(dt_type))
    in
        tbl

     

    • karim2026's avatar
      karim2026
      Regular Visitor

      It worked like a charm with a large set of data and the code is very compact and clear!

      Thank you very much!