Forum Discussion
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, 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.
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 ExpandBest Regards,
Gao
Community Support TeamIf 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
3 Replies
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 ExpandBest Regards,
Gao
Community Support TeamIf 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
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.
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 CommunityBest Regards,
Gao
Community Support TeamIf 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
