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.
@Ashish_Mathur I already tried the code and managed to execute, but got only one problem.
The blank columns just miss match the FROM and TO as highlighted below... if we can manage to delete the blue area then our data is fixed.
here is the code
let
Source = Excel.CurrentWorkbook(){[Name="DK1_tbl"]}[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", "FROM_Connection_DK1.1", "FROM_Connection_DK1.2", "To", "TO_Connection_DK1.1", "TO_Connection_DK1.2"})
in
#"Reordered Columns"
Hi,
Paste your raw data in the file that i shared with you in the past 15 minutes.
@Ashish_Mathur here is the screenshot after i pasted data into your file. the same problem of the empty cells
Hi,
Share the link from where i can download your PBI file with your data and my latest solution.
here is the link to the my file. https://1drv.ms/x/s!An2fh7sujfnkhT1rNZHjJ91QRppa?e=ay945B
please find the table and connection as shown below.
your file with some of my data goes here, as i cannot paste all of my data due to the 3000 rows limitation in your file.
https://1drv.ms/u/s!An2fh7sujfnkhT7E2cCAOsL0Wb2b?e=sIbZQe
We will have to start from scratch. In the first post, you shared only 1 combination of From and To. Share multiple From and to combinations and show me the expected result there. On a small dataset, show the expected result. Ensure that the small dataset that you share (and thereby show the result of), can be simple pasted in an MS Excel file.
yes you are right, there are more than one combination. Yes I woud like the results to be simply loaded to MS Excel, as i have already shared the file with you.
@Ashish_Mathur did you come up with any idea to resolve the issue?
@Ashish_Mathur If I understood you correctly, you are looking my data in excel form? Here is the data.
https://1drv.ms/u/s!An2fh7sujfnkhT95tJXSbXkU72o7?e=0SDvKf
@Ashish_Mathur I do not know if the link i shared with you earlier is working proper, if not, please find the link here
https://1drv.ms/x/s!An2fh7sujfnkhT_YYaSE9_-J-ZVt?e=ce1FBp