Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Irek
Helper II
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

IDSnaphot DateKey (Id + Snaphsot Date)Business Unit AllocationAllocation sum for induvidualFTEDiff FTE vs Allocation
1  a80110
1  b20110
2  c100110
3  d001-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
Jakinta
Solution Sage
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:

Jakinta_0-1680712621192.png

 

 

View solution in original post

1 REPLY 1
Jakinta
Solution Sage
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:

Jakinta_0-1680712621192.png

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors