Forum Discussion
Convert rows into Column as shown below
Hello
I'm sure there's a better way, but this M code works on your sample data at least
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Merged Columns" = Table.CombineColumns(Source,{"CONT", "EXP"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"), Partition = Table.Group(#"Merged Columns", {"Attribute"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"From", "To", "Merged", "Index"}, {"From", "To", "Merged", "Index"}), #"Pivoted Column" = Table.Pivot(#"Expanded Partition", List.Distinct(#"Expanded Partition"[Attribute]), "Attribute", "Merged"), #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "TO_Connection_DK1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"TO_Connection_DK1.1", "TO_Connection_DK1.2"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"TO_Connection_DK1.1", type text}, {"TO_Connection_DK1.2", type text}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "FROM_Connection_DK1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"FROM_Connection_DK1.1", "FROM_Connection_DK1.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"FROM_Connection_DK1.1", type text}, {"FROM_Connection_DK1.2", type text}}), #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"From", "To", "TO_Connection_DK1.1", "FROM_Connection_DK1.1", "TO_Connection_DK1.2", "FROM_Connection_DK1.2"}) in #"Reordered Columns"I hope this helps.
Hello
I'm sure there's a better way, but this M code works on your sample data at least
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Merged Columns" = Table.CombineColumns(Source,{"CONT", "EXP"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
Partition = Table.Group(#"Merged Columns", {"Attribute"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"From", "To", "Merged", "Index"}, {"From", "To", "Merged", "Index"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Partition", List.Distinct(#"Expanded Partition"[Attribute]), "Attribute", "Merged"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "TO_Connection_DK1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"TO_Connection_DK1.1", "TO_Connection_DK1.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"TO_Connection_DK1.1", type text}, {"TO_Connection_DK1.2", type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "FROM_Connection_DK1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"FROM_Connection_DK1.1", "FROM_Connection_DK1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"FROM_Connection_DK1.1", type text}, {"FROM_Connection_DK1.2", type text}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"From", "To", "TO_Connection_DK1.1", "FROM_Connection_DK1.1", "TO_Connection_DK1.2", "FROM_Connection_DK1.2"})
in
#"Reordered Columns"I hope this helps.
@Ashish_Mathur Thanks a lot. Can you please share the Power Query file which you have made for the is query. I can see you have only posted a picture/screen shot of the power query, it would be great to send me the file. Thanks.
Hi,
I have already shared the M code with you. Just copy the M code and paste it in the Query Editor.
@Ashish_Mathur I already have the below code in the my query editor. How can I add your code here? I just copied your code where my code is ended, but it gives me an error.
let
Source = Excel.Workbook(File.Contents("C:UsersdksahsOneDrive - Chr HansenDocumentsPersonalUsman3,0Master_28-10-2020.xlsx"), null, true),
MasterData_tbl_Table = Source{[Item="MasterData_tbl",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(MasterData_tbl_Table,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"DK1_FROM", type text}, {"FROM_Connections_DK1", type text}, {"DK1_TO", type text}, {"TO_Connection_DK1", type text}, {"DK2_FROM", type text}, {"FROM_Connection_DK2", type text}, {"DK2_TO", type text}, {"TO_Connection_DK2", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1),
#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "DK1_FROM", "FROM_Connections_DK1", "DK1_TO", "TO_Connection_DK1", "DK2_FROM", "FROM_Connection_DK2", "DK2_TO", "TO_Connection_DK2"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"DK2_FROM", "FROM_Connection_DK2", "DK2_TO", "TO_Connection_DK2"}),
#"Inserted Merged Column" = Table.AddColumn(#"Removed Columns", "Merged", each Text.Combine({[DK1_FROM], [FROM_Connections_DK1], [DK1_TO], [TO_Connection_DK1]}, "-"), type text),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Merged Column",{{"Merged", "KEY_DK1"}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns",{"Index", "KEY_DK1", "DK1_FROM", "FROM_Connections_DK1", "DK1_TO", "TO_Connection_DK1"}),
#"Filtered Rows" = Table.SelectRows(#"Reordered Columns1", each ([Index] = 7)),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Filtered Rows", {"FROM_Connections_DK1", "TO_Connection_DK1"}, "Attribute", "Value"),
#"Merged Queries" = Table.NestedJoin(#"Unpivoted Only Selected Columns", {"Value"}, Connections1, {"Connections"}, "Connections1", JoinKind.LeftOuter),
#"Expanded Connections1" = Table.ExpandTableColumn(#"Merged Queries", "Connections1", {"Ex-1", "Ex-2", "Ex-3", "Ex-4", "Ex-5", "Ex-6", "Ex-7", "Ex-8", "Ex-9", "Ex-10", "Ex-11", "Ex-12", "Ex-13", "Ex-14", "Ex-15", "Ex-16", "Ex-17", "Ex-18", "Ex-19", "Ex-20", "Ex-21", "Ex-22", "Ex-23", "Ex-24", "Ex-25", "Ex-26", "Ex-27", "Ex-28", "Ex-29", "Ex-30", "Ex-31", "Ex-32", "Ex-33", "Ex-34", "Ex-35", "Ex-36", "Ex-37", "Ex-38", "Ex-39", "Ex-40", "Ex-41", "Ex-42", "Ex-43", "Ex-44", "Ex-45", "Ex-46", "Ex-47", "Ex-48", "Ex-49", "Ex-50", "Ex-51", "Ex-52", "Ex-53", "Ex-54", "Ex-55", "Ex-56", "Ex-57", "Ex-58", "Ex-59", "Ex-60", "Ex-61", "Ex-62", "Ex-63", "Ex-64", "Ex-65", "Ex-66", "Ex-67", "Ex-68", "Ex-69", "Ex-70", "Ex-71", "Ex-72", "Ex-73", "Ex-74", "Ex-75", "Ex-76"}, {"Connections1.Ex-1", "Connections1.Ex-2", "Connections1.Ex-3", "Connections1.Ex-4", "Connections1.Ex-5", "Connections1.Ex-6", "Connections1.Ex-7", "Connections1.Ex-8", "Connections1.Ex-9", "Connections1.Ex-10", "Connections1.Ex-11", "Connections1.Ex-12", "Connections1.Ex-13", "Connections1.Ex-14", "Connections1.Ex-15", "Connections1.Ex-16", "Connections1.Ex-17", "Connections1.Ex-18", "Connections1.Ex-19", "Connections1.Ex-20", "Connections1.Ex-21", "Connections1.Ex-22", "Connections1.Ex-23", "Connections1.Ex-24", "Connections1.Ex-25", "Connections1.Ex-26", "Connections1.Ex-27", "Connections1.Ex-28", "Connections1.Ex-29", "Connections1.Ex-30", "Connections1.Ex-31", "Connections1.Ex-32", "Connections1.Ex-33", "Connections1.Ex-34", "Connections1.Ex-35", "Connections1.Ex-36", "Connections1.Ex-37", "Connections1.Ex-38", "Connections1.Ex-39" , "Connections1.Ex-40", "Connections1.Ex-41", "Connections1.Ex-42", "Connections1.Ex-43", "Connections1.Ex-44", "Connections1.Ex-45", "Connections1.Ex-46", "Connections1.Ex-47", "Connections1.Ex-48", "Connections1.Ex-49", "Connections1.Ex-50", "Connections1.Ex-51", "Connections1.Ex-52", "Connections1.Ex-53", "Connections1.Ex-54", "Connections1.Ex-55", "Connections1.Ex-56", "Connections1.Ex-57", "Connections1.Ex-58", "Connections1.Ex-59", "Connections1.Ex-60", "Connections1.Ex-61", "Connections1.Ex-62", "Connections1.Ex-63", "Connections1.Ex-64", "Connections1.Ex-65", "Connections1.Ex-66", "Connections1.Ex-67", "Connections1.Ex-68", "Connections1.Ex-69", "Connections1.Ex-70", "Connections1.Ex-71", "Connections1.Ex-72", "Connections1.Ex-73", "Connections1.Ex-74", "Connections1.Ex-75", "Connections1.Ex-76"-
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Connections1", {"Index", "KEY_DK1", "DK1_FROM", "DK1_TO", "Attribute", "Value"}, "Attribute.1", "Value.1"),
#"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute.1", "Index", "KEY_DK1"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"DK1_FROM", "FROM"}, {"DK1_TO", "TO"}, {"Value", "CONT"}, {"Value.1", "EXP"}})
in
#"Renamed Columns1"