Sorting Text and Number in one sentence

[Klik]

Can anyone help me on how can I sort my data accordingly like the expected below table. I really appreciate the help.

Expected: 

Milestone:

M1

M2

M3

M4

M5

M6

M7

M8

M9

M10

M11

M12

M13

M14

M15

M16

M17

M18

M19

M20

M21

M22

M23

M24

M25

M26

M27

M28

M29

M30

M31

M32

M33

M34

M35

M36

Outcome:

Milestone:

M1

M10

M11

M12

M13

M14

M15

M16

M17

M18

M19

M2

M20

M21

M22

M23

M24

M25

M26

M27

M28

M29

M3

M30

M31

M32

M33

M34

M35

M36

M4

M5

M6

M7

M8

M9

[Dangar332]

hi, @Klik  

i use your same data . for me table visual automaticly sort data as you want 

if not work for you then refer my reply in same post

[Dangar332]

hi, @Klik  

add new column 
sort column = LEFT(RIGHT(sort[Milestone: ],LEN(sort[Milestone: ])-1),1)

which look like

add your milestone column  to table visuals which automaticaly sort by as you want 

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

[Klik]

Hello dangar,

Thank you so much, I really appreciate your response to my question.

However, I want to ask you, what can i do after adding the column.. I didnt want the sortcolumn to appear in my table.. but if I untick it will go back to its unsorted numerical order...

[Dangar332]

hi, @Klik 

you want sorted data in your table view?
if yes follow below
(1) right click on your column

(2) click on ascending 

you get your answer

you get your answer

[Klik]

Thank you so much @Dangar332 the problem has been solved! Appreciate you help!!

[ChiragGarg2512]

@Klik this will require an additional numeric column, if the column is present then click on column and in column tools sort by that numeric field. 

If there is no numeric column that can sort the data. Go to Power Query and duplicate the milestone column and then use split group [By digit to non-digit] and change the type of number column from text to number, this can serve as milestone ID and then it can be used as stated above.

Thank You

[Klik]

Hello @ChiragGarg2512,

Thank you so much, I really appreciate your response to my question.

However, I want to ask you, what can i do after adding the column.. I didnt want the sortcolumn to appear in my table.. but if I untick it will go back to its unsorted numerical order...

[ChiragGarg2512 · Accepted solution]

@Klik What has to be done is click on the column(that has to be sorted) in data pane and this will open column tools tab. On that there is an option to sort column by, select the new numeric column. This will sort the column that has to be sorted on the basis of the new column that was create.

P.S. this is done in PowerBI not query after the additional numeric column is created.

Thank You

[Klik]

Thank you soo much @ChiragGarg2512 it worked!!!