Transform Data - Excel-CodePudding

I have data in table 1 and I want to transform it to table 2, any advice for this, I'm using power query but it's really slow for a large dataset.

CodePudding user response：

You can use this formula

=IFERROR(INDEX(FILTER($C$2:$D$6,$B$2:$B$6=$B10),C$8,MATCH(C$9,$C$1:$D$1,0)),"")

or =IFERROR(INDEX(FILTER($C$2:$D$6,$B$2:$B$6=$B17),VALUE(RIGHT(C$15,1)),MATCH(C$16,$C$1:$D$1,0)),"")

CodePudding user response：

If this format it ok for you, code is below for Powerquery. Otherwise explain why Code3 is needed, and when a row would use Code3 as opposed to Code2 in your sample

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Proportion", Int64.Type}, {"%", type number}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "Name"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"ID"}, {{"data", each 
    Table.AddColumn(
        Table.TransformColumns(
            Table.AddIndexColumn(_, "Index", 2, 1, Int64.Type)
        ,{{"Index", each Number.IntegerDivide(_, 2), Int64.Type}})
    , "Header", each if [Attribute]="Proportion" then "Code " &Text.From([Index]) else "Code " &Text.From([Index]) & "%"
    ), type table}}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Name", "Value",  "Header"}, {"Name", "Value", "Header"}),
#"Pivoted Column" = Table.Pivot(#"Expanded data", List.Distinct(#"Expanded data"[Header]), "Header", "Value")
in  #"Pivoted Column"