Forum Discussion
Create rows by combining columns
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
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
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
