locked
Group by (combining two columns) RRS feed

  • Question

  • I have the following table:




    ID Award Year
    A Aw1 2003
    A Aw2 2003
    B Aw3 2003
    A Aw4 2004
    C Aw5 2005
    B Aw6 2006



















    I would like the below output using only Group by. I would like to not have any additional "helper" columns added in the initial table or the Grouped table.

    ID Combined
    ID Combined
    A Aw1 2003, Aw2 2003, Aw4 2004
    B Aw3 2003, Aw6 2006
    C Aw5 2005







    Is it possible to use Text.Combine or List.Combine or ampersand (&) operator to achieve the output?

    Regards
    Sam





    • Edited by SamNaik Wednesday, June 26, 2019 9:55 PM Table was incorrect
    • Changed type Imke FeldmannMVP Sunday, July 7, 2019 5:13 AM
    Wednesday, June 26, 2019 9:53 PM

Answers

  • Sure you can. You just need to write some M code manually.
    There are actually 3 steps:

    1. make sure your columns Award and Year are type text (type text is mandatory for step2)
    2. concatene Award and Year
    3. combine all of them.

    Below I exploded the 3 steps in a let statement, I find it easier to understand, to read, to amend and to debug.
    note that step1 could easily be done before the Group, but I included in on learning purpose.

    let
        Source = Excel.CurrentWorkbook(){[Name="TableGroupByCombiningColumns"]}[Content],
        Group = Table.Group(#"Source", {"D"}, {
            {"Combined", each 
                let
                    step1 = Table.TransformColumnTypes(_, {{"Year", type text}, {"Award", type text}} ),
                    step2 = Table.CombineColumns(step1, {"Award", "Year"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), "merged"),
                    step3 = Combiner.CombineTextByDelimiter(", ") (step2[merged])
                    in step3,
                type text
            }
        })
    in #"Group"



    • Edited by anthony34 Thursday, June 27, 2019 6:44 AM
    • Proposed as answer by anthony34 Sunday, July 7, 2019 11:33 AM
    • Marked as answer by SamNaik Monday, July 8, 2019 4:43 AM
    Thursday, June 27, 2019 5:49 AM
  • of course, you can also make it in a 1 row code, but very unfriendly to read such as below.

    note that it is the exact same code, it will not run any quicker (afaik).

    let
        Source = Excel.CurrentWorkbook(){[Name="TableGroupByCombiningColumns"]}[Content],
        ChType = Table.TransformColumnTypes(#"Source", {{"D", type text}, {"Award", type text}, {"Year", type text}}),
        Group = Table.Group(ChType, {"D"}, {{"Combined", each Combiner.CombineTextByDelimiter(", ") (Table.CombineColumns(_, {"Award", "Year"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), "merged")[merged]), type text}})
    in #"Group

    or even

    let
        Source = Excel.CurrentWorkbook(){[Name="TableGroupByCombiningColumns"]}[Content],
        Group = Table.Group(#"Source", {"D"}, {{"Combined", each Combiner.CombineTextByDelimiter(", ") (Table.CombineColumns(Table.TransformColumnTypes(_, {{"Year", type text}, {"Award", type text}}, "en-US"), {"Award", "Year"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), "merged")[merged]), type text}})
    in #"Group"


    • Edited by anthony34 Thursday, June 27, 2019 6:07 AM
    • Marked as answer by SamNaik Monday, July 8, 2019 4:43 AM
    Thursday, June 27, 2019 6:01 AM
  • Alternate solution
    I didn't check which one is faster.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Grouped Rows" = Table.Group(Source, {"D"}, {{"Combined", each Text.Combine(Table.AddColumn(_[[Award],[Year]], "temp", each [Award] & " " & Text.From([Year]) )[temp], ","), type text}  })
    in
        #"Grouped Rows"

    • Marked as answer by SamNaik Monday, July 8, 2019 4:43 AM
    Thursday, June 27, 2019 8:45 AM
  • _ represents the table, and [[x],[y]] stands to keep only the columns x and y (like Table.SelectColumns)

    The 2 following codes are the same:

    let
        Source = Table.FromColumns(  {{1},{2}, {3}, {4}}, {"w", "x", "y", "z"} ),
        SelCol = Table.SelectColumns(Source,{"x", "y"})
    in #"SelCol"
    
    
    let
        Source = Table.FromColumns(  {{1},{2}, {3}, {4}}, {"w", "x", "y", "z"} ),
        SelCol = Source [[x],[y]]
    in #"SelCol"

    the syntax [[x],[y]] generates a table
    the syntaxe [y] generates a list


    • Marked as answer by SamNaik Monday, July 8, 2019 4:43 AM
    • Edited by anthony34 Thursday, November 7, 2019 4:32 PM
    Thursday, July 4, 2019 10:19 AM

All replies

  • Sure you can. You just need to write some M code manually.
    There are actually 3 steps:

    1. make sure your columns Award and Year are type text (type text is mandatory for step2)
    2. concatene Award and Year
    3. combine all of them.

    Below I exploded the 3 steps in a let statement, I find it easier to understand, to read, to amend and to debug.
    note that step1 could easily be done before the Group, but I included in on learning purpose.

    let
        Source = Excel.CurrentWorkbook(){[Name="TableGroupByCombiningColumns"]}[Content],
        Group = Table.Group(#"Source", {"D"}, {
            {"Combined", each 
                let
                    step1 = Table.TransformColumnTypes(_, {{"Year", type text}, {"Award", type text}} ),
                    step2 = Table.CombineColumns(step1, {"Award", "Year"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), "merged"),
                    step3 = Combiner.CombineTextByDelimiter(", ") (step2[merged])
                    in step3,
                type text
            }
        })
    in #"Group"



    • Edited by anthony34 Thursday, June 27, 2019 6:44 AM
    • Proposed as answer by anthony34 Sunday, July 7, 2019 11:33 AM
    • Marked as answer by SamNaik Monday, July 8, 2019 4:43 AM
    Thursday, June 27, 2019 5:49 AM
  • of course, you can also make it in a 1 row code, but very unfriendly to read such as below.

    note that it is the exact same code, it will not run any quicker (afaik).

    let
        Source = Excel.CurrentWorkbook(){[Name="TableGroupByCombiningColumns"]}[Content],
        ChType = Table.TransformColumnTypes(#"Source", {{"D", type text}, {"Award", type text}, {"Year", type text}}),
        Group = Table.Group(ChType, {"D"}, {{"Combined", each Combiner.CombineTextByDelimiter(", ") (Table.CombineColumns(_, {"Award", "Year"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), "merged")[merged]), type text}})
    in #"Group

    or even

    let
        Source = Excel.CurrentWorkbook(){[Name="TableGroupByCombiningColumns"]}[Content],
        Group = Table.Group(#"Source", {"D"}, {{"Combined", each Combiner.CombineTextByDelimiter(", ") (Table.CombineColumns(Table.TransformColumnTypes(_, {{"Year", type text}, {"Award", type text}}, "en-US"), {"Award", "Year"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), "merged")[merged]), type text}})
    in #"Group"


    • Edited by anthony34 Thursday, June 27, 2019 6:07 AM
    • Marked as answer by SamNaik Monday, July 8, 2019 4:43 AM
    Thursday, June 27, 2019 6:01 AM
  • Alternate solution
    I didn't check which one is faster.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Grouped Rows" = Table.Group(Source, {"D"}, {{"Combined", each Text.Combine(Table.AddColumn(_[[Award],[Year]], "temp", each [Award] & " " & Text.From([Year]) )[temp], ","), type text}  })
    in
        #"Grouped Rows"

    • Marked as answer by SamNaik Monday, July 8, 2019 4:43 AM
    Thursday, June 27, 2019 8:45 AM
  • Thanks Bill. Works perfectly!
    • Proposed as answer by anthony34 Sunday, July 7, 2019 11:33 AM
    Friday, June 28, 2019 4:07 AM
  • Thanks Anthony. Works perfectly!
    Friday, June 28, 2019 4:07 AM
  • Dont seem to get the option to mark them as answers
    Friday, June 28, 2019 4:48 AM

  • Dont seem to get the option to mark them as answers
    Friday, June 28, 2019 4:49 AM
  • Hi Bill , Any chance you can help explain this piece of your code -

    Table.AddColumn(_[[Award],[Year]],

    Table.AddColumn take a table value as its first argument but when I individually try to add this to the Source table as an extra columns it shows up as Record. Could you pls explain?

    Regards

    sam

    Friday, June 28, 2019 10:24 PM
  • _ represents the table, and [[x],[y]] stands to keep only the columns x and y (like Table.SelectColumns)

    The 2 following codes are the same:

    let
        Source = Table.FromColumns(  {{1},{2}, {3}, {4}}, {"w", "x", "y", "z"} ),
        SelCol = Table.SelectColumns(Source,{"x", "y"})
    in #"SelCol"
    
    
    let
        Source = Table.FromColumns(  {{1},{2}, {3}, {4}}, {"w", "x", "y", "z"} ),
        SelCol = Source [[x],[y]]
    in #"SelCol"

    the syntax [[x],[y]] generates a table
    the syntaxe [y] generates a list


    • Marked as answer by SamNaik Monday, July 8, 2019 4:43 AM
    • Edited by anthony34 Thursday, November 7, 2019 4:32 PM
    Thursday, July 4, 2019 10:19 AM
  • Hi SamNaik,

    I've changed the type of the thread from discussion to question, so now you should be able to mark the answers that solved your problem as answers.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, July 7, 2019 5:14 AM