none
Trouble with GroupBy using text values RRS feed

  • Question


  • I am struggling with GroupBy, as shown below. I get the Pivot on “Sem” just fine, but can’t get the bottom table “GroupBy”, which I built manually. I group on Name, then want to concatenate items in each Col “17a” and “18a”.


    • Edited by bennettp77 Monday, December 17, 2018 9:56 PM
    Friday, December 14, 2018 11:59 PM

Answers

  • Hi, bennettp77,

    You could use this code:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
        toText = Table.TransformColumnTypes(Source, List.Transform(Table.ColumnNames(Source), each {_, type text})),
        group = Table.Group(toText, {"Name", "Sem"}, {"Items", each Text.Combine(_[Course], ", ")}),
        pivot = Table.Pivot(group, List.Distinct(group[Sem]), "Sem", "Items")
    in
        pivot
    

    result


    • Edited by Aleksei Zhigulin Tuesday, December 18, 2018 4:18 PM
    • Marked as answer by bennettp77 Thursday, December 20, 2018 4:28 PM
    Tuesday, December 18, 2018 4:17 PM

All replies

  • Hi bennetp77, 

    the message is missing in your thread ;)


    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, December 16, 2018 7:37 AM
    Moderator
  • Hi, bennettp77,

    You could use this code:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
        toText = Table.TransformColumnTypes(Source, List.Transform(Table.ColumnNames(Source), each {_, type text})),
        group = Table.Group(toText, {"Name", "Sem"}, {"Items", each Text.Combine(_[Course], ", ")}),
        pivot = Table.Pivot(group, List.Distinct(group[Sem]), "Sem", "Items")
    in
        pivot
    

    result


    • Edited by Aleksei Zhigulin Tuesday, December 18, 2018 4:18 PM
    • Marked as answer by bennettp77 Thursday, December 20, 2018 4:28 PM
    Tuesday, December 18, 2018 4:17 PM