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

[AdamMetz23]

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

[zhqw]

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]

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

[AdamMetz23]

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 %.

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

[devanshi]

Another way to done this using power query : 

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

[AdamMetz23]

Thank you.

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

It seems the function does not exist:

What do you think? Thank you.

[devanshi]

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

[AdamMetz23 · 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.