Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
AdamMetz23
Advocate I
Advocate I

Using DAX to format a text column to percentage where it is applicable

Dear Community!

I hope you are having a great day.


Can I ask you whether you would have a solution for the following situation? I have text column which includes numbers (e.g. 0.953333) as text and text (e.g. Delayed, No Due Date) as text in the same column.

 

I would like to use FORMAT function or anything else to convert the numbers to percentage (95%) but leave the actual text as text (Delayed, No Due Date). Have you faced similar situation before to solve the issue? Thank you for helping out in advance.

 

Wishing you a great day,

Adam

 

AdamMetz23_0-1687509394562.png

 

 

1 ACCEPTED SOLUTION

Thank you Devanshi,

 

nor did it  work but I have duplicated the column, replaced the values (No Due Date / Delayed ) with empty cells and this way I could figure out a workaround. Not the nicest but it works for the purpose. Thank you for your help, I have learnt a lot.

View solution in original post

7 REPLIES 7
zhqw
Regular Visitor

in pbi go to PQ

= Table.AddColumn(source,"newcol",each if(

    Text.Contains([datacol],".")  ) then Text.Format(Text.From(Number.From(_[datacol])*100) & "%",_)

    else [datacol])

 

devanshi
Helper V
Helper V

FormattedColumn = IF( ISNUMBER('Table'[ColumnName]), FORMAT('Table'[ColumnName], "0%"), 'Table'[ColumnName] )

Thank you Devanshi

I have tried this approach before but it does not work unfortunately as ISNUMBER returns FALSE for all the numbers/values as they are recognized as text values just like (Due Date etc.) and as a result FORMAT does not format it into %.

AdamMetz23_0-1687512091000.png

 

AdamMetz23_1-1687512105868.png

 


Would you have any other idea or it is not really possible in Power BI to solve it? Thank you.

Another way to done this using power query : 

 

Formatted = if Text.IsNumeric([OriginalColumn]) then Number.ToText(Number.FromText([OriginalColumn]) * 100, "0%") else [OriginalColumn]

Thank you.

 

Can you confirm Text.IsNumeric exists? Power Query cannot recognize it. I tried Text.IsNumber, nor did it work.

 

AdamMetz23_0-1687515605156.png

 

It seems the function does not exist:

AdamMetz23_1-1687515694118.png

 

What do you think? Thank you.

 

Sorry , its not working sorry for that, Please try this 
if Text.Contains([OriginalColumn], ".") then Text.From(Number.From([OriginalColumn]) * 100) & "%" else [OriginalColumn]

Thank you Devanshi,

 

nor did it  work but I have duplicated the column, replaced the values (No Due Date / Delayed ) with empty cells and this way I could figure out a workaround. Not the nicest but it works for the purpose. Thank you for your help, I have learnt a lot.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.