MS Excel / VBA help thread

You are reading a single comment by @muthesius and its replies. Click here to read the full conversation.

•

muthesius

Powerquery - I have a list of items within categories, which are sorted within the categories by a category specific logic (e.g. items in Category A might sort by start date ascending, Category B by start date descending, etc.). That bit is working fine and I end up with a table like:

Category Item Rank
A Item X 1
A Item Y 2
A Item Z 3
B Item C 1
B Item V 2
B Item B 3

I've been asked to group certain items together by exception based on purely subjective criteria, something like in this case Item Z should appear after Item X rather than item Y. At present there is only one exception but potentially there would be more than one (but never a large number)

What is the best way of achieving this?

•

aggi in reply to @muthesius
It seems a bit of a strange requirement but I'd guess the best way would be to create a list of your custom sort order and use that as the criteria with Table.Sort e.g.
```
 Table.Sort( 
  Source,
  { each List.PositionOf( {"Item X", "Item Z", "Item Y"}, [Item] ), 
    { "Category",Order.Ascending } } 
)
```
(you can reference a list instead of doing it manually).

Or you could have another table with your sort order and an index. Merge the tables and sort by the index.

About

@muthesius started