Forum Discussion
How to sort column with mixed data type
I have a column containing positive and negative numbers as well as text values. It is a result of calulation, where sometime data are missing or the calculation makes no sense.
e.g.:
10
-1
No Data
150
-20
-2
350
not applicable
No Data
.. etc.
Problem is, that when I try sorting this (descending or ascending), it mixes negative and positive numbers.
From example above it sorts it like this:
-1
10
150
-2
-20
350
No Data
No Data
not applicable
I understand it considers the numbers as text (but I do not understand why it ignores negative sign).
What I need is, that it sorts the numbers as numbers and text values as text.
Any idea how to achieve this?
You cannot have text and values in the same column in Power BI unless you leave the data as "any" or untyped, and that still gets all converted to the DAX model as text, so it will sort as text.
What you could do is sort it as desired in Power Query, then add an index column, then in Power BI, Sort your main column by the index column.In the above image, I sorted the data which was the ANY type (ABC/123) then added an index column, then converted the original column explicitly to text. It is bad practice to leave any untyped columns in Power Query tables you load to the model.
See this M code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTiWBS10QpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each try Number.From([Column1]) otherwise [Column1]), #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Custom", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Custom", type text}}), #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Custom", "Index"}) in #"Removed Other Columns"So
would sort like this:
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.For more on the Sort By Column, see Sort by column in Power BI Desktop - Power BI | Microsoft Docs
5 Replies
You have mixed data, so Power Query is sorting accordingly. See how the 10 in my data is below 350?
You need to have everything as one data type per column. So change to all to text as anything else will throw an error. "not applicable" converted to a number will be an error for example.
If you want it all as numerical, then you have to use the Transform ribbon, Replace Values feature. Search for "not applicable" and replace with null - exactly that - null - all lower case. That will not replace with the text null, but the value of null.
Then change the column data type to numerical - fixed decimal or whole number probably - then sort.try this:
let Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQitWJVtI1BFN++QouiSWJYLahKVTKCEaDKWOocF5+iUJiQUFOZnJiUk4qqu5YAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna1 = _t]), #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Colonna1", type text}}), Colonna1 = List.Sort(#"Modificato tipo"[Colonna1],{(x)=>try Number.From(x) otherwise x, Order.Ascending} ) in Colonna1se ti serve qualcosa di più specifico nell'ordinamento, dovresti spiegare meglio il risultato atteso e, forse, si può definire un comparisoncriteria adatto alle esigenze.
I put here some more information to my question.
I want to achieve this sorting result from my example above:
-20
-2
-1
10
150
350
No Data
No Data
not applicable
I need to achieve this sorting in Visualisation (Table), not in Power Query Editor.
When I try the sorting in Power Query Editor, it works exactly how I need it (negative numbers, then positive numbers, then text values). But then in Visualisation Table it does not work. (I assume it converts all values to text type.)
I need to keep several text strings ("No Data" and "not applicable"), so replacing all those text strings with null value does not help.
You cannot have text and values in the same column in Power BI unless you leave the data as "any" or untyped, and that still gets all converted to the DAX model as text, so it will sort as text.
What you could do is sort it as desired in Power Query, then add an index column, then in Power BI, Sort your main column by the index column.In the above image, I sorted the data which was the ANY type (ABC/123) then added an index column, then converted the original column explicitly to text. It is bad practice to leave any untyped columns in Power Query tables you load to the model.
See this M code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTiWBS10QpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each try Number.From([Column1]) otherwise [Column1]), #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Custom", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Custom", type text}}), #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Custom", "Index"}) in #"Removed Other Columns"So
would sort like this:
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.For more on the Sort By Column, see Sort by column in Power BI Desktop - Power BI | Microsoft Docs