Correct Sort Order in Table

P

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
2,159
Office Version
  1. 365
Platform
  1. Windows
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
IJ
6Budget Holding PositionsCategory
7CaptainCapital Expense
8CaptainAdministration
9CaptainCharter & Sale Related
10EngineerComms & Subscription
11Chief StewardCrew
12EngineerEngineering Maintenance
13EngineerFuel/Oil
14Chief OfficerHull & Deck Maintenance
15Chief StewardInterior Maintenance
16CaptainManagement
17Chief OfficerNavigation
18CaptainPayroll
19CaptainPort Fees
20Chief OfficerSafety & Security
21Chief OfficerTenders/Toys
22CaptainYacht Insurance
23Chief StewardOwner/Guest
24EngineerRefit
25Chief StewardVIP
26Chief OfficerAshley 2
Defaults


Incorrect Output (needs column data in alphabetical order):

Copy of SY Ashley Accounts - TEST Ashley.xlsm
BCDE
1CaptainChief OfficerChief StewardEngineer
2Capital ExpenseHull & Deck MaintenanceCrewComms & Subscription
3AdministrationNavigationInterior MaintenanceEngineering Maintenance
4Charter & Sale RelatedSafety & SecurityOwner/GuestFuel/Oil
5ManagementTenders/ToysVIPRefit
6PayrollAshley 2
7Port Fees
8Yacht Insurance
9Comms & Subscription
10Crew
11Engineering Maintenance
12Fuel/Oil
13Hull & Deck Maintenance
14Interior Maintenance
15Navigation
16Safety & Security
17Tenders/Toys
18Owner/Guest
19Refit
20VIP
21Ashley 2
Delegations


Relevant PowerQuery M Code:

Rich (BB 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
AB
1Budget Holding PositionsCategory
2CaptainAdministration
3Chief OfficerAshley 2
4CaptainCapital Expense
5CaptainCharter & Sale Related
6EngineerComms & Subscription
7Chief StewardCrew
8EngineerEngineering Maintenance
9EngineerFuel/Oil
10Chief OfficerHull & Deck Maintenance
11Chief StewardInterior Maintenance
12CaptainManagement
13Chief OfficerNavigation
14Chief StewardOwner/Guest
15CaptainPayroll
16CaptainPort Fees
17EngineerRefit
18Chief OfficerSafety & Security
19Chief OfficerTenders/Toys
20Chief StewardVIP
21CaptainYacht Insurance
Sheet1
 
Sort by date Sort by votes
OK, I'll have to run whats here and see if I get the same result, but from what I'm seeing you're 'Captain' column in the table at post #8 in't correct either - does that change anything?
 
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
there are two tables and each has own sort
blue is for Captain only and it is sorted correctly

CaptainChief OfficerChief StewardEngineer
AdministrationAshley 2CrewComms & Subscription
Capital ExpenseHull & Deck MaintenanceInterior MaintenanceEngineering Maintenance
Charter & Sale RelatedNavigationOwner/GuestFuel/Oil
ManagementSafety & SecurityVIPRefit
PayrollTenders/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

but test Table.NestedJoin to be sure
 
Upvote 0
or maybe something like this one:

CaptainChief OfficerChief StewardEngineer
AdministrationAshley 2CrewComms & Subscription
Ashley 2Hull & Deck MaintenanceInterior MaintenanceEngineering Maintenance
Capital ExpenseNavigationOwner/GuestFuel/Oil
Charter & Sale RelatedSafety & SecurityVIPRefit
Comms & SubscriptionTenders/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
 
Upvote 0
Just about to have a go with your suggestions - but yes the table at #15 is the outcome I'm trying to achieve.
 
Upvote 0
sure
after step with Table.Pivot you need to add step with custom column with this: List.Sort([Captain], Order.Ascending)
and do some little things after that
 
Upvote 0
I'm having a mare with this!!

I've tried to use the Table.NestedJoin, but that seems to make no difference. I certainly can't get the result you've managed to do. Even when I have the table at FlatTable step correct the result of the custom formula seems to revert to an order that matches the absolute original source table!! Just to make sure I'm not completely losing it, I've just tried again and the detail below starts with the source table (I hadn't thought it would make a difference when I posted the two previous tables) and then shows the relevant M Code and the result at each stage. It really looks as if Excel goes back to the original data table in the custom function even though that doesnt make sense!). If you can sort this please post a complete M Code - I'm really struggling to understand this one.

Thanks for the help.,

I started with this table:

Book1 (Recovered).xlsx
AB
30Budget Holding PositionsCategory
31CaptainYacht Insurance
32CaptainPort Fees
33CaptainPayroll
34CaptainManagement
35CaptainCharter & Sale Related
36CaptainCapital Expense
37CaptainAdministration
38Chief OfficerTenders/Toys
39Chief OfficerSafety & Security
40Chief OfficerNavigation
41Chief OfficerHull & Deck Maintenance
42Chief OfficerAshley 2
43Chief StewardVIP
44Chief StewardOwner/Guest
45Chief StewardInterior Maintenance
46Chief StewardCrew
47EngineerRefit
48EngineerFuel/Oil
49EngineerEngineering Maintenance
50EngineerComms & Subscription
Sheet1


Then I added it to Powerquery with this M Code:

Rich (BB code): 
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Budget Holding Positions", type text}, {"Category", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Budget Holding Positions", Order.Ascending}, {"Category", Order.Ascending}})
in
    #"Sorted Rows"

This results in this table (note that the entries for each position are now in the correct sequence):

Book1 (Recovered).xlsx
EF
30Budget Holding PositionsCategory
31CaptainAdministration
32CaptainCapital Expense
33CaptainCharter & Sale Related
34CaptainManagement
35CaptainPayroll
36CaptainPort Fees
37CaptainYacht Insurance
38Chief OfficerAshley 2
39Chief OfficerHull & Deck Maintenance
40Chief OfficerNavigation
41Chief OfficerSafety & Security
42Chief OfficerTenders/Toys
43Chief StewardCrew
44Chief StewardInterior Maintenance
45Chief StewardOwner/Guest
46Chief StewardVIP
47EngineerComms & Subscription
48EngineerEngineering Maintenance
49EngineerFuel/Oil
50EngineerRefit
Sheet1


I then duplicated this table and created a table just for the Captain using this M Code:

Rich (BB code): 
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Budget Holding Positions", type text}, {"Category", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Budget Holding Positions", Order.Ascending}, {"Category", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Budget Holding Positions"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Budget Holding Positions", each "Captain"),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Budget Holding Positions", "Category"}),
    #"Sorted Rows1" = Table.Sort(#"Reordered Columns",{{"Category", Order.Ascending}})
in
    #"Sorted Rows1"

Which results in this table:

Book1 (Recovered).xlsx
HI
30Budget Holding PositionsCategory
31CaptainAdministration
32CaptainAshley 2
33CaptainCapital Expense
34CaptainCharter & Sale Related
35CaptainComms & Subscription
36CaptainCrew
37CaptainEngineering Maintenance
38CaptainFuel/Oil
39CaptainHull & Deck Maintenance
40CaptainInterior Maintenance
41CaptainManagement
42CaptainNavigation
43CaptainOwner/Guest
44CaptainPayroll
45CaptainPort Fees
46CaptainRefit
47CaptainSafety & Security
48CaptainTenders/Toys
49CaptainVIP
50CaptainYacht Insurance
Sheet1


Finally, using this code (which you've pretty much seen before, but in which I've been unable to implement your amendments):

Rich (BB code): 
let
    Source = Table.Combine({tblOne, #"tblTwo"}),
    #"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"

I get this table, which is completely wrong! Worth noting that at the FlatTable step in the M Code the table looks to be sorted exactly as I think it should.

Book1 (Recovered).xlsx
EFGH
54CaptainChief OfficerChief StewardEngineer
55Yacht InsuranceTenders/ToysVIPRefit
56Port FeesSafety & SecurityOwner/GuestFuel/Oil
57PayrollNavigationInterior MaintenanceEngineering Maintenance
58ManagementHull & Deck MaintenanceCrewComms & Subscription
59Charter & Sale RelatedAshley 2
60Capital Expense
61Administration
62Tenders/Toys
63Safety & Security
64Navigation
65Hull & Deck Maintenance
66Ashley 2
67VIP
68Owner/Guest
69Interior Maintenance
70Crew
71Refit
72Fuel/Oil
73Engineering Maintenance
74Comms & Subscription
Sheet1
 
Upvote 0
I said after step with Pivot.Table
Rich (BB code): 
    Pivot = Table.Pivot(Distinct, PivotColNames, ColToPivot, ColForValues, each _),
    SortCapt = Table.AddColumn(Pivot, "Custom", each List.Sort([Captain], Order.Ascending)),
    TSC = Table.SelectColumns(SortCapt,{"Custom", "Chief Officer", "Chief Steward", "Engineer"}),
    Ren = Table.RenameColumns(TSC,{{"Custom", "Captain"}}),

d'ya want whole M or you will try do that yourself ?
 
Upvote 0
Yes, I've done that and the outcome is exactly the same. I expanded the list that occurs at the Ren=Table.RenameColumns step and that is correctly ordered, but by the time the code completes its back to incorrect! This is the M Code I'm now using - is it the same as yours?

Rich (BB code): 
let
    Source = Table.Combine({tblOne, #"tblTwo"}),
    #"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 _),
    SortCapt = Table.AddColumn(#"Pivoted Column", "Custom", each List.Sort([Captain], Order.Ascending)),
    TSC = Table.SelectColumns(SortCapt,{"Custom", "Chief Officer", "Chief Steward", "Engineer"}),
    Ren = Table.RenameColumns(TSC,{{"Custom", "Captain"}}),

    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"
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Function Not Working - Sort & Remove Duplicates by Creating New Array
Replies
2
Views
3K
mlou
M
Q
  • Question
Power Query: Remove Duplicates Based on Sort Order
Replies
3
Views
6K
MarcelBeug
MarcelBeug
D
  • Question
[VBA] Fill arrays from sheet | SUMIFS | create & fill table based on multiple criteria
Replies
2
Views
965
Dimitris254
D

Forum statistics

Threads
1,214,834
Messages
6,121,876
Members
449,056
Latest member
ruhulaminappu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top