cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

## Adding new row with subtotals to the table.

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

1 ACCEPTED SOLUTION
Solution Sage

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:

Solution Sage

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:

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors