cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SClarke501
Frequent Visitor

Converting visible numbers to plain text

Bit of a strange one but I can't figure out how to do this in Power Query.

 

I have a column of decimal numbers, where I need to differentiate between things like 1.1 and 1.10 (1.10 wants to be treated as one point ten). Some of the numbers gathered from the data are also too long, i.e. "2.29999999" where I want it to just be 2.3.

 

I've actually managed to get the column into the format I want by mixing types of strings for 1.10 and decimal numbers for 1.1, rounding 2.2999999... to 2.3 etc.

 

Visually it is in the format I want, but I need to convert them all to strings to be used as columns. When I convert them to strings it converts 2.3 back into 2.29999999 etc.

 

Is there a way to convert this column to text, while keeping it as it visually is?

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @SClarke501 ,

 

You can use Number.RroundUp() to convert 2.29999999 to 2.3.

vstephenmsft_0-1657176149507.png

And the new column can be changed to text type.

vstephenmsft_1-1657176253750.png

 

 

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-stephen-msft
Community Support
Community Support

Hi @SClarke501 ,

 

You can use Number.RroundUp() to convert 2.29999999 to 2.3.

vstephenmsft_0-1657176149507.png

And the new column can be changed to text type.

vstephenmsft_1-1657176253750.png

 

 

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ronrsnfld
Memorable Member
Memorable Member

For example, if you initially set your column to text (before doing any transformations), and your rule was that if the numbers after the decimal are in the set {1..12}, otherwise round to one digit, you could use this step in Power Query:

 

= Table.TransformColumns(#"Changed Type",
        {"Column1", each if List.Contains(List.Transform(List.Numbers(1,12),each Text.From(_)),Text.Split(_,"."){1})
            then _ else Number.ToText(Number.FromText(_),"0.0")})
watkinnc
Super User
Super User

Sounds like some floating point math stuff.  You can use fixed decimal or double precision, I'm not at my desk, but if you do your math at Value.Add/Multiply/etc. you can control the precision--check the docs here:

 

https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
ronrsnfld
Memorable Member
Memorable Member

What formula do you use to round 2.9999999 => 2.3 ???

 

Sorry, I meant 2.2999999

It's not clear to me what exact rules you use to determine whether to Round a number, or leave it as one or more digits.  However, I'd suggest initially setting the values to type text.  Then, using an IF clause to determine the type of rounding required for the digits after the decimal, then using Number.ToText(Number.From(_),"0.0")) (or "0.00") to create the appropriate text string.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors