Tranpose a table without using Transpose UI

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.