Forum Discussion
karim2026
3 months agoRegular Visitor
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...
- 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
AlienSx
3 months agoSuper User
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
- karim20263 months agoRegular Visitor
It worked like a charm with a large set of data and the code is very compact and clear!
Thank you very much!