Table.InsertRows - expand the columns dynamically

Power Query
1

Hello,
I was wondering if you could help me out with the following challenge.

The following steps have been done:

  1. Source data uploaded to power query.

image

  1. Formatting.
  2. 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])]}
  3. Summary record was added to the table:
    = Table.InsertRows(ChType, 0, Summary)

image

So far so good :slight_smile:

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

2

@mno Table_InsertRows.xlsx (25.0 KB)

image
image1210×319 16.8 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
3

I understand that there is no way to do this without unpivoting the table?

Brilliant solution anyway! :+1:
Thank you
Mariusz

4

Hi @mno

I know it’s already solved. Just tried

TableTotalRow

2 Likes
5

Thank you Rajesh.
I know that solution, but your presentation is very good :+1:

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