How do I group columns together into rows?

[Anonymous]

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

1	2	3	4	5	1_1	2_2	3_3	4_4	5_5
5	4	2	2	5	4	6	4	6	6

Into

1	2	3	4	5
5	4	2	4	5
4	6	4	6	6

[slorin]

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 

[AlienSx]

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

[tackytechtom]

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/