Different amount of decimals is messing up the result

[Anonymous]

Hi everybody,

I have a raw data which contains a column of percentages. For example (in this format),

99,1%
97,10%
91,5% 

The issue is that when I load this into Power BI and look in the data tab the data is converted into:

9.91
97.10
9.15

In other words, if there is only one decimal then Power BI will make the number smaller. I am in Europe (,) but my standard of Power BI is in US (.) and the raw data is in commas (,). 

Any idea what I can do to fix this, apart from adding a second decimal to the raw data? (big file so don't know a smart way to do it in mass).

Thanks in advance! 

[watkinnc · Accepted solution]

You could make the column type text, and the use Replace Values to replace "," with ".".

Then you can transform back to type number and then to percentages.

--Nate

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

First you turn your field to text type.

Then right click on the field and select Using local.

Change type as follows, data type is percentage, and locale is german.

You can check more details from the attachment.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you, worked perfectly!

[watkinnc · Accepted solution]

You could make the column type text, and the use Replace Values to replace "," with ".".

Then you can transform back to type number and then to percentages.

--Nate

[HotChilli]

I think there's a mismatch in Power Query and PowerBi with the regional settings of your PC . The mismatch will be around decimal symbol and digit grouping symbol.  You might have to handle this yourself in Power Query.

It would be clearer if you can tell us what datatype the original has in Power Query.  Is there any type change in power query? And also what datatype the field has in powerbi?

---- 

If you have to handle this yourself (without changing regional settings etc) , 

you might try right-clicking the column in Power Query and pick 'Change Type ->using Locale' . That might take a little experimentation to get what you want.

[Syndicate_Admin]

@p4dd4 

 I believe your data look like this at the begining 

Change it to Demical Number

Choose Divide from Formatting under Transform Tab

Then change it to % Data Type

Result

[Fowmy]

@Anonymous 

 I believe your data look like this at the begining 

Change it to Demical Number

Choose Divide from Formatting under Transform Tab

Then change it to % Data Type

Result

[Anonymous]

Hi @Fowmy  thanks for your answer! I was perhaps a bit unclear. I want the result to be 99.1%, 97.1% and 91.5%. Is it something I have to fix in the raw data then? 

[Fowmy]

@Anonymous 

You need to do it in Power Query, If you can get done from the source it will be much better. 

To get the desired results, instead of dividing by 100, divide by 10

[Anonymous]

Hi again, but if I dive it by 10, won't the result then be  91.1%, 971.0%, and 91.5%?