CODEWITHSUNDEEP
excelgroupingpowerquerysubtotal
user11345588
user11345588

Reputation: 79

How to add totals row to excel power query?

I have produced a table like the one below by using 'group' function in excel power query

score 1 score 2 score 3
A   6   25  50
B   8   30  20
C   15  15  30
D   20  0   10

I want to add a totals row (equivalent to "show totals for column" in a normal pivot table), so result would be like this

score 1 score 2 score 3
A   6   25  50
B   8   30  20
C   15  15  30
D   20  0   10
Total 49    70      110

Anyone knows if there is a simple way to do this? Thank you, RY

Upvotes: 4

Views: 20631

Answers (3)

Brad
Brad

Reputation: 1

If you are wanting to add a total row to a Power Query that was loaded to a table. Just skip a row and add a total row. This seems to work for me. Just make sure you lock the first cell in your sum formulas. ex =SUM($C$2:C25)

Example 1

1

Example 2

2

Upvotes: -2

Aleksei Zhigulin
Aleksei Zhigulin

Reputation: 1634

Another way:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    group = Table.Group(Source, {}, {{"letter", each "Total"},
                                     {"score 1", each List.Sum([score 1])},
                                     {"score 2", each List.Sum([score 2])}, 
                                     {"score 3", each List.Sum([score 3])}}),
    append = Table.Combine({Source, group})
in
    append

Or:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    cols = Table.ColumnNames(Source),
    group = Table.Group(Source, {}, List.Zip({cols, {each "Total"}&
                                    List.Transform(List.Skip(cols), 
                                    (x)=>each List.Sum(Table.Column(_,x)))})),
    append = Table.Combine({Source, group})
in
    append

Or:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    group = Table.Group(Source, {}, List.TransformMany(Table.ColumnNames(Source),
                                    (x)=>{each if x = "letter" then "Total" 
                                    else List.Sum(Table.Column(_,x))}, (x,y)=>{x,y})),
    append = Table.Combine({Source, group})
in
    append

Upvotes: 9

will1329
will1329

Reputation: 173

I don't know why you would do this in PowerQuery rather than a pivottable but the only way I can think of is to duplicate the table, unpivot the columns and then repivot it using sum as an aggregation. Then you could append the table to your orginal query.

Your code would look something like for the table you want to create the totals in.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Attribute]), "Attribute", "Value", List.Sum)
in
    #"Pivoted Column"

Note you may have to choose to aggregate by count but you can change the code to sum (On the #"Pivot Column" line change List.Count to List.Sum.

You will get an error for the A,B,C,D col - you can replace this with Total if you would like by using the Replace Errors function.

Upvotes: 0

Related Questions