Convert "Percentage" Data type to "Text" Data Type, but keep full format

[StuartSmith]

I have a column in my data set of "Percentage" type and displays values as "75.00%", etc. but I need to convert this column to a "Text" data type, but retain the format of "75.00%", etc. but when I use the "Data Type" option and change to "Text" the formatting is lost and "70.00%" is displayed like "0.7".  

How can I convert to text and keep existing format or create a new column that will convert to text, but keep the original format?

[PhilipTreacy]

Hi @StuartSmith 

Isn't this the same question you asked here

Re: Mixed Text and Percentage column. - Microsoft Power BI Community

Please check my answer there that solves the issue using Custom Format Strings

Regards

Phil

[Tahreem24]

@StuartSmith ,

Create the Calculated Column with the help of FORMAT:

% Text = FORMAT(B[Percentage],"Percent")

[Anonymous]

Can we do this at transformation by replacing the origial column, instead of creating a new column? As I already have a lot of columns in my table.

[Tahreem24]

@Anonymous you can do that in Power Query.

[StuartSmith]

Thanks, I had been trying that, but didnt have much success, but that may have been because I was also then trying to replace some values in the column with text strings such as if the value in a cell was "-1.00%", replace with "Not Disclosed".  How can I improve you code so that it would replace values of "-1.00%" with "Not Discolsed" and "-2.00%" with "N/A"? 

Thanks in advance

[Tahreem24]

You can futher enhance your code by creating another column like below:

Column 2 = IF(Table[% Text] ="-1.00%", "Not Disclosed", IF(Table[% Text] = "-2.00%", "NA", Table [% Text])) 

[StuartSmith]

Thanks Tahreem, that worked.  I guess there is no way to get this working with 1 calculated column and not the 2 calculated columns.

[PhilipTreacy]

@StuartSmith 

You don't need to ceate another column.  Just make the column Decimal and apply Custom Formatting to the column as I described in your other question 🙂

Re: Mixed Text and Percentage column. - Microsoft Power BI Community

Regards

Phil

[v-xulin-mstf]

Hi @StuartSmith,

I'm not clear abbout 'working with 1 calculated column and not the 2 calculated columns', could you provide expected output?

Best Regards,

Link

[amitchandak]

@StuartSmith , Try the formatting using the format function

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-custom-format-strings

https://radacad.com/custom-format-fields-and-measures-in-power-bi