Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

How do I group columns together into rows?

I have a problem that I haven't been able to solve, I have data that currently looks like below.  I'd like to combine 1 and 1_1 to the same column moving 1_1 data onto a new row under the column 1.

 

I got this problem while reading in multiple  JSON arrays.

 

Does anyone have any ideas? Thanks in advance

 

123451_12_23_34_45_5
5422546466

 

Into

 

12345
54245
46466

 

Need Help

3 Replies

  • tackytechtom's avatar
    tackytechtom
    Most Valuable Professional
    3 years ago

    Hi Anonymous ,

     

    How about this:

     

    To be honest, it got quite hacky, but the solution might still work for your case.

     

    Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlXSUTIBYiMohvHNkGgzpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"1" = _t, #"2" = _t, #"3" = _t, #"4" = _t, #"5" = _t, #"1_1" = _t, #"2_2" = _t, #"3_3" = _t, #"4_4" = _t, #"5_5" = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute", "Attribute.2"}),
    #"Merged Queries" = Table.NestedJoin(#"Split Column by Delimiter", {"Attribute"}, #"Split Column by Delimiter", {"Attribute.2"}, "Split Column by Delimiter", JoinKind.LeftOuter),
    #"Expanded Split Column by Delimiter" = Table.ExpandTableColumn(#"Merged Queries", "Split Column by Delimiter", {"Value"}, {"Split Column by Delimiter.Value"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Split Column by Delimiter", each [Attribute.2] = null and [Attribute.2] <> ""),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute.2"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"1", Int64.Type}, {"2", Int64.Type}, {"3", Int64.Type}, {"4", Int64.Type}, {"5", Int64.Type}})
in
    #"Changed Type1"

     

     Let me know if this solves your issue 🙂

     

    /Tom
    https://www.tackytech.blog/
    https://www.instagram.com/tackytechtom/

  • AlienSx's avatar
    AlienSx
    Super User
    3 years ago

    Hi, Anonymous 

    let
    Source = your_table,
    n = Table.ColumnCount(Source) / 2,
    rs = List.Buffer(Table.ToRows(Source)),
    txform = 
        List.Accumulate(
            rs,
            {},
            (s, c) => s & {List.FirstN(c, n)} & {List.LastN(c, n)}
        ),
    z = Table.FromRows(txform, List.FirstN(Table.ColumnNames(Source), n))
in
    z
  • slorin's avatar
    slorin
    Super User
    3 years ago

    Hi

    Another solution

    let
    Source = YourSource,
    n = Table.ColumnCount(Source) / 2,
    Table = Table.Combine(
      List.Transform(
       Table.ToRows(Source),
       each Table.FromRows({List.FirstN(_,n),List.LastN(_,n)},
        List.FirstN(Table.ColumnNames(Source),n)
        )
      )
    )
    in
    Table

    Or

    let
    Source = YourSource,
    UnPivot = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    TextBefore = Table.TransformColumns(UnPivot, {{"Attribute", each Text.BeforeDelimiter(_, "_"), type text}}),
    Group = Table.Group(TextBefore, {"Attribute"}, {{"Data", each [Value]}}),
    TableFromColumns = Table.FromColumns(Group[Data],Group[Attribute])
    in
    TableFromColumns

     

    Stéphane