Hi Ron,
after looking at it again, I do agree with you that in terms of performance (and transparency) it does need an improvement.
Actually in my previous post, the bit which was part of the function (which represents the second argument passed to the Table.InsertRows function)
Code:
{Record.TransformFields(T{0},
List.Zip({
Record.FieldNames(T{0}),
List.Repeat({(_)=> null},
Record.FieldCount(T{0}))
})
)}
could be taken out completely of the function since it would essentially be always a list containing one single record whose fields are all to be blank. This would save us from asking PQ to recalculate it for each row when the Table.TransformColumns is called.
Thus, also in your clearer version, I would take the below bit out of the function:
Code:
InsertionPoint = Table.RowCount(TblRef),
ColHdgs = Record.FieldNames(TblRef{0}),
ColCount = Record.FieldCount(TblRef{0}),
NewRowVals = List.Repeat({null}, ColCount),
NewRowToAdd = Record.FromList(NewRowVals,ColHdgs)
and do something like (untested and potentially buggy):
Code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
InsertionPoint = Table.RowCount(Source),
ColHdgs = Record.FieldNames(Source {0}),
ColCount = Record.FieldCount(Source {0}),
NewRowVals = List.Repeat({null}, ColCount),
NewRowToAdd = Record.FromList(NewRowVals,ColHdgs),
fnInsertNullRow = (TblRef as table) as table =>
let
TblWithBlankRow = Table.InsertRows(TblRef,
InsertionPoint,
{NewRowToAdd})
in
TblWithBlankRow,
Grp = Table.Group(Source, {"Name"}, {{"Tbl", each _, type table}}, GroupKind.Global),
TransformCol = Table.TransformColumns(Table.SelectColumns(Grp, {"Tbl"}), {{"Tbl", fnInsertNullRow}}),
FinalResult = Table.ExpandTableColumn(TransformCol, "Tbl", Table.ColumnNames(TransformCol{0}[Tbl]))
in
FinalResult
PS. The final touch could be to turn the whole thing into function with 2 paramenters. Paramter 1: the table on which we want to add an empty row, Parameter2: a list (or single text value) representing the column name(s) used to perform the GroupBy when a new empty row is to be added
Like this thread? Share it with others