Correct Sort Order in Table

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
2,158
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
or even 8 (could be less, but its getting beyond the silly stage to do so) - I've learnt a lot in the process, but would never publish code like it.

This is my code now:

VBA Code:
let
    Source1 = Excel.CurrentWorkbook(){[Name="tblBdgtHoldgs"]}[Content],
    RemoveNulls = Table.SelectRows(#"Source1", each ([Budget Holding Positions] <> null)),
    Distinct = Table.Distinct(Table.Combine({RemoveNulls, Table.RenameColumns(Table.ReorderColumns(Table.RemoveColumns(Table.AddColumn(RemoveNulls, "Custom", each #"Source1"[#"Budget Holding Positions"]{0}),{"Budget Holding Positions"}),{"Custom", "Category"}),{{"Custom", "Budget Holding Positions"}})})),
    PivotColNames = List.Buffer(List.Sort(List.Distinct(Table.Column(Distinct, "Budget Holding Positions")), (x as text, y as text)=>if x=#"Source1"[#"Budget Holding Positions"]{0} then -1 else (if x<y then -1 else (if x=y then 0 else 1)))),
    Pivot = Table.Pivot(Distinct, PivotColNames, "Budget Holding Positions", "Category", each List.Sort(_, Order.Ascending))
in
    Table.ExpandTableColumn(Table.RemoveColumns(Table.AddColumn(Pivot, "Values", each Table.FromColumns(Record.ToList(Record.SelectFields(_,PivotColNames)),PivotColNames)),PivotColNames), "Values", PivotColNames)
 
Upvote 0
(n) this way I can write all in one line, not accepted
Lol, you have to accept it as you didn’t specify how to characterise a suitable reduction in lines! That said, I agree as it’s trivial way of reducing achieving the count and a pretty inelegant solution.
 
Upvote 0
:devilish:
so try again or end of challenge ?
(one day left)
Actually I hadn’t understood what you meant by ‘previous in next’, but now it’s obvious and is definitely a cheat.

but I’m going to suggest end of challenge as I have other things to work on. However, I’ve already learnt a lot and you’ve made me realise I still have much to learn - so thank you for throwing down the gauntlet. Any chance I can see your proper solution?

our posts will meet again I’m sure ?
 
Upvote 0
You started challenge and I agreed :biggrin:
my M? sure
Rich (BB code):
let Source = Excel.CurrentWorkbook(){[Name="tblBdgtHoldgs"]}[Content],
    Category = Table.SelectColumns(Source,{"Category"}),
    Name = Table.AddColumn(Category, "Name", each Record.RemoveFields(Table.First(Source, {[Budget Holding Positions]}), "Category")),
    BHP = Table.TransformColumns(Table.ExpandRecordColumn(Name, "Name", {"Budget Holding Positions"}, {"Budget Holding Positions"}), {{"Budget Holding Positions", each " " & _, type text}}),
    Join = Table.Distinct(Table.Combine({BHP, Source})),
    TSR = Table.SelectRows(Join, each [Budget Holding Positions] <> List.First(Source[Budget Holding Positions]) and [Budget Holding Positions] <> null and [Category] <> null),
    Headers = List.Sort(List.Distinct(Table.Column(TSR, "Budget Holding Positions")), Order.Ascending),
    Pivot = Table.Pivot(TSR, Headers, "Budget Holding Positions", "Category", each List.Sort(_, Order.Ascending)),
        Function = (rec as record, fieldnames as list) =>
            let RTL = Record.ToList(Record.SelectFields(rec,fieldnames))
            in  Table.FromColumns(RTL,fieldnames)
in  Table.AddColumn(Pivot, "Values", each Function(_, Headers)){0}[Values]

//12 lines
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

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