Forum Discussion

Haleem's avatar
Haleem
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's avatar
    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.

    Untitled.png

Haleem's avatar
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

new.png

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    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's avatar
    Haleem
    Helper III
    5 years ago

    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. 

  • Haleem's avatar
    Haleem
    Helper III
    5 years ago

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