Transform Table

[tuxo99]

hello,

I want to transform the first table to the second:

¿can you help/guide me?


[TABLE="width: 406"]
<colgroup><col span="3"><col span="2"></colgroup><tbody>[TR]
[TD]Segment[/TD]
[TD]Channel[/TD]
[TD]Product.ID[/TD]
[TD]jan[/TD]
[TD]feb[/TD]
[/TR]
[TR]
[TD]Domestic[/TD]
[TD]1[/TD]
[TD]23[/TD]
[TD]5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Domestic[/TD]
[TD]1[/TD]
[TD]54[/TD]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Online[/TD]
[TD]2[/TD]
[TD]43[/TD]
[TD]34[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Segment[/TD]
[TD]Channel[/TD]
[TD]Product.ID[/TD]
[TD]Month[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Domestic[/TD]
[TD]1[/TD]
[TD]23[/TD]
[TD]jan[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Domestic[/TD]
[TD]1[/TD]
[TD]54[/TD]
[TD]jan[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Online[/TD]
[TD]2[/TD]
[TD]43[/TD]
[TD]jan[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]Domestic[/TD]
[TD]1[/TD]
[TD]23[/TD]
[TD]feb[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Domestic[/TD]
[TD]1[/TD]
[TD]54[/TD]
[TD]mar[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Online[/TD]
[TD]2[/TD]
[TD]43[/TD]
[TD]abr[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


thank you

 

[SyedUsman]

In power query Paste the following code in advanced editor after inserting the table in power query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Segment", type text}, {"Channel", Int64.Type}, {"Product.ID", Int64.Type}, {"jan", Int64.Type}, {"feb", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Segment", "Channel", "Product.ID"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Month"}})
in
    #"Renamed Columns"

 

[tuxo99]

hello,

this is not an option, because the table has 7000 rows and the segment and products can change or add from one month to another

 

[SyedUsman]

well it is still working for me. you try & let me know where the issue is arising

 

[tuxo99]

you are right. i was doing something wrong

 

[Akuini]

This is another way.
I put the result in sheet2.

---

Please use Code Tags when posting a code.

[b][color=Royalblue]Sub[/color][/b] a1017816a()
[FONT=trebuchet ms][i][color=Lightseagreen]'https://www.mrexcel.com/forum/excel-questions/1017816-transform-table.html[/color][/i][/FONT]
[b][color=Royalblue]Dim[/color][/b] va, vc, vd, zd
[b][color=Royalblue]Dim[/color][/b] rr [b][color=Royalblue]As[/color][/b] [b][color=Royalblue]Long[/color][/b], rc [b][color=Royalblue]As[/color][/b] [b][color=Royalblue]Long[/color][/b], i [b][color=Royalblue]As[/color][/b] [b][color=Royalblue]Long[/color][/b], j [b][color=Royalblue]As[/color][/b] [b][color=Royalblue]Long[/color][/b], n [b][color=Royalblue]As[/color][/b] [b][color=Royalblue]Long[/color][/b]


rr = Range([b][color=brown]"A:A"[/color][/b]).Find([b][color=brown]"*"[/color][/b], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
    rc = Rows([b][color=crimson]1[/color][/b]).Find([b][color=brown]"*"[/color][/b], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        va = Range(Cells([b][color=crimson]2[/color][/b], [b][color=brown]"A"[/color][/b]), Cells(rr, [b][color=brown]"C"[/color][/b]))
            vc = Range(Cells([b][color=crimson]1[/color][/b], [b][color=brown]"D"[/color][/b]), Cells([b][color=crimson]1[/color][/b], rc))
                vd = Range(Cells([b][color=crimson]2[/color][/b], [b][color=brown]"D"[/color][/b]), Cells(rr, rc))
                    [b][color=Royalblue]ReDim[/color][/b] zd([b][color=crimson]1[/color][/b] [b][color=Royalblue]To[/color][/b] UBound(va, [b][color=crimson]1[/color][/b]) * (rc - [b][color=crimson]3[/color][/b]), [b][color=crimson]1[/color][/b] [b][color=Royalblue]To[/color][/b] [b][color=crimson]2[/color][/b])

    [b][color=Royalblue]For[/color][/b] j = [b][color=crimson]1[/color][/b] [b][color=Royalblue]To[/color][/b] (rc - [b][color=crimson]3[/color][/b])
        [b][color=Royalblue]For[/color][/b] i = [b][color=crimson]1[/color][/b] [b][color=Royalblue]To[/color][/b] UBound(vd, [b][color=crimson]1[/color][/b])
            n = n + [b][color=crimson]1[/color][/b]
            zd(n, [b][color=crimson]1[/color][/b]) = vc([b][color=crimson]1[/color][/b], j)
            zd(n, [b][color=crimson]2[/color][/b]) = vd(i, j)
        [b][color=Royalblue]Next[/color][/b]
    [b][color=Royalblue]Next[/color][/b]
    
    [FONT=trebuchet ms][i][color=Lightseagreen]'put the result in sheet2[/color][/i][/FONT]
    [b][color=Royalblue]With[/color][/b] Sheets([b][color=brown]"sheet2"[/color][/b])
    
        .Range([b][color=brown]"D2"[/color][/b]).Resize(UBound(zd, [b][color=crimson]1[/color][/b]), [b][color=crimson]2[/color][/b]) = zd
        
        [b][color=Royalblue]For[/color][/b] i = [b][color=crimson]0[/color][/b] [b][color=Royalblue]To[/color][/b] rc - [b][color=crimson]4[/color][/b]
            .Cells([b][color=crimson]2[/color][/b] + i * UBound(va, [b][color=crimson]1[/color][/b]), [b][color=brown]"A"[/color][/b]).Resize(UBound(va, [b][color=crimson]1[/color][/b]), [b][color=crimson]3[/color][/b]) = va
        [b][color=Royalblue]Next[/color][/b]
    
        .Range([b][color=brown]"A1"[/color][/b]).Resize([b][color=crimson]1[/color][/b], [b][color=crimson]5[/color][/b]) = Application.Transpose(Application.Transpose(Array([b][color=brown]"Segment"[/color][/b], [b][color=brown]"Channel"[/color][/b], [b][color=brown]"Product.ID"[/color][/b], [b][color=brown]"Month"[/color][/b], [b][color=brown]"Value"[/color][/b])))
    
    [b][color=Royalblue]End[/color][/b] [b][color=Royalblue]With[/color][/b]
    

[b][color=Royalblue]End[/color][/b] [b][color=Royalblue]Sub[/color][/b]

---