how can arrange data in excel

Posting Art

New member
Joined
Jul 22, 2017
Messages
18
Reaction score
0
Points
1
Location
PAK
Excel Version(s)
2018
i have table in this formate
1.png
i want result in this formate
2.png
i have huge row of data , i can do one by one any one can help me to show data in this formate
thanks dear
 

Attachments

  • Test.xlsx
    16 KB · Views: 8
Trivial with Power Query

Code:
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    types.Chnage = Table.TransformColumnTypes(Source,{{"Objects", type text}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}}),
    othercols.Unpivot = Table.UnpivotOtherColumns(types.Chnage, {"Objects"}, "Attribute", "Value"),
    rows.Filter = Table.SelectRows(othercols.Unpivot, each ([Objects] <> "Totals")),
    rows.Sorted = Table.Sort(rows.Filter,{{"Attribute", Order.Ascending}})
in
    rows.Sorted[/FONT]

If you want the blanks rows between, it is a bit more complex

Code:
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    types.Chnage = Table.TransformColumnTypes(Source,{{"Objects", type text}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}}),
    othercols.Unpivot = Table.UnpivotOtherColumns(types.Chnage, {"Objects"}, "Attribute", "Value"),
    rows.Filter = Table.SelectRows(othercols.Unpivot, each ([Objects] <> "Totals")),
    rows.Sorted = Table.Sort(rows.Filter,{{"Attribute", Order.Ascending}}),    
    list.Shifted = {null} &  List.RemoveLastN(Table.Column(rows.Sorted, "Attribute"),1),
    lists.Merged = Table.ToColumns(rows.Sorted) & {list.Shifted},
    lists.Expanded = Table.FromColumns(lists.Merged, Table.ColumnNames(rows.Sorted) & {"Previous Row"}),
    insert.Helper = Table.AddColumn(lists.Expanded, "Custom", each if [Attribute]<>[Previous Row] and [Previous Row]<> null then "-1 1" else 1),
    originals.Merge = Table.CombineColumns(Table.TransformColumnTypes(insert.Helper, {{"Value", type text}}, "en-GB"),{"Objects", "Attribute", "Value"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged.1"),
    shift.Remove = Table.RemoveColumns(originals.Merge,{"Previous Row"}),
    helper.Split = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(shift.Remove, {{"Custom", type text}}, "en-GB"), {{"Custom", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
    breaks.ToNull = Table.AddColumn(helper.Split, "Custom.1", each if [Custom]="-1" then null else [Merged.1]),
    othercols.Remove = Table.RemoveColumns(breaks.ToNull,{"Merged.1", "Custom"}),
    merged.Split = Table.SplitColumn(othercols.Remove, "Custom.1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Custom.1.1", "Custom.1.2", "Custom.1.3"}),
    columns.Type = Table.TransformColumnTypes(merged.Split,{{"Custom.1.1", type text}, {"Custom.1.2", type text}, {"Custom.1.3", Int64.Type}}),
    columns.Rename = Table.RenameColumns(columns.Type,{{"Custom.1.1", "Id"}, {"Custom.1.2", "Object"}, {"Custom.1.3", "Value"}})
in
    columns.Rename[/FONT]
 
Back
Top