locked
How to aggregate text columns over rows RRS feed

  • Question

  • Hello community

    I'm trying to find a way to aggregate text columns over different rows. I know I have to use Table.Group but I'm unsuccessful implementing this in the code.

    My dataset consists of 47 different columns. Sometimes a row is duplicated with extra information. I added an extra column 'Unique' so I can identify the same record. An example:

    Column 1    Column 2    Column 3   Unique

    A                Z                1              AZ

    A                Z                2              AZ

    B                Y                8              BY

    C                Z                4              CZ

    C                Z                5              CZ

    C                Z                6              CZ

    So, based on column 'Unique' I know when I have to aggregate Column 3. The desired result is:

    Column 1    Column 2    Column 3   Unique

    A                Z                1 - 2         AZ

    B                Y                8              BY

    C                Z                4 - 5 - 6   CZ

    Below the code I already use in Power Query.

    Every idea or answer is much appreciated!

    Best regards

    Koen

        Bron = Excel.Workbook(File.Contents("source.xlsx"), null, true),
        basislijst_Sheet = Bron{[Item="source",Kind="Sheet"]}[Data],
        #"Type gewijzigd" = Table.TransformColumnTypes(basislijst_Sheet,{{"Column1", type any}, {"Column2", type text}, {"Column3", type any}, {"Column4", type text}, {"Column5", type any}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}, {"Column35", type any}, {"Column36", type any}, {"Column37", type any}, {"Column38", type any}, {"Column39", type any}, {"Column40", type any}, {"Column41", type any}, {"Column42", type any}, {"Column43", type any}, {"Column44", type any}, {"Column45", type any}, {"Column46", type any}, {"Column47", type any}}),
        #"Bovenste rijen verwijderd" = Table.Skip(#"Type gewijzigd",12),
        #"Kolommen verwijderd" = Table.RemoveColumns(#"Bovenste rijen verwijderd",{"Column1", "Column4", "Column6", "Column8", "Column9", "Column12", "Column14"}),
        #"Headers met verhoogd niveau" = Table.PromoteHeaders(#"Kolommen verwijderd"),
        #"Namen van kolommen gewijzigd" = Table.RenameColumns(...),
        #"Waarde vervangen" = Table.ReplaceValue(…),
        #"Waarde vervangen1" = Table.ReplaceValue(...),
        #"Waarde vervangen2" = Table.ReplaceValue(...),
        #"Waarde vervangen3" = Table.ReplaceValue(...),
        #"Waarde vervangen4" = Table.ReplaceValue(...),
        #"Waarde vervangen5" = Table.ReplaceValue(...),
        #"Waarde vervangen6" = Table.ReplaceValue(...),
        #"Waarde vervangen7" = Table.ReplaceValue(...),
        #"Waarde vervangen8" = Table.ReplaceValue(...),
        #"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Waarde vervangen8", "Unique", each [column 1]&[column 2]&[column 3]&[column 4]&[column 5]&[column 6])
    in
        #"Aangepaste kolom toegevoegd"
    

    Wednesday, August 12, 2015 8:47 AM

Answers

  • This question reminded me of this post by Chris Webb, which inspired me for the proposed improvement:

    let

     Bron = Excel.Workbook(File.Contents("source.xlsx"), null, true),
        ChgToText = Table.TransformColumnTypes(Bron,{{"Column 3", type text}}),
        Result= Table.Group(ChgToText, {"Column1", "Column2", "Unique"}, {{"All", each  Combiner.CombineTextByDelimiter("-")(_[Column 3]) }})
     in
        Result

    Bertrand



    Wednesday, August 12, 2015 3:57 PM
  • Hi Koen,

    try this one:

    let
     Bron = Excel.Workbook(File.Contents("source.xlsx"), null, true),
        ChgToText = Table.TransformColumnTypes(Bron,{{"Column 3", type text}}),
        Group = Table.Group(ChgToText, {"Column1", "Column2", "Unique"}, {{"All", each _, type table}}),
        SelectCol = Table.AddColumn(Group, "Custom.1", each [All][Column 3]),
        Result = Table.AddColumn(SelectCol, "Custom", each Lines.ToText([Custom.1],"-"))
    in
        Result


    Imke

    • Marked as answer by Michael Amadi Saturday, September 19, 2015 8:41 PM
    Wednesday, August 12, 2015 9:13 AM

All replies

  • Hi Koen,

    try this one:

    let
     Bron = Excel.Workbook(File.Contents("source.xlsx"), null, true),
        ChgToText = Table.TransformColumnTypes(Bron,{{"Column 3", type text}}),
        Group = Table.Group(ChgToText, {"Column1", "Column2", "Unique"}, {{"All", each _, type table}}),
        SelectCol = Table.AddColumn(Group, "Custom.1", each [All][Column 3]),
        Result = Table.AddColumn(SelectCol, "Custom", each Lines.ToText([Custom.1],"-"))
    in
        Result


    Imke

    • Marked as answer by Michael Amadi Saturday, September 19, 2015 8:41 PM
    Wednesday, August 12, 2015 9:13 AM
  • This question reminded me of this post by Chris Webb, which inspired me for the proposed improvement:

    let

     Bron = Excel.Workbook(File.Contents("source.xlsx"), null, true),
        ChgToText = Table.TransformColumnTypes(Bron,{{"Column 3", type text}}),
        Result= Table.Group(ChgToText, {"Column1", "Column2", "Unique"}, {{"All", each  Combiner.CombineTextByDelimiter("-")(_[Column 3]) }})
     in
        Result

    Bertrand



    Wednesday, August 12, 2015 3:57 PM