Forum Discussion
Convert rows into Column as shown below
I have the first table, which i need to show in the format shown in the table 2. In short, Attribute, CONT, and Exp should be split into as shown below. this is just a sample data. i have hundred of commbination in Cont and Exp column together with from and to codes.
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.
15 Replies
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.
