Forum Discussion
Power Query/Power BI Problem with Decimals
It's very odd. I've:
1. Imported an xlsx file into Power Query
2. Did a bunch of transformations
3. Applied changes to carry over to Power BI
Some decimals get messed up!
An Example:
2.01 becomes 2.00999999999999
2.02 remains as 2.02
2.07 becomes 2.069999999999999
2.08 remains as 2.08
What? Where? How? Why?... What!?
I've changed all columns to TEXT in Power Query as soon as I imported the file.
I've checked the contents of the cells in the import file thoroughly
Rounding is not an option. I have all sorts of decimal places.
17 Replies
This "solution" really isn't a solution. I've got the same problem with my decimal values playing all kinds of stupid, but the source document is owned by a customer and cannot simply be saved as a CSV because it's just one part of a vast spreadsheet that includes macros, etc.
Is there an official "fix" for this? Power BI and Excel are supposed to work seamlessly together, but that doesn't seem to include carrying over decimal numbers from Excel.
Incredibly dissapointed to see there is no reply to this over a year later.
Not even a Microsoft employee to check in and let us know this is a known issue or direct us to any other threads with similar issues?
And work arounds should not be marked as solutions.
Spot on. I've unmarked my workaround as a solution.
Hello,
Still no answer ? Did you handle the issue ?
Regards,Don
WALEED must be getting from somewhere though, its probably rounding in excel
Please provide your M (Power Query) script for import and transform here.
I've tested import of similar data into Power BI and see result below:
let Source = Excel.Workbook(File.Contents("C:\data.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"data", type number}}) in #"Changed Type"Excel data
Same problem querying from sql server, power query is getting decimals from nowhere.
M script:
let Origen = Sql.Database("rps", "rps2019"), dbo_View_Datos_Certificados = Origen{[Schema="dbo",Item="View_Datos_Certificados"]}[Data] in dbo_View_Datos_CertificadosSource data:
2019-11-21 08:00:00.000 3034-0 1595 9562691 9563991 1505 1560 0 0 2 0,261 0,609 0,546 0,017 0,019 0,932 0,275 0,073 NULL NULL 0,022 0,014 0,013 0,005 0,095 0,143 96,96 0,6074Power Query result:
21/11/2019 8:00:00 3034-0 1595 9562691 9563991 1505 1560 0 0 2 0,261000007 0,609000027 0,546000004 0,017000001 0,018999999 0,931999981 0,275000006 0,072999999 null null 0,022 0,014 0,013 0,005 0,094999999 0,143000007 96,95999908 0,6074Hi,
I have the same problem with MS Power Query.
I have an Excel file connected to a MS Access database.
I tried first with MS Query and a 1.8 value in Access become 1,799999952 in Excel (and same in MS Query).
So I tried with Power Query and it's the same result !
to complete my previous message: adding the round function doesn't change anything
to solve the problem, I changed the fields in the Access table to text format...
My problem is a bit different.
I've loaded some data two months ago, and built my report. The next month I added more data in the same column but Power BI didn't recognize this new data as decimal numbers and rounded up. So in the same column I have decimal and integer, but the correct should be decimal for all. Changing the data type doesn't seem to work. I've checked the dataset and it's correct.
"And at that point they had started to learn about floating point numbers. And started to pull their hair out."
Floating Point Numbers - Computerphile - YouTube
Short answer, there is no fix. It's inherent to programming you get these "weird" decimals. You can force it, for example, rounding/precision steps with decimals allocation. But it's not "fixable".
