Create rows by combining columns

[irfan_abdrhman]

There's object_1, object_2, object_3, ... object_10 and item_1, item_2, item_3,... item_10. However instead of it being by row, all those data is in a single row. How do I link object_1 and item_1, object_2 and item_2, object_3 and item_3,.... object_10 and item_10 together in a row by rows, instead of all that in one single row. There are other columns as well, but some of them needs to be duplicated for example Subject, object_1 and item_1, is one row. Subject, object_2 and item_2 is the next row and so on. Please tell me if this is workable, thank you.

[v-cgao-msft · Accepted solution]

Hi @irfan_abdrhman ,

Suppose we have:

Please try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc+7CoNAFIThd9nawlldL89hKYuQYJFASGPePyKcmQG7n1Oc4VvXtPwe7/15bEhN+t4yKztlryzKQTkqJ+VsE+3Zr2P/XGNX5Iguoo8oEUPEGDFFzHzYptrIZQK40e9Gg9lgOJgOxoP5YMAsIIUgETSCSFAJMkEnCAWl50atfw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SubjectNames = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, Column14 = _t, Column15 = _t, Column16 = _t, Column17 = _t, Column18 = _t, Column19 = _t, Column20 = _t, Column21 = _t]),
Names = List.Range(Table.ColumnNames(Source), 1, (Table.ColumnCount(Source)-1)/2 ),
Col_Names = {"ColumnNames", "ObjectNames", "ItemNames"},
Group = Table.Group(Source, {"SubjectNames"}, {{"Data", each Table.FromColumns( {Names} & List.Split(List.Skip(Table.ToRows(_){0}), List.Count(Names)), Col_Names)}}),
Expand = Table.ExpandTableColumn(Group, "Data", Col_Names, Col_Names)
in
Expand

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[irfan_abdrhman]

That seems like a great workaround but is it possible for you to comment on the lines on how to alter the coding? And how the source would look like if the source is from an excel instead. But I think this method is great.

[v-cgao-msft]

Hi @irfan_abdrhman ,

Of course, for more information on how to integrate the solution with your code please refer to this blog.
Power BI Forum Help: How to integrate M-code into ... - Microsoft Fabric Community

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group