power query how to Group Text together and sum total

[Oscarr]

Hi all

anyone know how to use power query to group that text and sum total?

pls refer to attached file.

Thanks

 

[Chihiro]

Here's one way to do it.

1. Query Table1, and make copy of that query.
2. Group by Name column and sum deposit & sell columns in copied query.
3. Merge Table1 into copied table using Name column as key.
4. Add custom column with following formula.

=Text.Combine([Table1][detail],"*")

5. Remove merged column and reorder column as needed.

See attached.

 

[GraH - Guido]

Hi Chihiro,
If you don't mind me saying, I believe it can be done in a simpler way: no need to duplicate and merge. Or... Another way is :
Just group by
add 3 aggregations: sum Deposit; sum Sell and "all rows"
From the "all rows" table, extract the list via your formula in a custom column.
delete this "table" column and shuffle columns in correct order
save and load

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"detail", type text}, {"deposit", Int64.Type}, {"sell", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Deposit", each List.Sum([deposit]), type number}, {"Sell", each List.Sum([sell]), type number}, {"KeepTable", each _, type table}}),
    AddedList = Table.AddColumn(#"Grouped Rows", "List Details", each Text.Combine([KeepTable][detail],"*")),
    #"Removed Columns" = Table.RemoveColumns(AddedList,{"KeepTable"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Name", "List Details", "Deposit", "Sell"})
in
    #"Reordered Columns"

 

[Oscarr]

Hi @Chihiro @GraH - Guido

Thanks for your help...problem solve..

 

[p45cal]

30+ months later… and it can be even shorter:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    GroupedRows = Table.Group(Source, {"Name"}, {{"detail", each Text.Combine([detail],"*")},{"deposit", each List.Sum([deposit]), type nullable number}, {"sell", each List.Sum([sell]), type nullable number}})
in
    GroupedRows