Transpose each table before expanding

[Mederic]

Hello,

I'd like some help in obtaining the 2 transformations framed in red (see screenshot)
Here's the M code I'm stuck on

Thanks in advance

Regards

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Index"}, {{"Count", each Table.RemoveColumns( _, "Index")}})
in
    #"Grouped Rows"

Column1Column2

160 mm	412,37 EUR
AB 105 - 163 mm	11
170 mm	734,56 EUR
AB 115 - 173 mm	11
180 mm	467,33 EUR
AB 125 - 183 mm	22
190 mm	632,50 EUR
AB 135 - 193 mm	33
200 mm	532,15 EUR
AB 145 - 203 mm	44
210 mm	347,45 EUR
AB 155 - 213 mm	55
220 mm	842,45 EUR
AB 165 - 223 mm	66
230 mm	236,70 EUR
AB 175 - 233 mm	77

[Mederic]

Hello @ronrsnfld ,

thank you for your much, great work
Best Regards.

[ronrsnfld · Accepted solution]

Try:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Index"}, {
        {"Pivot",(t)=>
            let 
                #"Remove Index" = Table.RemoveColumns(t,"Index"),
                #"Unpivot" = Table.UnpivotOtherColumns(#"Remove Index",{},"Attribute","Value"),
                #"Col Names" = 
                    Table.RemoveColumns(
                        Table.TransformColumns(
                            Table.AddIndexColumn(#"Unpivot","New Cols",1,1,Int64.Type),
                        {"New Cols", each Number.ToText(_, "'Column'0")}),
                    {"Attribute"}),
                Pivot = Table.Pivot(#"Col Names",#"Col Names"[New Cols],"New Cols","Value")
            in 
                #"Pivot"}
    }),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Index"}),
    #"Expanded Pivot" = Table.ExpandTableColumn(#"Removed Columns", "Pivot", {"Column1", "Column2", "Column3", "Column4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Pivot",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", Int64.Type}})
in
    #"Changed Type1"