Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
Proud to be a Super User!
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
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
@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.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |