Forum Discussion
Sum Rows according to month
Hi all,
I need some assistance:
I have a table in PowerQuery which contains 18 columns. The table is looking like the table below. The "...." columns are representing all other columns in the table.
What I want is, to sum up all "Act YTD Savings" & "FC Savings" for All Plants per Month in a new row and the sum should be added to the same table. I already managed to sum the values by Month using the "Group" function, but I am stuck with adding the sums to the original table with all 18 columns, as the grouped table is not matching all columns. All other columns are not relevant for "All plants" sum row. They can either stay empty or be filled with a random value.
| Date | Plant | .... | .... | ... | Act YTD Savings SUM | FC Savings Sum |
| 01.01.2022 | a | 5 | 5 | |||
| 01.01.2022 | b | 5 | 5 | |||
| 01.01.2022 | c | 5 | 5 | |||
| 01.01.2022 | d | 5 | 5 | |||
| 01.01.2022 | All Plants | 20 | 20 | |||
| 01.02.2022 | a | 6 | 3 | |||
| 01.02.2022 | b | 6 | 3 | |||
| 01.02.2022 | c | 6 | 3 | |||
| 01.02.2022 | d | 6 | 3 | |||
| 01.02.2022 | All Plants | 24 | 12 |
Your help is appreciated.
Thank you!
Hi uie79957 ,
While I agree a 100% with Fowmy, here still a solution in Power Query.
Before:
After:
The idea is to first do the group by and the sum calculations and afterwards append upon the query from the beginning. Make sure to name the new columns the same as the old ones so PQ can match them during the append. Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY/dCsAgCIVfJboeQ6399CzRRW3v/wzzRGODEDpK+nHUnD3xqk9IxDm/+KpiVUI+NGxdZfmTWmoqZCaE0wYvVQADV042eKsiGHyEJlBesI4Ww1Zgu/cZE9hGi2HbKwaIHXErw1aCDWJH3MroS/zA8gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Plant = _t, Col1 = _t, Col2 = _t, Col3 = _t, #"Act YTD Savings SUM" = _t, #"FC Savings Sum" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Plant", type text}, {"Col1", Int64.Type}, {"Col2", Int64.Type}, {"Col3", Int64.Type}, {"Act YTD Savings SUM", Int64.Type}, {"FC Savings Sum", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"Act YTD Savings SUM", each List.Sum([Act YTD Savings SUM]), type nullable number}, {"FC Savings Sum", each List.Sum([FC Savings Sum]), type nullable number}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Plant", each "All Plants"), #"Appended Query" = Table.Combine({#"Changed Type", #"Added Custom"}), #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Date", Order.Ascending}, {"Plant", Order.Descending}}) in #"Sorted Rows"Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
2 Replies
Hi uie79957 ,
While I agree a 100% with Fowmy, here still a solution in Power Query.
Before:
After:
The idea is to first do the group by and the sum calculations and afterwards append upon the query from the beginning. Make sure to name the new columns the same as the old ones so PQ can match them during the append. Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY/dCsAgCIVfJboeQ6399CzRRW3v/wzzRGODEDpK+nHUnD3xqk9IxDm/+KpiVUI+NGxdZfmTWmoqZCaE0wYvVQADV042eKsiGHyEJlBesI4Ww1Zgu/cZE9hGi2HbKwaIHXErw1aCDWJH3MroS/zA8gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Plant = _t, Col1 = _t, Col2 = _t, Col3 = _t, #"Act YTD Savings SUM" = _t, #"FC Savings Sum" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Plant", type text}, {"Col1", Int64.Type}, {"Col2", Int64.Type}, {"Col3", Int64.Type}, {"Act YTD Savings SUM", Int64.Type}, {"FC Savings Sum", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"Act YTD Savings SUM", each List.Sum([Act YTD Savings SUM]), type nullable number}, {"FC Savings Sum", each List.Sum([FC Savings Sum]), type nullable number}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Plant", each "All Plants"), #"Appended Query" = Table.Combine({#"Changed Type", #"Added Custom"}), #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Date", Order.Ascending}, {"Plant", Order.Descending}}) in #"Sorted Rows"Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
