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]

like this?

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

 

[pjmorris]

that would be perfect!

 

[sandy666]

first could you explain why you are doing this

   Source = Table.Combine({tblDelegations1, #"tblDelegations1 (2)"}),
    #"Removed Duplicates" = Table.Distinct(Source),

if

Both tblDelegations are identical:

because it doesn't make any sense in case above or I am stupid

 

[pjmorris]

Point 1 - you're far from stupid, which is why I rather hoped you'd reply!!
Point 2 - I am, and should have checked exactly what is happening (its a while since I created these tables). The two tables are very similar, but not quite as shown below!

Apologies for making interpretation of the question difficult, but hope this clarifies.

Regards

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

 

[sandy666]

so you shouldn't say they are identical
anyway see this for single table (you can adapt this to your un-identical tables )
I suggest to look at second code line

let
//this is first table--------------------------------------------------------------------------------------------------
    Source = Excel.CurrentWorkbook(){[Name="tblDelegations1"]}[Content],
    SortAsc = Table.Sort(Source,{{"Budget Holding Positions", Order.Ascending}, {"Category", Order.Ascending}}),
//------------------------------------do the same for the second table------------------------------------------------

    FlatTable = Table.Sort(SortAsc,{{"Budget Holding Positions", Order.Ascending}, {"Category", Order.Ascending}}),
    ColForValues = "Category",
    ColToPivot = "Budget Holding Positions",
    PivotColNames = List.Buffer(List.Distinct(Table.Column(FlatTable,ColToPivot))),
    Pivot = Table.Pivot(FlatTable, PivotColNames, ColToPivot, ColForValues, each _),

    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

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

 

[sandy666]

btw. with tables from post#5 you will get table with single row

Captain	Chief Officer	Chief Steward	Engineer
Administration	Ashley 2	Crew	Comms & Subscription

 

[sandy666]

my fault, should be

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

 

[pjmorris]

Hi Sandy,

I had already sorted both source tables before joining them, I even sorted both columns after the combine:

This is Line 3 in the code at post #1: FlatTable = Table.Sort(#"Removed Duplicates",{{"Budget Holding Positions", Order.Ascending}, {"Category", Order.Ascending}}),

So I think I've already done as you suggest - I really don't know why it seems to revert to unsorted.

Note that your table in Post #8 is also wrong as 'Ashley 2' in the Captain's list should appear in the second line between Administration and Capital Expense.

Grateful for you looking at this.

Regards

 

[sandy666]

With your tables from post#5 my table is ok
I didn't transform anything except initial sort source tables and use you code
you said you need ascending order in all columns, that's all
show expected real result and we will think