mno
1
Hello,
I was wondering if you could help me out with the following challenge.
The following steps have been done:
- Source data uploaded to power query.

- Formatting.
- Summary record was created:
= {[ Name = “Total”, Month1 = List.Sum(ChType[Month1]), Month2 = List.Sum(ChType[Month2]), Month3 = List.Sum(ChType[Month3]), Month4 = List.Sum(ChType[Month4])]}
- Summary record was added to the table:
= Table.InsertRows(ChType, 0, Summary)

So far so good 
Here is the challenge:
If new columns are added the formula breaks unless is modified manually.
Basic question, how to change the code in the step Summary so the record is extended dynamically with the same logic (List.Sum…).
You can find the example file attached.
Table_InsertRows.xlsx (18.2 KB)
Thank you
Mariusz
@mno Table_InsertRows.xlsx (25.0 KB)
let
Source = Excel.CurrentWorkbook(){[ Name = "FactData" ]}[Content],
ChType = Table.TransformColumnTypes (
Source,
{
{ "Name", type text },
{ "Month1", Int64.Type },
{ "Month2", Int64.Type },
{ "Month3", Int64.Type },
{ "Month4", Int64.Type }
}
),
UnpivotedOtherColumns = Table.UnpivotOtherColumns ( ChType, { "Name" }, "Month", "Value" ),
InsertTotalRow = Table.AddColumn (
UnpivotedOtherColumns,
"Custom",
( OT ) =>
// OT is Outer reference to UnpivotedOtherColumns
let
SameRows =
Table.SelectRows (
UnpivotedOtherColumns,
( IT ) => OT[Month] = IT[Month]
// IT is Inner reference to UnpivotedOtherColumns
),
AddTotal =
Table.InsertRows (
SameRows,
0,
{ [ Name = "Total", Month = OT[Month], Value = List.Sum ( SameRows[Value] ) ] }
)
in
AddTotal
),
RemovedOtherColumns = Table.SelectColumns ( InsertTotalRow, { "Custom" } ),
ExpandedCustom = Table.ExpandTableColumn (
RemovedOtherColumns,
"Custom",
{ "Name", "Month", "Value" },
{ "Name", "Month", "Value" }
),
PivotedColumn = Table.Pivot (
ExpandedCustom,
List.Distinct ( ExpandedCustom[Month] ),
"Month",
"Value",
List.Min
)
in
PivotedColumn
1 Like
mno
3
I understand that there is no way to do this without unpivoting the table?
Brilliant solution anyway! 
Thank you
Mariusz
Rajesh
4
Hi @mno
I know it’s already solved. Just tried

2 Likes
mno
5
Thank you Rajesh.
I know that solution, but your presentation is very good 
I wanted to explore a little more Table.InsertRows.
I thought there should be a way to add additional columns when they appear in the model.
Thank you
Mariusz
1 Like