Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BigKev
Helper III
Helper III

Maintaining trailling zero in decimal point where applicable

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.

BigKev_0-1712148039923.png

 

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

1 ACCEPTED SOLUTION
Wilson_
Memorable Member
Memorable Member

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.

 

Wilson__1-1712195093064.png

 

Wilson__0-1712195066636.png

 

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

 

Wilson__3-1712195221959.png

 

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. 😄

View solution in original post

3 REPLIES 3
Wilson_
Memorable Member
Memorable Member

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.

 

Wilson__1-1712195093064.png

 

Wilson__0-1712195066636.png

 

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

 

Wilson__3-1712195221959.png

 

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
Solution Supplier
Solution Supplier

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

Joe_Barry
Solution Specialist
Solution Specialist

Hi @BigKev 

 

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

 

Joe

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.