Sum Rows according to month

[uie79957]

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!

[tackytechtom · Accepted solution]

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/

[Fowmy]

@uie79957 

Power Query is meant for data transformation, grouping etc. You easily achieve this in Power BI using Matrix visual where you will get the subtotal.