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]

Done it - wonder whether my solution is the same as yours?

Accounts - Aberlour 21yr Release -Development.xlsm
	B	C	D	E
1	Captain	Bosun	Chief Steward	Engineer
2	Administration	Asahi 2	Crew	Comms & Subscription
3	Asahi 2	Hull & Deck Maintenance	Interior Maintenance	Engineering Maintenance
4	Capital Expense	Navigation	Owner/Guest	Fuel/Oil
5	Charter & Sale Related	Safety & Security	VIP	Refit
6	Comms & Subscription	Tenders/Toys
7	Crew
8	Engineering Maintenance
9	Fuel/Oil
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

and the relevant code:

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)), (x as text, y as text)=>
        let
            isCaptain=if x="Captain" then -1 else null,
            other=if x<y then -1 else (if x=y then 0 else 1),
            result= if isCaptain=-1 then isCaptain else other
        in
            result

)),

    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

 

[sandy666]

I can say it works
and no, this is not the same as my M
I didn't use any additional function
Lines: 16 vs 22

Challenge goes on ? (4 days left)

 

[sandy666]

update:
all lines: 20 vs 32 (no blanks)
no cheating, previous step in next step

 

[sandy666]

16 vs 32 and still works

 

[pjmorris]

Ok, I've removed a number of superfluous lines, but still no where near your 16. That said, minimum lines wasn't part of the challenge and I prefer to ensure that code is readable rather than short (it helps debug it later!). Additionally, I've changed the functionality slightly. Now, the 'Captain' position is whoever is first listed in the Source table as a 'Budget Holding Position' this means that the yacht can use names rather than positions and still identify who is the captain. There's probably a more elegant way to do this, but it suits the evolution of the app. I shall continue to work at reducing the number of lines (now down to 25!), but while I do so, this is the source data followed by the output I want.

Source:

Accounts - Aberlour 21yr Release -Development.xlsm
	I	J
6	Budget Holding Positions	Category
7	Xavier	Capital Expense
8	Xavier	Administration
9	Xavier	Charter & Sale Related
10	Haley	Comms & Subscription
11	Eric	Crew
12	Haley	Engineering Maintenance
13	Haley	Fuel/Oil
14	Bosun	Hull & Deck Maintenance
15	Eric	Interior Maintenance
16	Xavier	Management
17	Bosun	Navigation
18	Xavier	Payroll
19	Xavier	Port Fees
20	Bosun	Safety & Security
21	Bosun	Tenders/Toys
22	Xavier	Yacht Insurance
23	Eric	Owner/Guest
24	Haley	Refit
25	Eric	VIP
26	Bosun	Ashley2
Defaults

Output:

Accounts - Aberlour 21yr Release -Development.xlsm
	B	C	D	E
1	Xavier	Bosun	Eric	Haley
2	Administration	Ashley2	Crew	Comms & Subscription
3	Ashley2	Hull & Deck Maintenance	Interior Maintenance	Engineering Maintenance
4	Capital Expense	Navigation	Owner/Guest	Fuel/Oil
5	Charter & Sale Related	Safety & Security	VIP	Refit
6	Comms & Subscription	Tenders/Toys
7	Crew
8	Engineering Maintenance
9	Fuel/Oil
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]

minimum lines wasn't part of the challenge

of course not but would be nice if there is less lines than more
proper M doesn't need debugging
changing Captain to any name wasn't part of challenge ?
hard to say something only from the two tables without M

 

[pjmorris]

proper M doesn't need debugging

Well M doesn't have any debugging tools - you have to break the code down into small segments to see what is happening. And I doubt anyone ever, always, wrote bug free code.

Well the code that delivers the output from the source is only marginally different from the code above - actually, all I've done is replace the 'Captain' with (inline) a short piece of M. Thats your challenge

I possibly won't be online much in the next couple of days (certainly not for deep thinking!) as I have to paint the kitchen!!

 

[sandy666]

M with bugs doesn't work otherwise works
Do you want to bully me with a new challenge? ?

I have to paint the kitchen!!

Good luck ??

 

[sandy666]

done, unfortunately 18 lines (I'd like less but I think 18 is a good number, isn't it?)
and working with different names

 

[sandy666]

back to 16 lines ?