Correct Sort Order in Table

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,903
Office Version
2016
Platform
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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,757
like this?

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
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,757
first could you explain why you are doing this
Rich (BB code):
   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

Well-known Member
Joined
Aug 2, 2012
Messages
1,903
Office Version
2016
Platform
Windows
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
ABCDEF
1tblDelegations1tblDelegations1 (2)
2Budget Holding PositionsCategoryBudget Holding PositionsCategory
3CaptainAdministrationCaptainAdministration
4Chief OfficerAshley 2CaptainAshley 2
5CaptainCapital ExpenseCaptainCapital Expense
6CaptainCharter & Sale RelatedCaptainCharter & Sale Related
7EngineerComms & SubscriptionCaptainComms & Subscription
8Chief StewardCrewCaptainCrew
9EngineerEngineering MaintenanceCaptainEngineering Maintenance
10EngineerFuel/OilCaptainFuel/Oil
11Chief OfficerHull & Deck MaintenanceCaptainHull & Deck Maintenance
12Chief StewardInterior MaintenanceCaptainInterior Maintenance
13CaptainManagementCaptainManagement
14Chief OfficerNavigationCaptainNavigation
15Chief StewardOwner/GuestCaptainOwner/Guest
16CaptainPayrollCaptainPayroll
17CaptainPort FeesCaptainPort Fees
18EngineerRefitCaptainRefit
19Chief OfficerSafety & SecurityCaptainSafety & Security
20Chief OfficerTenders/ToysCaptainTenders/Toys
21Chief StewardVIPCaptainVIP
22CaptainYacht InsuranceCaptainYacht Insurance
Sheet1
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,757
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 :cool:
Rich (BB code):
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
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
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,757
btw. with tables from post#5 you will get table with single row
CaptainChief OfficerChief StewardEngineer
AdministrationAshley 2CrewComms & Subscription
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,757
my fault, should be
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
 
Last edited:

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,903
Office Version
2016
Platform
Windows
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,757
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,141
Messages
5,484,982
Members
407,478
Latest member
wsupaul

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top