Adding new row with subtotals to the table.

[Irek]

Hello everyone, 

I have issue to solve for which I can't find solution. 

I have table like these

ID	Snaphot Date	Key (Id + Snaphsot Date)	Business Unit	Allocation	Allocation sum for induvidual	FTE	Diff FTE vs Allocation
1			a	80	1	1	0
1			b	20	1	1	0
2			c	100	1	1	0
3			d	0	0	1	-1

I need to add row (maybe there is better solution for such issue, I'm open for suggestions) with new Business Unit called Not Allocated, these new rows should show sub totals of allocation, sum for indyvidula, fte and allocation for each key Id. 

Thank you in advance for help

Marcin 

[Jakinta · Accepted solution]

You can try with steps below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVKA4kQgtjAAEoZQbKAUq4OqJAmIjTCVGCEpSQbJGGCqMUZSkwIWhWCQCl1DpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Snaphot Date" = _t, #"Key (Id + Snaphsot Date)" = _t, #"Business Unit" = _t, Allocation = _t, #"Allocation sum for induvidual" = _t, FTE = _t, #"Diff FTE vs Allocation" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Allocation", Int64.Type}, {"Allocation sum for induvidual", Int64.Type}, {"FTE", Int64.Type}, {"Diff FTE vs Allocation", Int64.Type}}),
    #"Grouped Rows" = Table.Combine ( Table.Group(#"Changed Type", {"ID"}, {{"A", each 
            let Transposed1 = Table.Transpose(_),
                Totals = Table.AddColumn(Transposed1, "Custom", each try List.Sum(Record.ToList(_)) otherwise ""),
                Transposed2 = Table.RenameColumns (Table.Transpose(Totals), 
                List.Zip ({Table.ColumnNames(Table.Transpose(Totals)), Table.ColumnNames(_)})) 
            in  
            Table.ReplaceValue(Transposed2,"","Not Allocated",Replacer.ReplaceValue, 
            List.Select( Table.ColumnNames( Transposed2), each _="Business Unit")), type 
            table}})[A])
in
    #"Grouped Rows"

The result: