Forum Discussion

Mai_Tran's avatar
Mai_Tran
Regular Visitor
6 months ago
Solved

Tranpose a table without using Transpose UI

Hi guys

 

I have a file like this

 

I want to tranpose it into this

 

I did it successfully using Tranpose UI, but I am finding another way, just code manually to do it.

 

Do you guys have any ideas to do it just with M code?

File in the link 

 

https://docs.google.com/spreadsheets/d/1zz00s_rJ1DPEHL6MWxv8PuT_L5x2OUO2/edit?usp=drive_link&ouid=105165278033135240195&rtpof=true&sd=true

 

Thanks

 

 

Need Help
  • v-echaithra's avatar
    v-echaithra
    6 months ago

    Hi Mai_Tran ,

    Here is another approach you can use that avoids the Transpose action and instead relies on a metadata-driven transformation.

    Conceptually, this approach:
    Treats the Year / Quarter / Month rows as metadata.
    Unpivots the value columns.
    Reconstructs the dimensional structure by mapping each column position back to its corresponding header.
    Produces a normalized table suitable for reporting or modeling.
    This makes the logic more scalable and predictable compared to a hard transpose.

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    AddIndex = Table.AddIndexColumn(Source, "RowID", 0, 1, Int64.Type),

    HeaderRows = Table.FirstN(AddIndex, 3),

    DataRows = Table.Skip(AddIndex, 4),

    ValueColumns = List.Skip(Table.ColumnNames(Source), 2),


    Unpivoted =
    Table.Unpivot(
    DataRows,
    ValueColumns,
    "Attribute",
    "Revenue"
    ),


    AddColIndex =
    Table.AddColumn(
    Unpivoted,
    "ColIndex",
    each List.PositionOf(ValueColumns, [Attribute]),
    Int64.Type
    ),


    YearList = List.Skip(Record.ToList(HeaderRows{0}), 2),
    QuarterList = List.Skip(Record.ToList(HeaderRows{1}), 2),
    MonthList = List.Skip(Record.ToList(HeaderRows{2}), 2),


    AddYear =
    Table.AddColumn(AddColIndex, "Year",
    each YearList{[ColIndex]}),

    AddQuarter =
    Table.AddColumn(AddYear, "Quarter",
    each QuarterList{[ColIndex]}),

    AddMonth =
    Table.AddColumn(AddQuarter, "Month",
    each MonthList{[ColIndex]}),


    Final =
    Table.SelectColumns(
    AddMonth,
    {"Region","Manager","Year","Quarter","Month","Revenue"} )
    in
    Final

    Best Regards,
    Chaithra E.

6 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    6 months ago
    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    headers = {"Region", "Manager", "Year", "Quarter", "Month", "Revenue"},
    rows = List.Buffer(Table.ToList(Source, (x) => x)),
    periods = List.Buffer(List.Zip(List.Transform(List.Range(rows, 1, 3), (r) => List.Skip(r, 2)))),
    trx = List.TransformMany(
        List.Skip(rows, 4),
        (x) => List.Zip({periods, List.Skip(x, 2)}),
        (x, y) => List.FirstN(x, 2) & y{0} & {y{1}}
    ),
    result = Table.FillDown(Table.FromList(trx, (x) => x, headers), headers)
in
    result
  • v-echaithra's avatar
    v-echaithra
    Community Support
    6 months ago

    Hi Mai_Tran ,

    Thank you AlienSx   for the inputs.

    We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

    Best Regards,
    Chaithra E.

    • Mai_Tran's avatar
      Mai_Tran
      Regular Visitor
      6 months ago

      Hi Chaithra E. and AlienSx

       

      Thank you so much for your prompt support. It is great help for me and I can learn a lot from your solution. But if you have another way to do it please send me here.

       

      Regards

       

      Mai Tran

      • v-echaithra's avatar
        v-echaithra
        Community Support
        6 months ago

        Hi Mai_Tran ,

        Here is another approach you can use that avoids the Transpose action and instead relies on a metadata-driven transformation.

        Conceptually, this approach:
        Treats the Year / Quarter / Month rows as metadata.
        Unpivots the value columns.
        Reconstructs the dimensional structure by mapping each column position back to its corresponding header.
        Produces a normalized table suitable for reporting or modeling.
        This makes the logic more scalable and predictable compared to a hard transpose.

        let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

        AddIndex = Table.AddIndexColumn(Source, "RowID", 0, 1, Int64.Type),

        HeaderRows = Table.FirstN(AddIndex, 3),

        DataRows = Table.Skip(AddIndex, 4),

        ValueColumns = List.Skip(Table.ColumnNames(Source), 2),


        Unpivoted =
        Table.Unpivot(
        DataRows,
        ValueColumns,
        "Attribute",
        "Revenue"
        ),


        AddColIndex =
        Table.AddColumn(
        Unpivoted,
        "ColIndex",
        each List.PositionOf(ValueColumns, [Attribute]),
        Int64.Type
        ),


        YearList = List.Skip(Record.ToList(HeaderRows{0}), 2),
        QuarterList = List.Skip(Record.ToList(HeaderRows{1}), 2),
        MonthList = List.Skip(Record.ToList(HeaderRows{2}), 2),


        AddYear =
        Table.AddColumn(AddColIndex, "Year",
        each YearList{[ColIndex]}),

        AddQuarter =
        Table.AddColumn(AddYear, "Quarter",
        each QuarterList{[ColIndex]}),

        AddMonth =
        Table.AddColumn(AddQuarter, "Month",
        each MonthList{[ColIndex]}),


        Final =
        Table.SelectColumns(
        AddMonth,
        {"Region","Manager","Year","Quarter","Month","Revenue"} )
        in
        Final

        Best Regards,
        Chaithra E.

  • v-echaithra's avatar
    v-echaithra
    Community Support
    6 months ago

    Hi Mai_Tran ,

    Thank you for confirming that issue has been resolved. If further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

    Best Regards,
    Chaithra E.