Solved: Issue with automatic rounding during import of xls

PGuenther · ‎09-03-2021

Hello,

I have an issue with my imported data. Whenever I import it from an xls datafile into Power BI the values in some columns are automatically rounded during the import process.

Ex. In the xls column there are values like 4,24 or 2,45 which after import into Power BI show up as "4" and "2" in the colum structure of Power BI. See screenshot below where the Diesel column has this issue and the Gasoline doesn't. I already tried switching the data types to "decimal" and others. But as the original cell values (4,24) has been overwritten with "4" during import it doens't help. Any hints? Thank you!

Anonymous · ‎09-06-2021

Hi @PGuenther ,

Please try changing the region settings in Power BI Desktop options and settings and change the locale for import to Germany first.

Then reload the data from the Excel file into Power BI Desktop.

I have used this to reproduce and solve the problem, hope this helps.

Community Support Team _ Caitlyn Yan

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

View solution in original post

lbendlin · ‎09-06-2021

Seems like a culture mismatch between de-de and en-us. Add the culture hint to the source step.

PGuenther · ‎09-06-2021

Thx, what do you mean by source step?

Anonymous · ‎09-05-2021

Hi @PGuenther ,

In a case like this, We have to check 3 things

Column Format in Excel should be "Number with decimal"
Power BI "Query Editor" make sure the Data type is "Decimal Number"
In Power BI in "Modeling" tab, make sure the Data type is "Whole Numer" and Format is "Decimal Number"

If it does not work, Here's a case for a similar situation that you can try in the Query editor based on its solution.

Hope it helps,

Community Support Team _ Caitlyn Yan

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

PGuenther · ‎09-06-2021

Hello, the formatting in the Excel file is decimal with 2 digits.

Anonymous · ‎09-06-2021

Hi @PGuenther ,

Have you tried the method in the link above?
I can't reproduce your problem.If it still doesn't work , can you provide me with the excel file？ (make sure there is no sensitive information in it.)

Hope it helps,

Community Support Team _ Caitlyn Yan

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

PGuenther · ‎09-06-2021

Index	Emission - Gasoline	Emission - Diesel	Emissions - Scope 1	Emission - Electricity	Emissions - Scope 2	Emissions - TOTAL
1	0,97	3,56	4,53	0,36	0,36	4,89
2	0,49	4,34	4,84	0,29	0,29	5,13
3	0,00	1,61	6,48	0,44	0,44	6,92
4	0,00	1,61	1,61	1,23	1,23	2,84
5	2,35	0,69	3,41	0,36	0,36	3,77
6	1,17	2,92	4,10	1,39	1,39	5,48
7	1,21	11,87	13,11	1,60	1,60	14,71
8	0,59	6,92	10,00	1,03	1,03	11,02
9	0,41	3,13	5,13	1,73	1,73	6,85

Emissions Gasoline work fine (no rounding) Emissions Diesl and others show rounding issue.

Anonymous · ‎09-06-2021

Hi @PGuenther ,

In the sample you provided, we found that the numbers are separated using commas and it should be separated using decimal points.
We recommend you to check the punctuation in the Excel file, replace them with decimals and re-import them to Power BI.

In addition, the same situation occurs in your other columns, (other columns containing "," must be in text format) In order to ensure the accuracy of your subsequent calculations, we recommend that you modify them at the same time.

Hope it helps,

Community Support Team _ Caitlyn Yan

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

PGuenther · ‎09-06-2021

Thank you, I am using an international (DE) version where comma and decimal points are inverted compared to US-version. Would you still recommend to switch? This would mean to rearrange my whole xls sheet to US-format.