Forum Discussion
Inserting Rows at given postion in table
Just thought this may be of interest to someone,
to insert rows at given interals in a tablle, here I have used a list for easy illustration, but just convert table
to records, and create records to insert in place of null, the principle is the same , here inserting every 3 rows.
[
Source = {"A".."Z"} ,
pos = List.Transform({0..25},(x)=> Number.IntegerDivide( x, 3)) ,
result = List.Generate( ()=>
[ x = 0 , y = Source {0} , z = pos {0}, i = {y} ] ,
each [x] < List.Count( Source) ,
each [ x = [x] + 1, y = Source {x} , z = pos {x} , i = if [z] = z then {y} else { null, y } ] ,
each [i] ) ,
xp = List.Combine( result )
]
Richard.
Hi Dicken
Add null item every 3 items in a list
let
Source = {"A".."Z"},
Result = List.Combine(List.Transform(List.Split(Source, 3), each _ & {null}))
in
ResultAdd null record every 3 rows in a table
let
Source = Table.FromColumns({{"A".."Z"}, {1..26}}),
Null_Table = Table.Buffer(Table.FromColumns({{null}}, List.FirstN(Table.ColumnNames(Source),1))),
Result = Table.Combine(List.Transform(Table.Split(Source, 3), each _ & Null_Table))
in
ResultStéphane
4 Replies
Hi Dicken
Add null item every 3 items in a list
let
Source = {"A".."Z"},
Result = List.Combine(List.Transform(List.Split(Source, 3), each _ & {null}))
in
ResultAdd null record every 3 rows in a table
let
Source = Table.FromColumns({{"A".."Z"}, {1..26}}),
Null_Table = Table.Buffer(Table.FromColumns({{null}}, List.FirstN(Table.ColumnNames(Source),1))),
Result = Table.Combine(List.Transform(Table.Split(Source, 3), each _ & Null_Table))
in
ResultStéphane
let
// Step 1: Create a list from A to Z
Source = {"A".."Z"},
// Step 2: Determine group positions (0-based, every 3 items)
pos = List.Transform({0..25}, (x) => Number.IntegerDivide(x, 3)),// Step 3: Generate a new list inserting nulls every 3 rows
result = List.Generate(
()=> [x=0, y=Source{0}, z=pos{0}, i={y}],
each [x] < List.Count(Source),
each [
x = [x] + 1,
y = Source{x},
z = pos{x},
i = if [z] = z then {y} else {null, y}
],
each [i]
),
// Step 4: Flatten the list of lists into one
xp = List.Combine(result)
in
xp
Example Output (first few items):
"A", "B", "C", null, "D", "E", "F", null, "G", "H", "I", null, ...
Perfectly adds anullafter every 3 items!Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
thank you for you replies, I did like the list transform, method as quite straightforward,
one thing about my version is it neednt be regular, so it could be a { 5, 11, 12, 23 .., whatever,
or use an actual table column and indert at change if x[Item] = y [Item] then {y} else { null, y] .
Thanks of help and suggestions.
RD
