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 PositionsCategory

CaptainCapital Expense

CaptainAdministration

CaptainCharter & Sale Related

EngineerComms & Subscription

Chief StewardCrew

EngineerEngineering Maintenance

EngineerFuel/Oil

BosumHull & Deck Maintenance

Chief StewardInterior Maintenance

CaptainManagement

BosumNavigation

CaptainPayroll

CaptainPort Fees

BosumSafety & Security

BosumTenders/Toys

CaptainYacht Insurance

Chief StewardOwner/Guest

EngineerRefit

Chief StewardVIP

BosumAsahi 2



BosumX-ray



sandy666Power Query

vba

EngineerA-bomb


Result

CaptainBosumChief StewardEngineersandy666

A-bombAsahi 2CrewA-bombPower Query

AdministrationHull & Deck MaintenanceInterior MaintenanceComms & Subscription

Asahi 2NavigationOwner/GuestEngineering Maintenance

Capital ExpenseSafety & SecurityVIPFuel/Oil

Charter & Sale RelatedTenders/ToysRefit

Comms & SubscriptionX-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(Na me, "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]