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

 

[pjmorris]

That works to change the column headings, but now the rows are not sorted, because we've removed the SortChOff, SortEng rows from the M Code. If this were vba I'd simply look through each column to sort, but I don't know how to write the equivalent in M.

Accounts - Aberlour 21yr Release -Development.xlsm
	B	C	D	E
1	Bosun	Captain	Chief Steward	Grease Monkey
2	Hull & Deck Maintenance	Administration	Crew	Comms & Subscription
3	Navigation	Asahi 2	Interior Maintenance	Grease Monkeying Maintenance
4	Safety & Security	Capital Expense	Owner/Guest	Fuel/Oil
5	Tenders/Toys	Charter & Sale Related	VIP	Refit
6	Asahi 2	Comms & Subscription
7		Crew
8		Fuel/Oil
9		Grease Monkeying Maintenance
10		Hull & Deck Maintenance
11		Interior Maintenance
12		Management
13		Navigation
14		Owner/Guest
15		Payroll
16		Port Fees
17		Refit
18		Safety & Security
19		Tenders/Toys
20		VIP
21		Yacht Insurance
Delegations

 

[sandy666]

try

let
    Source1 = Excel.CurrentWorkbook(){[Name="tblBdgtHoldgs"]}[Content],
    RemoveNulls = Table.SelectRows(Source1, each ([Budget Holding Positions] <> null)),
    #"Added Custom" = Table.AddColumn(RemoveNulls, "Custom", each "Captain"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Budget Holding Positions"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Category"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "Budget Holding Positions"}}),

    Join = Table.Combine({RemoveNulls, #"Renamed Columns"}),
    Distinct = Table.Distinct(Join),

    ColForValues = "Category",
    ColToPivot = "Budget Holding Positions",

    PivotColNames = List.Buffer(List.Sort(List.Distinct(Table.Column(Distinct, ColToPivot)), Order.Ascending)),
    Pivot = Table.Pivot(Distinct, PivotColNames, ColToPivot, ColForValues, each List.Sort(_, Order.Ascending)),


    Function = (rec as record, fieldnames as list) =>
        let
            PartialRecord = Record.SelectFields(rec,fieldnames),
            RecordToList = Record.ToList(PartialRecord),
            Table = Table.FromColumns(RecordToList,fieldnames)
        in
            Table,

    TFR = Table.AddColumn(Pivot, "Values", each Function(_,PivotColNames)),
    RC = Table.RemoveColumns(TFR,PivotColNames),
    Expand = Table.ExpandTableColumn(RC, "Values", PivotColNames)
in
    Expand

you need define custom sort for columns

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

 

[sandy666]

or if you want like this

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

let
    Source1 = Excel.CurrentWorkbook(){[Name="tblBdgtHoldgs"]}[Content],
    RemoveNulls = Table.SelectRows(Source1, each ([Budget Holding Positions] <> null)),
    #"Added Custom" = Table.AddColumn(RemoveNulls, "Custom", each "Captain"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Budget Holding Positions"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Category"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "Budget Holding Positions"}}),

    Join = Table.Combine({RemoveNulls, #"Renamed Columns"}),
    Distinct = Table.Distinct(Join),

    ColForValues = "Category",
    ColToPivot = "Budget Holding Positions",

    PivotColNames = List.Buffer(List.Distinct(Table.Column(Distinct, ColToPivot))),
    Pivot = Table.Pivot(Distinct, PivotColNames, ColToPivot, ColForValues, each List.Sort(_, Order.Ascending)),


    Function = (rec as record, fieldnames as list) =>
        let
            PartialRecord = Record.SelectFields(rec,fieldnames),
            RecordToList = Record.ToList(PartialRecord),
            Table = Table.FromColumns(RecordToList,fieldnames)
        in
            Table,

    TFR = Table.AddColumn(Pivot, "Values", each Function(_,PivotColNames)),
    RC = Table.RemoveColumns(TFR,PivotColNames),
    Expand = Table.ExpandTableColumn(RC, "Values", PivotColNames)
in
    Expand

 

[pjmorris]

Sandy,

Absolutely brilliant - obvious when you show me! Whenever I offer to help someone on here - feel free to improve my idea!

Really appreciated your help - and unbelievably quick replies.

Thank you - Likes added as appropriate.

 

[sandy666]

you are welcome
thanks for the feedback

Whenever I offer to help someone on here - feel free to improve my idea!

thanks for the permission

but i don't like the result because columns are not sorted A-Z (except Captain which should be as first column always)

 

[pjmorris]

Actually I agree with you - my preferred solution is actually post #42 which has Bosun first, which is correct alphabetically. I think I can live with the Captain not being the first in this instance - though if you could make Captain first and everyone else alphabetical that would be amazing - I guess we'd need a custom sort order?

 

[sandy666]

surprise me, do something yourself

 

[pjmorris]

lol, sarcasm is hurtful, I do a lot more than most, but not as much as some. That said its a fair challenge - so dont answer any more of my queries for a week and I'll see if I can learn enough to make it work (though I've no idea how - yet).

 

[sandy666]

the most important thing is that you want to do it

 

[sandy666]

your goal

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