Brand new... why do numbers sort as alphas?

[oandroido]

Hi all,

Just downloaded Power BI to experiment with visualization, and watched some tutorials, but none really covered what I'm trying to do - 

Which is, basically, sort a column full of numbers numerically.

Sounds dumb, but when attempting to sort numerically, the top number is "8" followed by "79"....   ?? 🙂

Thanks in advance, I'm sure this is the first of many... 

[Anonymous]

Each field is saved as a data type.  In your case I would suspect that the column you have you concerned with is being stored as a data type of "Text".  If that field is all numbers, change the data type to either Decimal or Whole Numbers, whichever is most applicable.

You can do this either in the "Edit Queries" section but change the type in the icon next to the column header name, or you can do this inside the Modelling Tab outside of Edit Queries.

[oandroido]

Thanks - I'm getting an error saying 

"We can't automatically convert the column to Whole Number type" (or whichever I try).

That said, there are a bunch of "NULL" values - could that stop it from working?

[Anonymous]

I'm sure Ross will have the complete answer for you; he's great.

First, are you sure they are whole numbers? Also, blanks may not be "blank" to the format selection.

I suggest creating a column and fill it from the other column using FORMAT to convert to a number and use code to replace BLANK with 0. If that gives you an error then there is something funky with the text format that I've not run into. 

Cheers!

[Anonymous · Accepted solution]

@Anonymous answer is pretty much it.  I simply start with a few basic cleansing steps, such as using Transform -> Trim, if I get the error you have mentioned, then do a replace values for null to 0.

Its also worth checking to make sure that this column doesn't contain any non-numeric values.  Depending on the source will depend on the data reliability.  For example, if you are getting the data from a program that only accepts numbers, then you will have more certainty that the data will be clean.  Conversely, if the data comes from excel where users type in information, a user could easy have entered "Eight" rather than 8.