Convert rows into Column as shown below

[Haleem]

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. 

[Ashish_Mathur · Accepted solution]

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.

[Haleem]

@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.

[Ashish_Mathur]

Hi,

I have already shared the M code with you.  Just copy the M code and paste it in the Query Editor.

[Haleem]

@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"

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Haleem]

@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"

[Ashish_Mathur]

Hi,

Paste your raw data in the file that i shared with you in the past 15 minutes.

[Haleem]

@Ashish_Mathur here is the screenshot after i pasted data into your file. the same problem of the empty cells

[Haleem]

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

[Ashish_Mathur]

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.

[Haleem]

@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

[Haleem]

@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

[Haleem]

@Ashish_Mathur did you come up with any idea to resolve the issue?

[Haleem]

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]

Hi,

Share the link from where i can download your PBI file with your data and my latest solution.