Maintaining trailling zero in decimal point where applicable

[BigKev]

Hi All,

I have an issue converting data type from text to decimal point while maintaining the leading zero where applicable.

The column on the left is the original data, and the column on the right is after I converted it to the decimal point.

As you can see 

• 5.10 become 5.1
• 7.10 become 7.1
• 7.20 become 7.2

Because of this, I have a duplicate rank, which is supposed to be different. 

How can I keep the trailing zero, where applicable, after converting to a decimal point? And I need the data type as a decimal point.

Thanks

[Wilson_ · Accepted solution]

BigKev,

If you need 5.9 to come before 5.10, then you need to transform your 5.9 to 5.09 (assuming they'll only ever be a maximum of two decimal places) before converting it to a decimal place, then show two decimal places in your column.

From the query editor, go back to in your steps to before you added the column, click Add Column > Column From Examples, then work with that until you're satisfied with the transformation. Then you can convert that new column to a number. At this point, click Close & Apply.

If you want to see two decimal places, you can do that from the column tools once the data is loaded.

Hope that helps!

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI issue? Feel free to hire me on Upwork or to DM me on here to discuss how we can make your problems disappear. 😄

[MNedix]

Have you tried formatting the Custom column as text in PowerQuery?

[Joe_Barry]

Hi @BigKev 

Is it possible to change numbers like 7.1 to 7.01? This will then keep it's numerical sort

Joe