Convert Percentage Type Column to Text Type and Preserve Percentage Format

[Linnil]

Hi Everyone

I have various percentages in a list ie 20.0%, 14.5%, 36.7% etc.

One decimal place.

I want to change the Data Type from Percentage to Text, so I still see  20.0%, 14.5%, 36.7%

Because if the values are text, I can "Replace Values" - for instance, I want to replace the text 20.0% with the WORD "NA".

If you try and change the Data Type in Power Query from Percentage to Text, it just gives you decimal values 😞

I'd like to do this in Power Query if possible.

Any ideas? I think some "Text.From" and I have to do calcs multiple by 100, then join & % sign...

But that seems very unelegant. 

Thanks for your thoughts.

[rohit1991]

Hi Linnil 

I've updated my previous response to simplify and fully address your question:

The easiest way to convert a percentage column to text while keeping the format (like "20.0%") intact is using this straightforward step in Power Query:

= Table.TransformColumns(#"Changed Type", {"YourColumn", each Number.ToText(_, "0.0%"), type text})

This directly converts your percentages into properly formatted text, preserving the percentage sign and decimal precision. It also works smoothly with "Replace Values" or similar text-based operations.

[Linnil]

Thank you for responding - this was a partial solution, I thought of this myself and added another step to get my end solution.

[v-tsaipranay · Accepted solution]

Hi @Linnil ,

Thank you for reaching out to the Microsoft Fabric Community.

you're correct that directly converting a Percentage column to Text in Power Query results in decimal values like 0.2 instead of retaining the "20.0%" format.

The solution shared by @Vijay_A_Verma  is correct as well. You can use the following transformation to convert the percentage to text while preserving the formatting:

= Table.TransformColumns(#"Changed Type", {"Data", each Number.ToText(_, "0.0%"), type text})

This ensures values like 0.2 are displayed as "20.0%" in text format, making them usable with "Replace Values" and other text-based operations.

Alternatively, if you’d like to keep the original column and add a new formatted one, you can use:

= Table.AddColumn(#"Changed Type", "FormattedText", each Number.ToText([Data], "0.0%"), type text)

I hope this will resolve your issue, if you need any further assistance, feel free to reach out.

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

Thankyou.

[Linnil]

Thanks - this was perfect (I changed it to 0.00% which is a minor thing).

I've used Text.From Number a lot.

I have to remind myself of Number.ToText ! 

Which is the other way of thinking of the issue!

Many thanks

[v-tsaipranay]

Hi @Linnil ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Linnil]

I did mark a Solution - I understand how the system works.

[Vijay_A_Verma]

Use this where Data is column name

Number.ToText([Data], "0.0%")

Hence, to transform the column type where #"Changed Type" is previous step

= Table.TransformColumns(#"Changed Type", {"Data", each Number.ToText(_, "0.0%")})

[Linnil]

Thanks for your solution.
I went with the Add Column solution but I appreciate your solution