Help sorting numbers that also have A, B, C attached

[Anonymous]

I have a table that lists numbers, but some numbers can have A, B, C values. For example:

1

2A

2B

3

4A

4B

5

6

7

8A

8B

8C

9

10

I want them to sort as above, but it's a text field because it has letters mixed in. Is there any workaround? This is how it sorts as text:

1

10

2A

2B

3

4A

4B

5

6

7

8A

8B

8C

9

[vanessafvg · Accepted solution]

@Anonymous  best thing to do is add an index to it and then sort by that index.  import the data in in the order you want it in, add an index column in power query, and then in the visual pane under modelling, use the sort by column and sort by that index, this will allow you to sort by the index column which will be numerical.

hope that makes sense.

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

You could add an Index and then use Sort by column function in Power BI Desktop

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

For add index step, you could refer to this simple way:

 duplicate the column and split the duplicate column in power query, then change the numerical part to numeric.

now sort by it. 

here is M code for you refer to:

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMnKEUE5gyhhMmkDETCBipmDSDEyag0kLiLwFRN7CGUxZgklDA6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),

    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Column1 - Copy"),

    #"Removed Columns" = Table.RemoveColumns(#"Duplicated Column",{"Column1 - Copy"}),

    #"Duplicated Column1" = Table.DuplicateColumn(#"Removed Columns", "Column1", "Column1 - Copy"),

    #"Split Column by Character Transition" = Table.SplitColumn(#"Duplicated Column1", "Column1 - Copy", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Column1 - Copy.1", "Column1 - Copy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Character Transition",{{"Column1 - Copy.1", Int64.Type}})

in

    #"Changed Type1"

Best Regards,

Lin

[vanessafvg · Accepted solution]

@Anonymous  best thing to do is add an index to it and then sort by that index.  import the data in in the order you want it in, add an index column in power query, and then in the visual pane under modelling, use the sort by column and sort by that index, this will allow you to sort by the index column which will be numerical.

hope that makes sense.