Forum Discussion

Helper III

5 years ago

Solved

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

Need Help

Ashish_Mathur

5 years ago

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

Helper III

5 years ago

@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

Super User

5 years ago

Hi,

You may download my PBI file from here.

Hope this helps.

Haleem
Helper III
5 years ago
@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
  Super User
  5 years ago
  Hi,
  
  Paste your raw data in the file that i shared with you in the past 15 minutes.
  - Haleem
    Helper III
    5 years ago
    @Ashish_Mathur here is the screenshot after i pasted data into your file. the same problem of the empty cells

Forum Discussion

Convert rows into Column as shown below

Related content

Limit Columns Shown in Column Chart

Open Mirroring CSV column types not converting

Convert measure to column

Convert calculated column into measure

converting rows into column in powerquery