Heo to transform XLS Table in PBI?

Hi Marco_88, check this:

Output

let
    Source = Excel.Workbook(File.Contents("C:\Users\abc\Downloads\PBI Table Example_R01.xlsx"), null, false),
    Sheet = Source{[Item="Summary",Kind="Sheet"]}[Data],
    RemovedBlankAndTotalCols = [ blankCols = Table.SelectRows(Table.TransformColumnTypes(Table.Profile(Sheet),{{"NullCount", Int64.Type}, {"Count", Int64.Type}}), each [Count] = [NullCount])[Column],
    totalCols = List.RemoveFirstN(Table.ColumnNames(Sheet), List.PositionOf(List.Transform(Record.ToList(Sheet{0}), Text.Trim), "TOTAL", Occurrence.First, Comparer.OrdinalIgnoreCase)),
    removed = Table.RemoveColumns(Sheet, blankCols & totalCols)
  ][removed],
    RemovedBlankAndTotalRows = Table.SelectRows(RemovedBlankAndTotalCols, each (not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))) and (not List.Contains(Record.ToList(_), "TOTAL", (x,y)=> Text.StartsWith(Text.From(x) ?? "", y)))),
    Headers = Table.PromoteHeaders(Table.Skip(RemovedBlankAndTotalRows, each not List.Contains(Record.ToList(_), "unit price", Comparer.OrdinalIgnoreCase))),
    GroupedRows = Table.Group(Headers, "Column1", {{"T", each
        // [ T = GroupedRows{3}[T],
        [ T = _,
          Zones = List.Zip(List.TransformMany(Table.ToRows(Table.FirstN(RemovedBlankAndTotalCols, 2)),
            each {List.RemoveNulls(List.Skip(_, 3))},
            (x,y) => y )),
          Transformed = List.TransformMany(List.Skip(Table.ToRows(T)),
            each List.Split(List.Skip(_, 3), 4),
            (x,y)=> List.FirstN(Record.ToList(T{0}), 2) & List.FirstN(x, 3) & y),
          Combined = List.Transform(List.Zip({Transformed, List.Repeat(Zones, Table.RowCount(T)-1)}), List.Combine),
          ToTable = 
            [ colNames = {"Category Code", "Category Description"} & List.FirstN(Record.ToList(RemovedBlankAndTotalRows{0}), 3) & List.FirstN(List.Skip(Table.ColumnNames(T), each Text.StartsWith(_, "column", Comparer.OrdinalIgnoreCase)), 4) & {"ZONE", "SUBGROUP"},
              tbl = Table.FromRows(Combined, colNames)
            ][tbl]
        ][ToTable], type table}}, 0, (x,y)=> Byte.From(y is text)),
    CombinedT = Table.Combine(GroupedRows[T])
in
    CombinedT