How to add "%" to values

[cjngct1112]

Hi all,

I have a table like this (but > 40 columns)

A	B	C	D	E	F........
AA	AB	0.28	0.68	0.99	0.41
BB	BC	0.83	0.13	0.20	0.53
CC	CD	0.56	0.24	0.09	0.45
DD	DE	0.68	0.35	0.24	0.90
EE	EF	0.03	0.55	0.73	0.39

I would like to add "%" on all values except the first 2 columns like this.

How to transform in Power Query with a efficient query?

A	B	C	D	E	F
AA	AB	0.28%	0.68%	0.99%	0.41%
BB	BC	0.83%	0.13%	0.2%	0.53%
CC	CD	0.56%	0.24%	0.09%	0.45%
DD	DE	0.68%	0.35%	0.24%	0.9%
EE	EF	0.03%	0.55%	0.73%	0.39%

Thanks in advance

[Ahmedx · Accepted solution]

pls see my video
https://1drv.ms/v/s!AiUZ0Ws7G26RiBgWRs6CITvMH5Hh?e=I1N5iV
Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26RiBeh65zx6Lo9gcL6?e=KLxt9B

= Table.TransformColumns(from,
         List.Transform(
              List.Skip( Table.ColumnNames(from),2),(i)=> {i,(x)=> 
         Text.Insert(Text.From(x),Text.Length(Text.From(x))
," %"),
       type text}))

[cjngct1112]

@Ahmedx , the script is working on my case and really brilliant

Thanks so much!

[shmmini]

Hi @cjngct1112 

you can add the format function to your measure to format it like this 

Measure = FORMAT(Your Calculation or a reference to a measure , "#\%"). Pay attention to the \. If you forget it, the value will be transformed to a %, which seems not what you want

If your formatting requirement is more complex, you can use Calculation Groups for Dynamic format string

another alternative could be simply dividing your results by 100 and converting the measure to a percentage in the measure formatting ribbon

I hope this helps

[cjngct1112]

@danextian , after I change the datatype to "percetage", it will auto devide by 100. not achieve the desired result

[danextian]

Hi @cjngct1112 ,

You can change the data type in Power Query but they will still be loaded as decimal numbers in the designer. In the designer, you have to select each column and change the formatting to percentage.

[danextian]

So you want to just add % after each number and for 40 columns?