Tranpose a table without using Transpose UI

[Mai_Tran]

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=10...

Thanks

[v-echaithra]

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.

[v-echaithra]

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]

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 · Accepted solution]

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.

[Mai_Tran]

Dear @v-echaithra 

Thank you so much for your solution and detailed explanation. It is great help to me. 

Mai Tran

[AlienSx]

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