Forum Discussion
Add sub totals for certian columns in large table
Hi guys
I have a large table in Power Query. It list products that are sold and each products has variants. In this case sizes. I would like to have my "sold" column to be summarized in the bottom for each style. But had a difficulties finding a solution.
This is how it looks now:
| Modell | Size | Sold | Bought | Stock |
| Product A | A | 2 | 0 | 0 |
| Product A | B | 7 | 0 | 0 |
| Product A | C | 12 | 0 | 0 |
| Product A | D | 18 | 0 | 0 |
| Product A | E | 17 | 0 | 0 |
| Product A | F | 12 | 0 | 0 |
| Product A | G | 5 | 0 | 0 |
| Product A | H | 3 | 0 | 0 |
| Product B | A | 1 | 0 | 0 |
| Product B | B | 7 | 0 | 0 |
| Product B | C | 11 | 0 | 0 |
| Product B | D | 15 | 0 | 0 |
| Product B | E | 14 | 0 | 0 |
| Product B | F | 10 | 0 | 0 |
| Product B | G | 4 | 0 | 0 |
| Product B | H | 1 | 0 | 0 |
| Product C | A | 2 | 0 | 19 |
| Product C | B | 4 | 0 | 77 |
| Product C | C | 7 | 0 | 124 |
| Product C | D | 8 | 0 | 64 |
| Product C | E | 6 | 0 | 155 |
| Product C | F | 2 | 0 | 52 |
My hope is to have it look like this
| Modell | Size | Sold | Bought | Stock |
| Product A | A | 2 | 0 | 0 |
| Product A | B | 7 | 0 | 0 |
| Product A | C | 12 | 0 | 0 |
| Product A | D | 18 | 0 | 0 |
| Product A | E | 17 | 0 | 0 |
| Product A | F | 12 | 0 | 0 |
| Product A | G | 5 | 0 | 0 |
| Product A | H | 3 | 0 | 0 |
| Total | 76 | |||
| Product B | A | 1 | 0 | 0 |
| Product B | B | 7 | 0 | 0 |
| Product B | C | 11 | 0 | 0 |
| Product B | D | 15 | 0 | 0 |
| Product B | E | 14 | 0 | 0 |
| Product B | F | 10 | 0 | 0 |
| Product B | G | 4 | 0 | 0 |
| Product B | H | 1 | 0 | 0 |
| Total | 63 | |||
| Product C | A | 2 | 0 | 19 |
| Product C | B | 4 | 0 | 77 |
| Product C | C | 7 | 0 | 124 |
| Product C | D | 8 | 0 | 64 |
| Product C | E | 6 | 0 | 155 |
| Product C | F | 2 | 0 | 52 |
| Total | 29 |
Is it possible?
Hi Syndicate_Admin ,
You can try this query in excel power query editor for the source table, it is possible to achieve that but due to the 'null' value in other columns, the sort of size for each product could be different by default.
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Modell", type text}, {"Size", type text}, {"Sold", Int64.Type}, {"Bought", Int64.Type}, {"Stock", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Modell"}, {{"Sold", each List.Sum([Sold]), type number}}), Append = Table.Combine({#"Changed Type",#"Grouped Rows"}), #"Sorted Rows" = Table.Sort(Append,{{"Modell", Order.Ascending}, {"Sold", Order.Ascending}}), #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows",each [Modell],each if [Size] = null and [Bought] = null and [Stock] = null then "Total" else [Modell],Replacer.ReplaceText,{"Modell"}) in #"Replaced Value"Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Syndicate_Admin
You can do it in Power BI using a table or matrix visual. Is there any reason why you need a Power Query solution?Hi, yes. I dont have access to Power BI, as of now i only have excel. It will change in the future tho.
Hello - three different ways to accomplish this in Power Query are described in this article. https://ssbi-blog.de/blog/technical-topics-english/3-ways-for-sums-over-columns-in-power-query/
I have tried this, but when i did the only sum i got was an total of all products and all sizes combined. And that is not what i need. I rly need to have them summarized product by product. And not all together.
Syndicate_Admin Why not just append just the summarized rows back to the original table and call it a day? So, start with the original table as a query. Reference this query and implement your grouping/totalling. Then Append this second table to the first table.
Hi Syndicate_Admin ,
You can try this query in excel power query editor for the source table, it is possible to achieve that but due to the 'null' value in other columns, the sort of size for each product could be different by default.
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Modell", type text}, {"Size", type text}, {"Sold", Int64.Type}, {"Bought", Int64.Type}, {"Stock", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Modell"}, {{"Sold", each List.Sum([Sold]), type number}}), Append = Table.Combine({#"Changed Type",#"Grouped Rows"}), #"Sorted Rows" = Table.Sort(Append,{{"Modell", Order.Ascending}, {"Sold", Order.Ascending}}), #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows",each [Modell],each if [Size] = null and [Bought] = null and [Stock] = null then "Total" else [Modell],Replacer.ReplaceText,{"Modell"}) in #"Replaced Value"Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
