Aggregate column in Power Query

[Juan Sombrero]

Hi all,

i have a table like this one

[TABLE="width: 161"]
<colgroup><col style="width: 48pt;" width="64"> <col style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;" width="86"> <col style="width: 48pt;" width="64"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Date[/TD]
[TD="width: 86, bgcolor: transparent"]Fruit[/TD]
[TD="width: 64, bgcolor: transparent"]Amount[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1/mei[/TD]
[TD="bgcolor: transparent"]Apples[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1/mei[/TD]
[TD="bgcolor: transparent"]Oranges[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]2/mei[/TD]
[TD="bgcolor: transparent"]Apples[/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]2/mei[/TD]
[TD="bgcolor: transparent"]Strawberries[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]3/mei[/TD]
[TD="bgcolor: transparent"]Apples[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]3/mei[/TD]
[TD="bgcolor: transparent"]Oranges[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

And now I want to add a column with the sum of all apples (21) behind every apples row. Same for the other fruits, so both Orange elines should equal 5.

Any M-code to realize this? Or even better a UI solution?

Thankx

 

[billszysz]

This is one of possible way

let
    Source = Excel.CurrentWorkbook(){[Name="Tabela2"]}[Content],
    ChType = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Fruit", type text}, {"Amount", Int64.Type}}),
    ToMerge = Table.Group(ChType, {"Fruit"}, {{"Sum", each List.Sum([Amount]), type number}}),
    ChType_mer_ToMerge = Table.NestedJoin(ChType,{"Fruit"},ToMerge,{"Fruit"},"Tbl"),
    Expand = Table.ExpandTableColumn(ChType_mer_ToMerge, "Tbl", {"Sum"}, {"Sum"})
in
    Expand

Regards