Forum Discussion

karim2026's avatar
karim2026
Regular Visitor
3 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 wit...
  • AlienSx's avatar
    3 months ago

    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