1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

power query how to Group Text together and sum total

Discussion in 'Power Pivot, Power Map etc' started by Oscarr, Apr 12, 2018.

  1. Oscarr

    Oscarr Member

    Messages:
    75
    Hi all

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

    pls refer to attached file.

    Thanks

    Attached Files:

  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,428
    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.
    Code (vb):
    =Text.Combine([Table1][detail],"*")
    5. Remove merged column and reorder column as needed.

    See attached.

    Attached Files:

    Oscarr and Thomas Kuriakose like this.
  3. GraH - Guido

    GraH - Guido Active Member

    Messages:
    521
    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
    Code (vb):

    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"
     

    Attached Files:

    Oscarr likes this.
  4. Oscarr

    Oscarr Member

    Messages:
    75

Share This Page