Reformat Field to Percent

[Anonymous]

hi

is it possible to reformat a field to be a percent, even if the value is alreday an integer number?

for example, if the data under 'percent' is 98, 97, 22, etc, and these values are actually the percentage values, how can i change this information to indicate percent, as changing the format to percent multiplies all of them by 100 and then turns into percent, i.e. 9800%, 9700%, 2200%. One option would be to make a new column and divide these values by 100 prior to formatting to percent but is there an easier way to do this?

br,

dave

[v-jiascu-msft]

@Anonymous,

Hi Dave,

I'm a little confused. Why not change the format directly?

Best Regards!

Dale

[Anonymous]

v-jiascu-msft

Don't think you fully understand the question, changing to the percent format is simple but unfortunately the raw data is already showing as a percent, this was explained in the original post in better detail. If I just change the column to format percent i get 9800%, 5000%, even when I need it to read 98% and 50%

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

I get the whole idea now. If you don't want to add a new column, you have to divide the values by 100 first. 

1. Open Query Editor, apply "Replace values" once. Whatever the values are. We only need this step to generate some codes for us.

2. Open "Advanced Editor". Replace the blue part with this:

each [Column1], each [Column1] / 100

3. Change the Format to "Percentage".

Best Regards!

Dale

[Anonymous]

Thats brilliant Dale, thank you

[Anonymous]

Hi Dale,

When you say original query do you mean the query editor?

[mow700]

I would create a new column and probably use the FORMAT() function in conjunction with the calculation.

New Column = FORMAT([Original Column]/100,"Percent")

You could always transform this column in the original query as well.  If you never need this data as an int that's likely the best way to handle this.