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 ...
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
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?