Correct Sort Order in Table

P

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
2,172
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
no prob, I've never really paid any attention to likes. Something else to consider when posting! Thanks again - you can like any of mine too :)
 
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Hi Sandy,

Well I can't quite get my head around the 'each _' and need to study some more. However, I have tidied up the code which works much more effectively than before - that said, its now decided that the column order should change (I don't want it to!), and additionally it occurs to me that we have 4 rows of code that are dependent on the data in table matching the code ie, Captain, Chief Officer, etc. If a yacht decides the budget holders can include the Bosun, for example, the code will stop working. I'd be grateful for any suggestions on how to remedy these two issues. Below is the current code and the Input/Output tables showing the incorrect column order.

Grateful for your insight.

Regards

Input Table:

Accounts - Aberlour 21yr Release -Development.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


Output Table - (column order incorrect)

Accounts - Aberlour 21yr Release -Development.xlsm
BCDE
1CaptainEngineerChief StewardChief Officer
2AdministrationComms & SubscriptionCrewAshley 2
3Ashley 2Engineering MaintenanceInterior MaintenanceHull & Deck Maintenance
4Capital ExpenseFuel/OilOwner/GuestNavigation
5Charter & Sale RelatedRefitVIPSafety & Security
6Comms & SubscriptionTenders/Toys
7Crew
8Engineering Maintenance
9Fuel/Oil
10Hull & Deck Maintenance
11Interior Maintenance
12Management
13Navigation
14Owner/Guest
15Payroll
16Port Fees
17Refit
18Safety & Security
19Tenders/Toys
20VIP
21Yacht Insurance
Delegations


M Code:

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 _),


    SortCapt = Table.AddColumn(Pivot, "Custom", each List.Sort([Captain], Order.Ascending)),
    SortChOff = Table.AddColumn(SortCapt, "Custom1", each List.Sort([Chief Officer], Order.Ascending)),
    SortChStw = Table.AddColumn(SortChOff, "Custom2", each List.Sort([Chief Steward], Order.Ascending)),
    SortEng = Table.AddColumn(SortChStw, "Custom3", each List.Sort([Engineer], Order.Ascending)),
    TSC = Table.SelectColumns(SortEng,{"Custom", "Custom1", "Custom2", "Custom3"}),
    Ren = Table.RenameColumns(TSC,{{"Custom", "Captain"}, {"Custom1", "Chief Officer"}, {"Custom2", "Chief Steward"}, {"Custom3", "Engineer"}}),

        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(Ren, "Values", each Function(_,PivotColNames)),
    RC = Table.RemoveColumns(TFR,PivotColNames),
    Expand = Table.ExpandTableColumn(RC, "Values", PivotColNames)
in
    Expand
 
Upvote 0
Same row order, column order:

Captain, Chief Officer, Chief Steward, Engineer
 
Upvote 0
test this one
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 _),


    SortCapt = Table.AddColumn(Pivot, "Custom", each List.Sort([Captain], Order.Ascending)),
    SortChOff = Table.AddColumn(SortCapt, "Custom1", each List.Sort([Chief Officer], Order.Ascending)),
    SortChStw = Table.AddColumn(SortChOff, "Custom2", each List.Sort([Chief Steward], Order.Ascending)),
    SortEng = Table.AddColumn(SortChStw, "Custom3", each List.Sort([Engineer], Order.Ascending)),
    TSC = Table.SelectColumns(SortEng,{"Custom", "Custom1", "Custom2", "Custom3"}),
    Ren = Table.RenameColumns(TSC,{{"Custom", "Captain"}, {"Custom1", "Chief Officer"}, {"Custom2", "Chief Steward"}, {"Custom3", "Engineer"}}),

        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(Ren, "Values", each Function(_,PivotColNames)),
    RC = Table.RemoveColumns(TFR,PivotColNames),
    Expand = Table.ExpandTableColumn(RC, "Values", PivotColNames)
in
    Expand
 
Upvote 0
Worked a treat thanks you. (lesson to myself, step through and understand what is happening!)

Finally, I hope, do you have any suggestions for how I might deal with other column headings in case a particular yacht wants to use names, or add additional budget holding positions? In either of those cases this section of code will obviously fail:

Rich (BB code): 
    SortCapt = Table.AddColumn(Pivot, "Custom", each List.Sort([Captain], Order.Ascending)),
    SortChOff = Table.AddColumn(SortCapt, "Custom1", each List.Sort([Chief Officer], Order.Ascending)),
    SortChStw = Table.AddColumn(SortChOff, "Custom2", each List.Sort([Chief Steward], Order.Ascending)),
    SortEng = Table.AddColumn(SortChStw, "Custom3", each List.Sort([Engineer], Order.Ascending)),
    TSC = Table.SelectColumns(SortEng,{"Custom", "Custom1", "Custom2", "Custom3"}),
    Ren = Table.RenameColumns(TSC,{{"Custom", "Captain"}, {"Custom1", "Chief Officer"}, {"Custom2", "Chief Steward"}, {"Custom3", "Engineer"}}),
 
Upvote 0
yup, for the sort order of columns it works perfectly.

But if I have a source data that looks like this where the position of Bosun has replaced Chief Officer it creates an error - the first that occurs is at the line 'SortChOff

Accounts - Aberlour 21yr Release -Development.xlsm
IJ
6Budget Holding PositionsCategory
7CaptainCapital Expense
8CaptainAdministration
9CaptainCharter & Sale Related
10EngineerComms & Subscription
11Chief StewardCrew
12EngineerEngineering Maintenance
13EngineerFuel/Oil
14BosunHull & Deck Maintenance
15Chief StewardInterior Maintenance
16CaptainManagement
17BosunNavigation
18CaptainPayroll
19CaptainPort Fees
20BosunSafety & Security
21BosunTenders/Toys
22CaptainYacht Insurance
23Chief StewardOwner/Guest
24EngineerRefit
25Chief StewardVIP
26BosunAsahi 2
Defaults
 
Upvote 0
Rich (BB code): 
let
    Source1 = Excel.CurrentWorkbook(){[Name="tblBdgtHoldgs"]}[Content],
    SortAsc = Table.Sort(Source1,{{"Budget Holding Positions", Order.Ascending}, {"Category", Order.Ascending}}),
    RemoveNulls = Table.SelectRows(SortAsc, 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 _),


    SortCapt = Table.AddColumn(Pivot, "Custom", each List.Sort([Captain], Order.Ascending)),
    RCCpt = Table.RemoveColumns(SortCapt,{"Captain"}),
    Ren = Table.RenameColumns(RCCpt,{{"Custom", "Captain"}}),
    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(Ren, "Values", each Function(_,PivotColNames)),
    RC = Table.RemoveColumns(TFR,PivotColNames),
    Expand = Table.ExpandTableColumn(RC, "Values", PivotColNames)
in
    Expand
you need to define sort order for columns, now it is Ascending (A-Z) and sort for Bosum
 
Last edited:
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
4K
mlou
M
Q
  • Question Question
Power Query: Remove Duplicates Based on Sort Order
Replies
3
Views
6K
MarcelBeug
MarcelBeug
D
  • Question Question
[VBA] Fill arrays from sheet | SUMIFS | create & fill table based on multiple criteria
Replies
2
Views
1K
Dimitris254
D

Forum statistics

Threads
1,223,787
Messages
6,174,561
Members
452,573
Latest member
Cpiet

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