Power Query Sum of column by group as new column

2
votes

So I am new to power query and I just wasted over an hour looking for something that I can do easily in many other programs.

I just want to create a new column summing up another column. FOr instance, to check if the percentage a correct and if not normalize therafter. I dont want to group by and reduce the table.

enter image description here

I ve been searching left and right and tried to add a new column like "Group Sum" using stuff like = list.sum([Number]) = Calculate(SUM([Number])

just to get the the total sum of all entries 200. No success.

Maybe its me, but I really dont see the logic.

I now tried

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
        #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Group", type text}, {"Gender", type text}, {"Number", Int64.Type}, {"Group Sum", Int64.Type}, {"Spalte1", Int64.Type}})
        #"Added Custom" = Table.AddColumn(#"Geänderter Typ","Group Sum",(i)=>List.Sum(Table.SelectRows(#"Geänderter Typ", each [Group]=i[Group])[Number]), type number )
    in
        #"Geänderter Typ"

which results in an error and

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Group", type text}, {"Gender", type text}, {"Number", Int64.Type}, {"Group Sum", Int64.Type}}),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Geänderter Typ", "Benutzerdefiniert", each Table.Group(Quelle, {"Group"}, {{"Group Sum", each List.Sum([Number]), type nullable number}}))
in
    #"Hinzugefügte benutzerdefinierte Spalte"

Which gives me a new column where all entries say "Table"

3
excelpowerquery
Easiest ,,, Group on column [Group] and sum column [Number]. Merge that back into original table using left outer join (the default) and expand using arrows atop the new column – horseyride
First step is easy, where do I find the left outer join? Do i need to save the group by result in a different table in between? – Max M
I just want to note here this example is confusing because the group sums are the same for both groups. It means you might think something is working when it isn't. I believe that's the case in at least one of the answers below, where it uses Table.Distinct() in part and that works only by coincidence because the group sums are the same, and so if you "pick one group sum and apply it to all groups" it looks like it's working. – Mark E.

3 Answers

1
votes

try

let Quelle= Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(Quelle, [PromoteAllScalars=true]),
#"Geänderter Typ" = Table.TransformColumnTypes(#"Promoted Headers",{{"Group", type text}, {"Gender", type text}, {"Number", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Geänderter Typ","Group Sum2",(i)=>List.Sum(Table.SelectRows(#"Geänderter Typ", each [Group]=i[Group]) [Number]), type number )
in #"Added Custom"
1
votes

Here are two other options. The examples assume your source table is named Table1. Here's how mine looks at its source in Excel:

enter image description here

Note it does not have a Group Sum column. The query will derive that.

Option 1.

Click Add Column then Custom Column and fill out the screen like this and click OK:

enter image description here

You should see a table like this:

enter image description here

Then just click the table in the first row of the Custom column and you should get a table that looks like this:

enter image description here

Then you can merge this new table with the original source table (Table1). Click Home > Merge Queries and fill out the information for the merge like this and click OK. (Note that the same query "Table1" is being merged to itself at this point, and only the Group column is selected for each entry.)

enter image description here

You should see a table like this:

enter image description here

Then, in the formula bar above that table, where you see = Table.NestedJoin(Custom, {"Group"}, Custom, {"Group"}, "Custom", JoinKind.LeftOuter), change the first instance of Custom to Source, so the line reads = Table.NestedJoin(Source, {"Group"}, Custom, {"Group"}, "Custom", JoinKind.LeftOuter) instead.

That is, change it from:

enter image description here

To:

enter image description here

Then expand the new Custom column by clicking the enter image description here button, only selecting the Group Sum column, clearing the checkbox beside "Use original column name as prefix," and clicking OK:

enter image description here

You should get this result:

enter image description here

Here's the M code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}, {"Gender", type text}, {"Number", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.Group(Source, {"Group"}, {{"Group Sum", each List.Sum([Number]), type nullable number}})),
    Custom = #"Added Custom"{0}[Custom],
    #"Merged Queries" = Table.NestedJoin(Source, {"Group"}, Custom, {"Group"}, "Custom", JoinKind.LeftOuter),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Merged Queries", "Custom", {"Group Sum"}, {"Group Sum"})
in
    #"Expanded Custom"

(You can replace Table1, Source and Changed Type with Tablelle1, Quelle, and #"Geänderter Typ", respectively throughout the code above to align with Max's language.)

Option 2.

Click Transform then Group By and fill out the screen like this and click OK:

enter image description here

Then expand the AllData column with only the Gender and Number columns selected like this:

enter image description here

The result:

enter image description here

Here's the M code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Group"}, {{"AllData", each _, type table [Group=text, Gender=text, Number=number]}, {"Group Sum", each List.Sum([Number]), type number}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Gender", "Number"}, {"Gender", "Number"})
in
    #"Expanded AllData"
0
votes

Group and Join Method

I have now seen a few ways to do this, but I think the most efficient is probably a group-and-join approach that builds on previous comments and answers here. It takes one line:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.Join(Source, "Group", Table.Group(Source,{"Group"},{{"Group Sum", each List.Sum([Number]), type nullable number}}), "Group")
in
    #"Added Custom"

The Table.Group() part of this creates a table with each unique value of the grouping variable ("Group" here) and, for each of those unique values, its summary value (the sum of [Number] for all rows with the same "Group" value here). To attach these summary values onto the original table becomes the job for Table.Join(). The Table.Join() function gets four input arguments: 1.) the original table, 2.) the grouping column in the original table ("Group" here), 3.) the summary table (that's the output of the Table.Group() function here) and 4.) the grouping column in summary table (also "Group" here).

I tested this and get the results as shown:

enter image description here

Note: I changed Number column values from the question to show that the code is working. In the example provided in the original question, the Group Sum is 100 for both groups, and that seems to make the approach suggested in another answer look like it's working when it does not.