Forum Discussion

Mederic's avatar
Mederic
Post Patron
2 years ago
Solved

Transpose each table before expanding

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 mm412,37 EUR
AB 105 - 163 mm11
170 mm734,56 EUR
AB 115 - 173 mm11
180 mm467,33 EUR
AB 125 - 183 mm22
190 mm632,50 EUR
AB 135 - 193 mm33
200 mm532,15 EUR
AB 145 - 203 mm44
210 mm347,45 EUR
AB 155 - 213 mm55
220 mm842,45 EUR
AB 165 - 223 mm66
230 mm236,70 EUR
AB 175 - 233 mm 77

 

 

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    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"

     

     

     

2 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    2 years ago

    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"