Correct Sort Order in Table

[pjmorris]

I have some data shown below. I wish to convert that to the table shown below, but with the information in each column in alphabetical order. Note, that while the Captain doesn't have every category listed against his position in the input table he needs them all listed in the output. The current scheme creates two tblDelegations which are then combined and manipulated to create the output. The Powerquery M Code that does that is shown at the bottom. I confess I don't fully understand how the code works and, so, would be grateful for any advice on how I can get from the input to the output!

Input:

Copy of SY Ashley Accounts - TEST Ashley.xlsm
	I	J
6	Budget Holding Positions	Category
7	Captain	Capital Expense
8	Captain	Administration
9	Captain	Charter & Sale Related
10	Engineer	Comms & Subscription
11	Chief Steward	Crew
12	Engineer	Engineering Maintenance
13	Engineer	Fuel/Oil
14	Chief Officer	Hull & Deck Maintenance
15	Chief Steward	Interior Maintenance
16	Captain	Management
17	Chief Officer	Navigation
18	Captain	Payroll
19	Captain	Port Fees
20	Chief Officer	Safety & Security
21	Chief Officer	Tenders/Toys
22	Captain	Yacht Insurance
23	Chief Steward	Owner/Guest
24	Engineer	Refit
25	Chief Steward	VIP
26	Chief Officer	Ashley 2
Defaults

Incorrect Output (needs column data in alphabetical order):

Copy of SY Ashley Accounts - TEST Ashley.xlsm
		B	C	D	E
1		Captain	Chief Officer	Chief Steward	Engineer
2		Capital Expense	Hull & Deck Maintenance	Crew	Comms & Subscription
3		Administration	Navigation	Interior Maintenance	Engineering Maintenance
4		Charter & Sale Related	Safety & Security	Owner/Guest	Fuel/Oil
5		Management	Tenders/Toys	VIP	Refit
6		Payroll	Ashley 2
7		Port Fees
8		Yacht Insurance
9		Comms & Subscription
10		Crew
11		Engineering Maintenance
12		Fuel/Oil
13		Hull & Deck Maintenance
14		Interior Maintenance
15		Navigation
16		Safety & Security
17		Tenders/Toys
18		Owner/Guest
19		Refit
20		VIP
21		Ashley 2
Delegations

Relevant PowerQuery M Code:

let
    Source = Table.Combine({tblDelegations1, #"tblDelegations1 (2)"}),
    #"Removed Duplicates" = Table.Distinct(Source),
    FlatTable = Table.Sort(#"Removed Duplicates",{{"Budget Holding Positions", Order.Ascending}, {"Category", Order.Ascending}}),

    ColForValues = "Category",
    ColToPivot = "Budget Holding Positions",
    PivotColNames = List.Buffer(List.Distinct(Table.Column(FlatTable,ColToPivot))), 
    #"Pivoted Column" = Table.Pivot(FlatTable, PivotColNames, ColToPivot, ColForValues, each _),
    
    TableFromRecordOfLists = (rec as record, fieldnames as list) =>
    let
        PartialRecord = Record.SelectFields(rec,fieldnames),
        RecordToList = Record.ToList(PartialRecord),
        Table = Table.FromColumns(RecordToList,fieldnames)
    in
        Table

    ,#"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames))
    ,#"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames)
    ,#"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames)    

in
    #"Expanded Values"

Both tblDelegations are identical:

Copy of SY Ashley Accounts - TEST Ashley.xlsm
	A	B
1	Budget Holding Positions	Category
2	Captain	Administration
3	Chief Officer	Ashley 2
4	Captain	Capital Expense
5	Captain	Charter & Sale Related
6	Engineer	Comms & Subscription
7	Chief Steward	Crew
8	Engineer	Engineering Maintenance
9	Engineer	Fuel/Oil
10	Chief Officer	Hull & Deck Maintenance
11	Chief Steward	Interior Maintenance
12	Captain	Management
13	Chief Officer	Navigation
14	Chief Steward	Owner/Guest
15	Captain	Payroll
16	Captain	Port Fees
17	Engineer	Refit
18	Chief Officer	Safety & Security
19	Chief Officer	Tenders/Toys
20	Chief Steward	VIP
21	Captain	Yacht Insurance
Sheet1

 

[sandy666]

and even 13

 

[pjmorris]

or even 8 (could be less, but its getting beyond the silly stage to do so) - I've learnt a lot in the process, but would never publish code like it.

This is my code now:

let
    Source1 = Excel.CurrentWorkbook(){[Name="tblBdgtHoldgs"]}[Content],
    RemoveNulls = Table.SelectRows(#"Source1", each ([Budget Holding Positions] <> null)),
    Distinct = Table.Distinct(Table.Combine({RemoveNulls, Table.RenameColumns(Table.ReorderColumns(Table.RemoveColumns(Table.AddColumn(RemoveNulls, "Custom", each #"Source1"[#"Budget Holding Positions"]{0}),{"Budget Holding Positions"}),{"Custom", "Category"}),{{"Custom", "Budget Holding Positions"}})})),
    PivotColNames = List.Buffer(List.Sort(List.Distinct(Table.Column(Distinct, "Budget Holding Positions")), (x as text, y as text)=>if x=#"Source1"[#"Budget Holding Positions"]{0} then -1 else (if x<y then -1 else (if x=y then 0 else 1)))),
    Pivot = Table.Pivot(Distinct, PivotColNames, "Budget Holding Positions", "Category", each List.Sort(_, Order.Ascending))
in
    Table.ExpandTableColumn(Table.RemoveColumns(Table.AddColumn(Pivot, "Values", each Table.FromColumns(Record.ToList(Record.SelectFields(_,PivotColNames)),PivotColNames)),PivotColNames), "Values", PivotColNames)

 

[sandy666]

this way I can write all in one line, not accepted

 

[pjmorris]

this way I can write all in one line, not accepted

Lol, you have to accept it as you didn’t specify how to characterise a suitable reduction in lines! That said, I agree as it’s trivial way of reducing achieving the count and a pretty inelegant solution.

 

[sandy666]

no cheating, previous step in next step

so try again or end of challenge ?
(one day left)

 

[pjmorris]

so try again or end of challenge ?
(one day left)

Actually I hadn’t understood what you meant by ‘previous in next’, but now it’s obvious and is definitely a cheat.

but I’m going to suggest end of challenge as I have other things to work on. However, I’ve already learnt a lot and you’ve made me realise I still have much to learn - so thank you for throwing down the gauntlet. Any chance I can see your proper solution?

our posts will meet again I’m sure ?

 

[sandy666]

You started challenge and I agreed
my M? sure

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]

//12 lines