- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Brand new... why do numbers sort as alphas?
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...
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
10-20-2024 08:37 PM | |||
07-10-2024 04:10 AM | |||
12-08-2023 07:34 AM | |||
08-23-2023 07:58 AM | |||
10-11-2023 04:26 AM |
User | Count |
---|---|
128 | |
81 | |
59 | |
57 | |
44 |
User | Count |
---|---|
184 | |
111 | |
82 | |
66 | |
52 |