Formatting Number

[Anonymous]

Hi All,

This is my first post to Power BI, I am looking for formattng numbers

I have a column called metrics which has mixed type of values 

Metrics	Value
Orders	2.00
Revenue	10.76
New Customers	3.00

Now I want to format these numbers in such a way that it shoud be as below and also keep Value as NUmber and not Text.

I have tried creating with Format but ut changes the value to text.

Metrics	Value
Orders	2
Revenue	10.76
New Customers	3

[Anonymous · Accepted solution]

HI @Anonymous,

If you setting 'date type' option to decimal number, format option to general; decimal places to auto, power bi will auto hide the decimal part of whole number.

Regards,

Xiaoxin Sheng

[Sweet-T]

Hey Xiaoxin, 

Can you help me keep trailing zeros on the end of my decimal numbers, but only if the zeros exist? (in other words, don't add trailing zeros if they don't exist). I want to keep my column datatype as a number, not text. 

Take this subset of data {44.1, 44.10, 44.100}. I am using these values to rank data, so differentiating between the numbers after the decimal (1, 10, 100) is important. 

If the datatype is set to "Decimal Number", then Power BI removes the trailing zeros after the decimal, by default.
E.g. 44.10  --> 44.1
Eg. 44.100 --> 44.1

If I then set the number of desired decimal places, for example to "3", then PBI adds extra zeros where I don't want them. 

E.g. 44.1   --> 44.100
E.g. 44.10 --> 44.100

What I'd like: 

44.1

44.10

44.100

Let me know!

Thanks, T

[alena2k]

hi! you can get this only in Text format. Check your power query code, it is trying to change format to decimal for you like this:
= Table.TransformColumnTypes(Source,{{"Column1", type number}})

You need to replace it with number with text:

= Table.TransformColumnTypes(Source,{{"Column1", type text}})

[alena2k]

Please look under Modeling in Desktop as shown above or you can set type on column in Query Editor.