Forum Discussion

Trebor84's avatar
Trebor84
Helper II
3 years ago
Solved

Grouping every 100 items in a column

Hi, I am trying to group a list of values in a column for every 100 rows then join them with a comma as seperator into a single value.  Can anyone assist with a way to do this in Power Query please?

 

Example:

 

=TEXTJOIN(",",TRUE,A2:A101)

=TEXTJOIN(",",TRUE,A102:A201)

=TEXTJOIN(",",TRUE,A202:A205)

etc

 

The end result in Power Query should look like column C below.  There is no sorting required, it just needs to concaenate the first 100 vaues then the next 100 and so on.

 

 

 

 

Need Help
  • ImkeF's avatar
    ImkeF
    3 years ago

    Hi Trebor84 ,
    you can do this by:

     

    1. Adding an Index colum
    2. Convert that Index column by a Modulo of 100
    3. Group on that new column and combine the values from the VALUE-column with comma.

    Please check out this code and follow the steps:

    let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "PdO7bR0wAATBXhQ7eCTv+KlFUP9tGIahyTbbaL6/v8Zc6dfPn/+11VFXvd/qRw011VJRHvWoRz3qsT22x/bYHttje2yP7bE9tsfxOB7H43gcj+NxPI7H8Tge1+N6XI/rcT2ux/W4HtfjejyP5/E8nsfzeB7P43k8j/f7yOejhppqqaiqrY66ymN4DI/hMTyGx/AYHsNjeAyP6TE9psf0mB7TY3pMj+kxPZbH8lgey2N5LI/lsTyWx/KIRzziEY94cB7Ow3k4D+fhPJyH83AezsN5OA/n4Tych/NwHs7DeTgP5+E8nIfzcB7Ow3k4D+fhPJyH83AezsN5OA/n4Tych/NwHs7DeTgP5+E8nIfzcB7Ow3k4D+fhPJyX83Jezst5OS/n5bycl/NyXs7LeTkv5+W8nJfzcl7Oy3k5L+flvJyX83Jezst5OS/n5bycl/NyXs7LeTkv5+W8nJfzcl7Oy3k5L+flvJyX83Jezst5OS/n5bycl/NyXs7LeTkv5+W8nJfzcl7Oy3k5L+flvP+c//wF", 
                    BinaryEncoding.Base64
                ), 
                Compression.Deflate
            )
        ), 
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [VALUE = _t]
    ), 
    #"Changed Type" = Table.TransformColumnTypes(Source, {{"VALUE", type text}}), 
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), 
    #"Integer-Divided Column" = Table.TransformColumns(
        #"Added Index", 
        {{"Index", each Number.IntegerDivide(_, 100), Int64.Type}}
    ), 
    #"Grouped Rows" = Table.Group(
        #"Integer-Divided Column", 
        {"Index"}, 
        {{"Count", each Text.Combine(_[VALUE], ", ")}}
    )
in
    #"Grouped Rows"
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    NewStep=List.Transform(Table.Split(Table.TransformColumns(PreviousStepName,{},Text.From),100),each Text.Combine([Value],","))

4 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    3 years ago

    Hi Trebor84 ,
    you can do this by:

     

    1. Adding an Index colum
    2. Convert that Index column by a Modulo of 100
    3. Group on that new column and combine the values from the VALUE-column with comma.

    Please check out this code and follow the steps:

    let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "PdO7bR0wAATBXhQ7eCTv+KlFUP9tGIahyTbbaL6/v8Zc6dfPn/+11VFXvd/qRw011VJRHvWoRz3qsT22x/bYHttje2yP7bE9tsfxOB7H43gcj+NxPI7H8Tge1+N6XI/rcT2ux/W4HtfjejyP5/E8nsfzeB7P43k8j/f7yOejhppqqaiqrY66ymN4DI/hMTyGx/AYHsNjeAyP6TE9psf0mB7TY3pMj+kxPZbH8lgey2N5LI/lsTyWx/KIRzziEY94cB7Ow3k4D+fhPJyH83AezsN5OA/n4Tych/NwHs7DeTgP5+E8nIfzcB7Ow3k4D+fhPJyH83AezsN5OA/n4Tych/NwHs7DeTgP5+E8nIfzcB7Ow3k4D+fhPJyX83Jezst5OS/n5bycl/NyXs7LeTkv5+W8nJfzcl7Oy3k5L+flvJyX83Jezst5OS/n5bycl/NyXs7LeTkv5+W8nJfzcl7Oy3k5L+flvJyX83Jezst5OS/n5bycl/NyXs7LeTkv5+W8nJfzcl7Oy3k5L+flvP+c//wF", 
                    BinaryEncoding.Base64
                ), 
                Compression.Deflate
            )
        ), 
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [VALUE = _t]
    ), 
    #"Changed Type" = Table.TransformColumnTypes(Source, {{"VALUE", type text}}), 
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), 
    #"Integer-Divided Column" = Table.TransformColumns(
        #"Added Index", 
        {{"Index", each Number.IntegerDivide(_, 100), Int64.Type}}
    ), 
    #"Grouped Rows" = Table.Group(
        #"Integer-Divided Column", 
        {"Index"}, 
        {{"Count", each Text.Combine(_[VALUE], ", ")}}
    )
in
    #"Grouped Rows"
    • CNENFRNL's avatar
      CNENFRNL
      Community Champion
      3 years ago

      Table.Group() does the trick at one go based on a 0-based index column,

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TdG5DcIAEETRXhwTMIc5QqAMy3L/XYCEtN/Zz552dtuW1/tzaNkv//JUpjq1Tt2m7lOPqeeUriSKYIQjICEJSlgCE5rRfLoJzWhGM5rRjGY0owUtaDlNiBa0oAUtaEELWtGKVrSePoZWtKIVrWhFW3/a/gU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),

    Grouped = Table.Group(#"Added Index", "Index", {"Grouped", each _}, 0, (x,y) => Byte.From(Number.Mod(y,10)=0))
in
    Grouped

       

       

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    3 years ago

    NewStep=List.Transform(Table.Split(Table.TransformColumns(PreviousStepName,{},Text.From),100),each Text.Combine([Value],","))