Combine rows based on pattern

[Greatbi1]

Hello all,

I am struct with a problem in power query. Below is what data in single column.

Colum1

Name1

Value11

Value12

Value13

Name2

Value21

Value22

Value23

Result that I want is

Column 1, Column 2

Name1,  "Value11,Value12,Value13"

Name2, "Value21,Value22,Value23"

• As you can see, i want to aggregate values from 2,3,4 rows for row 1. This needs to repeat. I tried lot of things for now but having difficulty in creating a loop in power query.

Thanks to all for contribution to this community.

[Anonymous · Accepted solution]

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WUorViVbyS8xNNQSzwERYYk5pqiGGgBG6gDFCAGQCurwRuglGGCqAJsQCAA==", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type text) meta [Serialized.Text = true])
    in
      type table[Colum1 = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Colum1", type text}}),
  #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Colum1] <> null and [Colum1] <> ""),
  #"Grouped Rows" = Table.Group(
    #"Filtered Rows", 
    {"Colum1"}, 
    {{"all", each Table.Transpose(_)}}, 
    GroupKind.Local, 
    (x, y) => Number.From(Text.Start(x[Colum1], 4) = Text.Start(y[Colum1], 4))
  ),
  #"Expanded all" = Table.ExpandTableColumn(
    #"Grouped Rows", 
    "all", 
    {"Column2", "Column3", "Column4"}, 
    {"all.Column2", "all.Column3", "all.Column4"}
  )
in
  #"Expanded all"

this code works for any column that has groups of any size delimited by words that have the first 4 equal characters

[ziying35 · Accepted solution]

@Greatbi1 

let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65Wcs7PKc3NM1SyUvJLzE01VKrVQRIzNELlGqNyTVG4YAOMUA1QSja0RBNJMTJAE0k1AlobCwA=",BinaryEncoding.Base64),Compression.Deflate))),
    acc = List.Accumulate(
             Source[Column1]&{"Name"},{{}, {}},
             (s,c)=>if s{0}={} then {{c},{}}
                    else if c is text and Text.StartsWith(c, "Name") then {{c}, s{1}&{{s{0}{0}}&{Text.Combine(List.Transform(List.Skip(s{0}),Text.From),", ")}}} 
                         else {s{0}&{c}, s{1}}
          ){1},
    result = Table.FromRows(acc)
in
    result

If you have data below 2000 lines, you can also use the List.Accumulate function solution.  Although I also like List.Accumulate, it handles more than 2000 lines of data and is prone to stack overflows

[ziying35]

Hi, @Greatbi1 

let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65Wcs7PKc3NM1SyUvJLzE01VKrVQRIzNELlGqNyTVG4YAOMUA1QSja0RBNJMTJAE0k1AlobCwA=",BinaryEncoding.Base64),Compression.Deflate))),
    result = Table.Group(Source,"Column1",{"Column2",each Text.Combine(List.Transform(List.Skip([Column1]),(x)=>Text.From(x)),", ")},0,(x,y)=> Byte.From(y is text and Text.StartsWith(y,"Name")) )
in
    result

If my solution solves your problem, please mark it as a solution

[Anonymous · Accepted solution]

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WUorViVbyS8xNNQSzwERYYk5pqiGGgBG6gDFCAGQCurwRuglGGCqAJsQCAA==", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type text) meta [Serialized.Text = true])
    in
      type table[Colum1 = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Colum1", type text}}),
  #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Colum1] <> null and [Colum1] <> ""),
  #"Grouped Rows" = Table.Group(
    #"Filtered Rows", 
    {"Colum1"}, 
    {{"all", each Table.Transpose(_)}}, 
    GroupKind.Local, 
    (x, y) => Number.From(Text.Start(x[Colum1], 4) = Text.Start(y[Colum1], 4))
  ),
  #"Expanded all" = Table.ExpandTableColumn(
    #"Grouped Rows", 
    "all", 
    {"Column2", "Column3", "Column4"}, 
    {"all.Column2", "all.Column3", "all.Column4"}
  )
in
  #"Expanded all"

this code works for any column that has groups of any size delimited by words that have the first 4 equal characters

[Greatbi1]

Hi @Anonymous  , Just need one more help. Can you please explain how group rows is working. 

[Anonymous]

I could not explain it better than as done by @imke here, where you can also find other examples of use of the fifth element.

do not hesitate to ask if you still need specific clarifications for "your" specific case ...