Correct Sort Order in Table

P

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
2,161
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
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
BCDE
1BosunCaptainChief StewardGrease Monkey
2Hull & Deck MaintenanceAdministrationCrewComms & Subscription
3NavigationAsahi 2Interior MaintenanceGrease Monkeying Maintenance
4Safety & SecurityCapital ExpenseOwner/GuestFuel/Oil
5Tenders/ToysCharter & Sale RelatedVIPRefit
6Asahi 2Comms & Subscription
7Crew
8Fuel/Oil
9Grease Monkeying Maintenance
10Hull & Deck Maintenance
11Interior Maintenance
12Management
13Navigation
14Owner/Guest
15Payroll
16Port Fees
17Refit
18Safety & Security
19Tenders/Toys
20VIP
21Yacht Insurance
Delegations
 
Upvote 0

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
try

Rich (BB 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)), 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
BosumCaptainChief StewardEngineer
Asahi 2AdministrationCrewComms & Subscription
Hull & Deck MaintenanceAsahi 2Interior MaintenanceEngineering Maintenance
NavigationCapital ExpenseOwner/GuestFuel/Oil
Safety & SecurityCharter & Sale RelatedVIPRefit
Tenders/ToysComms & 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
 
Upvote 0
or if you want like this

CaptainEngineerChief StewardBosum
AdministrationComms & SubscriptionCrewAsahi 2
Asahi 2Engineering MaintenanceInterior MaintenanceHull & Deck Maintenance
Capital ExpenseFuel/OilOwner/GuestNavigation
Charter & Sale RelatedRefitVIPSafety & Security
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

Rich (BB 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.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
 
Upvote 0
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.
 
Upvote 0
you are welcome
thanks for the feedback

pjmorris said:
Whenever I offer to help someone on here - feel free to improve my idea!
Click to expand...
thanks for the permission :biggrin: :biggrin: :biggrin:

but i don't like the result because columns are not sorted A-Z (except Captain which should be as first column always)
 
Upvote 0
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?
 
Upvote 0
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).

:)
 
Upvote 0
your goal ;) :cool:

CaptainBosumChief StewardEngineer
AdministrationAsahi 2CrewComms & Subscription
Asahi 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
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
966
Dimitris254
D

Forum statistics

Threads
1,214,923
Messages
6,122,289
Members
449,077
Latest member
Rkmenon

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