Inclue Total in a Table

[Anonymous]

Hello, 

I need make a Line with the total in a table, for example: 

I have this table that I insert in power query:

Category	Count1	Count2
A	1	1
B	2	1
C	3	1
D	4	1
E	5	1
F	6	1
G	7	1

I need a line in the end of the table, that contains the total and the sum of the colunms, for example:

Category	Count1	Count2
A	1	1
B	2	1
C	3	1
D	4	1
E	5	1
F	6	1
G	7	1
TOTAL	28	7

Anybody can help me? 

Thanks a lot.

[ToddChitt]

Duplicate your original table. Add a Custom Column that is equal to the literal value of "TOTAL". Do a Group By on this custom column and add SUM aggregations for the two columns. Make sure you name the resulting columns exactly the same as the original table.

Now simply UNION (Merge Append) the two tables.

Just curious: In the reporting side, you can build a visual table that does all this summing for you with a few mouse clicks. Why do you NEED the TOTAL INSIDE the table as one of the rows of the table?

[Anonymous]

Hello @ToddChitt 

I need this line with the total for export. 

This export will feed another Power BI.

[ToddChitt]

OK, but did my suggestion work?

One thing to note: If you have values that come AFTER "T", then they will show up BELOW the TOTAL line and it will look wierd. 

[jgordon11 · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIE41idaCUnIMsIznMGsozhPBcgywTOcwWyTOE8NyDLDM5zB7LMIbxYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Count1 = _t, Count2 = _t]),
    ChangeType = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Count1", Int64.Type}, {"Count2", Int64.Type}}),
    Total = {"Total", List.Sum(ChangeType[Count1]), List.Sum(ChangeType[Count2])},
    Result = Table.FromRows(Table.ToRows(ChangeType) & {Total}, Table.ColumnNames(ChangeType))
in
    Result

[Anonymous]

Hello @jgordon11 

In power query, where I put this code?

tks.