Correct Sort Order in Table

P

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
2,171
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
a bit different because I am using single query
IMHO, FlatTable is not necessary

Rich (BB code): 
let
    Source1 = Excel.CurrentWorkbook(){[Name="tblDelegations1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="tblDelegations2"]}[Content],

    SortAsc1 = Table.Sort(Source1,{{"Budget Holding Positions", Order.Ascending}, {"Category", Order.Ascending}}),
    SortAsc2 = Table.Sort(Source2,{{"Budget Holding Positions", Order.Ascending}, {"Category", Order.Ascending}}),

    Join = Table.Combine({SortAsc1, SortAsc2}),
    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)),
    TSC = Table.SelectColumns(SortCapt,{"Custom", "Chief Officer", "Chief Steward", "Engineer"}),
    Ren = Table.RenameColumns(TSC,{{"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
 
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Well I tried your M Code, but the list for the Chief Officer was incorrect, so I tried sorting that list following your model above - I thought it had worked until it hit the function and then it reverted. I'm clearly missing something about the way this code operates. For info, my current M Code is below. For now I'm off to bed and will post a link to the spreadsheet tomorrow - many thanks for sticking with this.

Rich (BB code): 
let
    Source1 = Excel.CurrentWorkbook(){[Name="tblDelegations1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="tblDelegations2"]}[Content],

    SortAsc1 = Table.Sort(Source1,{{"Budget Holding Positions", Order.Ascending}, {"Category", Order.Ascending}}),
    SortAsc2 = Table.Sort(Source2,{{"Budget Holding Positions", Order.Ascending}, {"Category", Order.Ascending}}),

    Join = Table.Combine({SortAsc1, SortAsc2}),
    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)),
    TSC = Table.SelectColumns(SortCapt,{"Custom", "Chief Officer", "Chief Steward", "Engineer"}),
    Ren = Table.RenameColumns(TSC,{{"Custom", "Captain"}}),

    SortChOff = Table.AddColumn(Pivot, "Custom", each List.Sort([Chief Officer], Order.Ascending)),
    #"Removed Other Columns" = Table.SelectColumns(SortChOff,{"Captain", "Chief Steward", "Engineer", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Custom", "Chief Officer"}}),
    SortChStw = Table.AddColumn(Pivot, "Custom", each List.Sort([Chief Steward], Order.Ascending)),
    #"Removed Other Columns1" = Table.SelectColumns(SortChStw,{"Captain", "Chief Officer", "Engineer", "Custom"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Other Columns1",{{"Custom", "Chief Steward"}}),
    SortEng = Table.AddColumn(Pivot, "Custom", each List.Sort([Engineer], Order.Ascending)),
    #"Removed Other Columns2" = Table.SelectColumns(SortEng,{"Captain", "Chief Officer", "Chief Steward", "Custom"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Other Columns2",{{"Custom", "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
in case you sorted lists so first two sort steps are not necessary
I made your M a bit shorter :biggrin:
see your code, you sorting all four columns from Pivot step - wrong!
In most cases PQ needs step from previous step

Rich (BB code): 
let
    Source1 = Excel.CurrentWorkbook(){[Name="tblDelegations1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="tblDelegations2"]}[Content],

    Join = Table.Combine({Source1, Source2}),
    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
Sandy,

Thank you - works perfectly - really don't know why I was sorting from the PIVOT, I know that PQ usually works from Previous Step. Next time I won't try doing things I'm unsure of at midnight!!

Thanks again, your help is very much appreciated.

Regards
 
Upvote 0
Hi Sandy,

Working through the code to understand how it works. At this line Pivot = Table.Pivot(Distinct, PivotColNames, ColToPivot, ColForValues, each _), when referring to the M Code Reference, I find that the 'each _' is an aggregationfunction, but I can't find any explanation of how that is defined or can be used. Where did you find out about it? I'd be grateful for any links or book references.

Many thanks.
 
Upvote 0
Thanks for the Sandy, I'd thought I'd found the reference, but thats better.

Much appreciated - and plenty to learn!
 
Upvote 0
I'm glad it turned out to be useful
As usual I like Likes (y) :biggrin:

hint: hit like in the post(s) which were helpful
 
Last edited:
Upvote 0
And you can un-like posts which were not important as solution if you wish ;)
 
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
978
Dimitris254
D

Forum statistics

Threads
1,215,767
Messages
6,126,774
Members
449,336
Latest member
p17tootie

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