Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Formatting Number

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 ...
  • Anonymous's avatar
    Anonymous
    8 years ago

    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's avatar
Sweet-T
Helper III
4 years ago

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's avatar
alena2k
Resolver IV
4 years ago

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}})