Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Why does Power Bi change the % decimal places?

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.  

 

      

 

 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

2 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    5 years ago

    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.

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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!