Insert row for missing repeating sequence in a table

[karim2026]

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.261	GSB01	93
2026-01-05T07:23:39.479	GSB01	90
2026-01-05T07:25:03.896	GSB01	91
2026-01-05T07:37:14.519	GSB01	92
2026-01-05T07:40:43.126	GSB01	93
2026-01-06T09:49:12.376	GSB01	91
2026-01-06T10:00:01.121	GSB01	92
2026-01-06T10:02:01.825	GSB01	93
2026-01-06T10:12:41.143	GSB01	90
2026-01-06T10:14:41.883	GSB01	91
2026-01-06T10:26:42.770	GSB01	92

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.261	GSB01	93
2026-01-05T07:23:39.479	GSB01	90
2026-01-05T07:25:03.896	GSB01	91
2026-01-05T07:37:14.519	GSB01	92
2026-01-05T07:40:43.126	GSB01	93
		90
2026-01-06T09:49:12.376	GSB01	91
2026-01-06T10:00:01.121	GSB01	92
2026-01-06T10:02:01.825	GSB01	93
2026-01-06T10:12:41.143	GSB01	90
2026-01-06T10:14:41.883	GSB01	91
2026-01-06T10:26:42.770	GSB01	92

Thanks,

[AlienSx · Accepted solution]

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]

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

Thank you very much!

[Zanqueta]

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]

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,