Forum Discussion
Solved
Text should be text and numeric values should be converted to percentage
Hi everyone, One Column (Data type text) contains text and numeric values. Text should be text and numeric values should be converted to percentage in Power BI. how i will get this. This is ...
srinudatanalyst Here's an approach using custom columns in the query editor (M), and DAX calculated columns. One multiplies the original number by 100 and one doesn't. A sample .pbix is attached.
Hi, You can try at Power Query lik this:
Custom column in Power query:
= Table.AddColumn(#"Changed Type", "Custom column in Power Query", each if Text.Select([Q1], {"0".."9", ".", "-"}) = [Q1] then Number.ToText(Number.From([Q1]) * 100) & "%" else [Q1])
Or with a calculated column like this:
Q1_Percent =
VAR Val = SUBSTITUTE([Q1], "%", "")
VAR NumVal =
IF (
ISERROR ( VALUE ( Val ) ),
BLANK(),
VALUE ( Val )
)
RETURN
IF (
NOT ISBLANK ( NumVal ),
FORMAT ( NumVal , "0.0%" ),
[Q1]
)
- bchager
Resolver III
data_msfabric Your DAX isn't quite right. It's converting 100% to 10000%.
