PQ - Sort columns and rows

[sandy666]

• rows are sorted ascending (A-Z)
• columns are sorted ascending (A-Z) except first column which is TeamLeader's column
• Person from the first top left cell is a Team Leader (in this case: Captain)
• Team Leader's name can be changed and still will be in the first column (in this case: Captain)
• code is squeezed as much as I could do that

Source

Budget Holding Positions	Category
Captain	Capital Expense
Captain	Administration
Captain	Charter & Sale Related
Engineer	Comms & Subscription
Chief Steward	Crew
Engineer	Engineering Maintenance
Engineer	Fuel/Oil
Bosum	Hull & Deck Maintenance
Chief Steward	Interior Maintenance
Captain	Management
Bosum	Navigation
Captain	Payroll
Captain	Port Fees
Bosum	Safety & Security
Bosum	Tenders/Toys
Captain	Yacht Insurance
Chief Steward	Owner/Guest
Engineer	Refit
Chief Steward	VIP
Bosum	Asahi 2
Bosum	X-ray
sandy666	Power Query
	vba
Engineer	A-bomb

Result

Captain	Bosum	Chief Steward	Engineer	sandy666
A-bomb	Asahi 2	Crew	A-bomb	Power Query
Administration	Hull & Deck Maintenance	Interior Maintenance	Comms & Subscription
Asahi 2	Navigation	Owner/Guest	Engineering Maintenance
Capital Expense	Safety & Security	VIP	Fuel/Oil
Charter & Sale Related	Tenders/Toys		Refit
Comms & Subscription	X-ray
Crew
Engineering Maintenance
Fuel/Oil
Hull & Deck Maintenance
Interior Maintenance
Management
Navigation
Owner/Guest
Payroll
Port Fees
Power Query
Refit
Safety & Security
Tenders/Toys
VIP
X-ray
Yacht Insurance
vba

// squeezed12
let Source = Excel.CurrentWorkbook(){[Name="tblBdgtHoldgs"]}[Content],
    Category = Table.SelectColumns(Source,{"Category"}),
    Name = Table.AddColumn(Category, "Name", each Record.RemoveFields(Table.First(Source, {[Budget Holding Positions]}), "Category")),
    BHP = Table.TransformColumns(Table.ExpandRecordColumn(Name, "Name", {"Budget Holding Positions"}, {"Budget Holding Positions"}), {{"Budget Holding Positions", each " " & _, type text}}),
    Join = Table.Distinct(Table.Combine({BHP, Source})),
    TSR = Table.SelectRows(Join, each [Budget Holding Positions] <> List.First(Source[Budget Holding Positions]) and [Budget Holding Positions] <> null and [Category] <> null),
    Headers = List.Sort(List.Distinct(Table.Column(TSR, "Budget Holding Positions")), Order.Ascending),
    Pivot = Table.Pivot(TSR, Headers, "Budget Holding Positions", "Category", each List.Sort(_, Order.Ascending)),
        Function = (rec as record, fieldnames as list) =>
            let RTL = Record.ToList(Record.SelectFields(rec,fieldnames))
            in  Table.FromColumns(RTL,fieldnames)
in  Table.AddColumn(Pivot, "Values", each Function(_, Headers)){0}[Values]