Why does Power Bi change the % decimal places?

[Anonymous]

Our ERP system brings in this column like the 1st screen shot.   Gross margin = 57.29 but notice it brings it in as a text field.   If I convert that column to a percentage type, it makes the margin 5729.16%. 

Intuitively I understand why, because it is multiplying it by 100 once the column is converted to a percentage.  

Aside from changing the ERP field, which our sys admin won't do, is there a better way to handle this?   Perhaps create a custom column and divide the original column by 100, then change that column to a percentage type.  

Any ideas?   We have about 5 columns that all have this same issue so looking for an efficient solution.  

      

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

First go to query editor and change the column type to number,then using below M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjXXM7I0NFOKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1]>0 and [Column1]<100 
then Number.ToText([Column1])&"%"
else[Column1])
in
    #"Added Custom"

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[ryan_mayu]

@Anonymous 

I think if you can't add % in the ERP field, then you need to create a custom column to get the correct value.