Forum Discussion

Mai_Tran's avatar
Mai_Tran
Regular Visitor
7 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 ...
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.

AlienSx's avatar
AlienSx
Icon for Super User rankSuper User
7 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