Forum Discussion

irfan_abdrhman's avatar
irfan_abdrhman
Icon for Helper II rankHelper II
2 years ago
Solved

Create rows by combining columns

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, o...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
irfan_abdrhman
Icon for Helper II rankHelper II
2 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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