Custom sorting ASC order

[Anonymous]

Hi all,

I need help with ordering the columns in ASC order correctly. Here is my data:

Day	Code	Number
1	300-500-10C-1	1
2	300-500-10C-10	10
3	300-500-10C-2	2
4	300-500-10C-6	3
5	103-300-23C-20	20
6	103-300-23C-21	21
6	180P-100-1C-PR	PR
7	135-100-1A-10	10
8	135-100-1A-12	12
9	135-100-1A-12r	12r
10	180-120-2B-8	8
11	180-120-2B-9	9
12	20S-200-9C-1	1
13	20S-200-9C-10	10
14	20S-200-9C-2	2
15	20S-200-9C-2	2
16	20S-200-9C-3	3
17	20S-200-9C-3	3
17	19E-260-9B-PR	PR
18	135-101-1A-10	10
19	135-101-1A-10r	10r
20	130-200-6Y	6Y

The columns are Day, Code and Number (made by the last symbols from Code). I need to order the Code column in ASC order, but with the following exceptions:
• The “PR” symbols at the end of Code (or in the Number column) should always populate on the top
• All codes in Code column should be ordered in ASC order and the numbers at the end should be ordered 1,2,3…not 1,10,2…
• All codes are separated in some sort of groups (300-500-10C-1, 300-500-10-2, 135-100-1A-10) and I need the numbers to be ordered ASC for every new group
• All codes ending with number&“r” should be shown after the same code without “r”

This is the desired result:

180P-100-1C-PR	PR
19E-260-9B-PR	PR
300-500-10C-1	1
300-500-10C-2	2
300-500-10C-6	6
300-500-10C-10	10
103-300-23C-20	20
103-300-23C-21	21
135-100-1A-10	10
135-100-1A-12	12
135-100-1A-12r	12r
180-120-2B-8	8
180-120-2B-9	9
20S-200-9C-1	1
20S-200-9C-2	2
20S-200-9C-2	2
20S-200-9C-3	3
20S-200-9C-3	3
20S-200-9C-10	10
135-101-1A-10	10
135-101-1A-10r	10r
130-200-6Y	6Y

*In the future there will be more different codes&numbers. The logic for PR, r, ASC order will remain the same.

I tried to add a new column with the following calculation:
Sort_Order =
IF (
NOT ( ISERROR ( 'Table'[Column] + 0 ) )
= TRUE ();
CONCATENATE ( REPT ( 0; 4 - LEN ( 'Table'[Column] ) ); 'Table'[Column] );
'Table'[Column]
)

But if I order the table by this column, only the Number column is sorted correctly, I cannot order the table by Code. And I need to be ordered by Code and then the Numbers to be correctly placed next to the code.

Do you have any suggestions if this is possible?

Thank you.

[v-xiaotang]

Hi @Anonymous 

Have you solved your problem? If yes, kindly accept the answer helpful as the solution(OR kindly share your solution). so the others can find it more quickly.😁

Or

if problem still persists, please let me know. Looking forward to receiving your reply.

Best Regards,

Community Support Team _Tang

[v-xiaotang · Accepted solution]

Hi @Anonymous 

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.