Forum Discussion
Inclue Total in a Table
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.
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
5 Replies
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 ResultDuplicate 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?
