Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
"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".
Hello,
Still no answer ? Did you handle the issue ?
Regards,
Don
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.
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_Certificados
Source 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,6074
Power 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,6074
Hi,
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...
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.
@WALEED must be getting from somewhere though, its probably rounding in excel
Proud to be a Super User!
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
Apologies for the late response; query below:
let Source = Excel.Workbook(File.Contents("B:\BP\Work Exchange\Export\2017.08.10_SC.xlsx"), null, true), SHARECAT = Source{[Item="2017.08.10_SC",Kind="Sheet"]}[Data], ALL_TEXT = Table.TransformColumnTypes(SHARECAT,List.Transform(Table.ColumnNames(SHARECAT),each {_, type text})), PROMOTE_HEADERS = Table.PromoteHeaders(ALL_TEXT, [PromoteAllScalars=true]), TRIM_STATUS = Table.TransformColumns(PROMOTE_HEADERS,{{"Tag Status", Text.Trim, type text}}), REMOVE_COLUMNS = Table.RemoveColumns(TRIM_STATUS,{"ORIGINATING CONTRACTOR","SYSTEM","Service Description","TAG STATUS (OPERATIONAL / VOID)","LOOP ID","PURCHASE ORDER NO"}), TRANSFORM_ATTR_1 = Table.RenameColumns(REMOVE_COLUMNS,Table.ToRows(TRANSFORM_ATTR), MissingField.Ignore), TRANSFORM_ATTR_2 = Table.SelectColumns(TRANSFORM_ATTR_1,TRANSFORM_ATTR_LIST[EB ATTR],MissingField.Ignore), FILTER_TAGS = Table.SelectRows(TRANSFORM_ATTR_2, each ([#"TAG STATUS (OPERATIONAL / VOID)"] = "Operational") and ([TAG CODE] <> "LAD")), DUPLICATE_TAG_NUMBER = Table.DuplicateColumn(FILTER_TAGS, "TAG NUMBER", "TAG NUMBER COPY"), SPLIT_TAG_NUMBER = Table.SplitColumn(DUPLICATE_TAG_NUMBER, "TAG NUMBER COPY", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"TAG NUMBER COPY.1", "TAG NUMBER COPY.2", "TAG NUMBER COPY.3"}), REMOVE_EXTRAS = Table.RemoveColumns(SPLIT_TAG_NUMBER,{"TAG NUMBER COPY.2","TAG NUMBER COPY.3"}), RENAME_PLID = Table.RenameColumns(REMOVE_EXTRAS,{{"TAG NUMBER COPY.1", "PROJECT LOCATION IDENTIFIER"}}), CONCAT_DATASHEET_1 = Table.ReplaceValue(RENAME_PLID," ","_",Replacer.ReplaceText,{"PIPING SPECIFICATION DOC NO"}), CONCAT_DATASHEET_2 = Table.ReplaceValue(CONCAT_DATASHEET_1," ","_",Replacer.ReplaceText,{"PROJECT DATASHEET DOC NO"}), CONCAT_DATASHEET_3 = Table.CombineColumns(Table.TransformColumnTypes(CONCAT_DATASHEET_2, {{"PROJECT DATASHEET DOC NO", type text}, {"PIPING SPECIFICATION DOC NO", type text}}, "en-GB"),{"PROJECT DATASHEET DOC NO", "PIPING SPECIFICATION DOC NO"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"DATA SHEET"), CONCAT_DATASHEET_4 = Table.ReplaceValue(CONCAT_DATASHEET_3,"NA","",Replacer.ReplaceText,{"DATA SHEET"}), CONCAT_DATASHEET_5 = Table.ReplaceValue(CONCAT_DATASHEET_4,"N/A","",Replacer.ReplaceText,{"DATA SHEET"}), CONCAT_DATASHEET_6 = Table.ReplaceValue(CONCAT_DATASHEET_5,"N_/_A","",Replacer.ReplaceText,{"DATA SHEET"}), CONCAT_DATASHEET_7 = Table.TransformColumns(CONCAT_DATASHEET_6,{{"DATA SHEET", Text.Trim, type text}}), CONCAT_DATASHEET_8 = Table.ReplaceValue(CONCAT_DATASHEET_7," ","|",Replacer.ReplaceText,{"DATA SHEET"}), CONCAT_DATASHEET_9 = Table.ReplaceValue(CONCAT_DATASHEET_8,"_"," ",Replacer.ReplaceText,{"DATA SHEET"}), CONCAT_BLOCK_DIAGRAM_1 = Table.ReplaceValue(CONCAT_DATASHEET_9," ","_",Replacer.ReplaceText,{"SOFTWARE FUNCTION BLOCK DIAGRAM"}), CONCAT_BLOCK_DIAGRAM_2 = Table.ReplaceValue(CONCAT_BLOCK_DIAGRAM_1," ","_",Replacer.ReplaceText,{"BLOCK DIAGRAM DOC NO"}), CONCAT_BLOCK_DIAGRAM_3 = Table.CombineColumns(Table.TransformColumnTypes(CONCAT_BLOCK_DIAGRAM_2, {{"BLOCK DIAGRAM DOC NO", type text}, {"SOFTWARE FUNCTION BLOCK DIAGRAM", type text}}, "en-GB"),{"BLOCK DIAGRAM DOC NO", "SOFTWARE FUNCTION BLOCK DIAGRAM"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"BLOCK DIAGRAM"), CONCAT_BLOCK_DIAGRAM_4 = Table.ReplaceValue(CONCAT_BLOCK_DIAGRAM_3,"NA","",Replacer.ReplaceText,{"BLOCK DIAGRAM"}), CONCAT_BLOCK_DIAGRAM_5 = Table.ReplaceValue(CONCAT_BLOCK_DIAGRAM_4,"N/A","",Replacer.ReplaceText,{"BLOCK DIAGRAM"}), CONCAT_BLOCK_DIAGRAM_6 = Table.ReplaceValue(CONCAT_BLOCK_DIAGRAM_5,"N_/_A","",Replacer.ReplaceText,{"BLOCK DIAGRAM"}), CONCAT_BLOCK_DIAGRAM_7 = Table.TransformColumns(CONCAT_BLOCK_DIAGRAM_6,{{"BLOCK DIAGRAM", Text.Trim, type text}}), CONCAT_BLOCK_DIAGRAM_8 = Table.ReplaceValue(CONCAT_BLOCK_DIAGRAM_7," ","|",Replacer.ReplaceText,{"BLOCK DIAGRAM"}), CONCAT_BLOCK_DIAGRAM_9 = Table.ReplaceValue(CONCAT_BLOCK_DIAGRAM_8,"_"," ",Replacer.ReplaceText,{"BLOCK DIAGRAM"}), UNPIVOT_TABLE = Table.UnpivotOtherColumns(CONCAT_BLOCK_DIAGRAM_9, {"TAG NUMBER"}, "Attribute", "Value"), TRANSFORM_VALUES_0 = Table.NestedJoin(UNPIVOT_TABLE,{"Attribute", "Value"},TRANSFORM_VALUES,{"Attr Name", "SC Value"},"TRANSFORM_VALUES",JoinKind.LeftOuter), TRANSFORM_VALUES_1 = Table.ExpandTableColumn(TRANSFORM_VALUES_0, "TRANSFORM_VALUES", {"EB Value"}, {"EB Value"}), TRANSFORM_VALUES_2 = Table.SelectRows(TRANSFORM_VALUES_1,each true), TRANSFORM_VALUES_3 = Table.AddColumn(TRANSFORM_VALUES_2, "Revised Values", each if [EB Value]=null then [Value] else [EB Value]), TRANSFORM_VALUES_4 = Table.TransformColumnTypes(TRANSFORM_VALUES_3,{{"Revised Values", type text}}), TRANSFORM_VALUES_5 = Table.RemoveColumns(TRANSFORM_VALUES_4,{"Value", "EB Value"}), TRANSFORM_VALUES_6 = Table.Distinct(TRANSFORM_VALUES_5), TRANSFORM_UOM_1 = Table.SplitColumn(TRANSFORM_VALUES_6, "Revised Values", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, false), {"Value", "UoM"}), TRANSFORM_UOM_2 = Table.NestedJoin(TRANSFORM_UOM_1,{"Attribute", "UoM"},TRANSFORM_UOM,{"ATTR", "SC UOM"},"TRANSFORM_UOM",JoinKind.LeftOuter), TRANSFORM_UOM_3 = Table.ExpandTableColumn(TRANSFORM_UOM_2, "TRANSFORM_UOM", {"EB UOM"}, {"EB UOM"}), TRANSFORM_UOM_4 = Table.AddColumn(TRANSFORM_UOM_3, "Final Value", each if [EB UOM] <> null then Text.Combine({[Value],[EB UOM]}," ") else if [UoM] <> null then Text.Combine({[Value],[UoM]}," ") else [Value], type text), TRANSFORM_UOM_5 = Table.AddColumn(TRANSFORM_UOM_4, "Trim", each Text.Trim([Final Value]), type text), TRANSFORM_UOM_6 = Table.SelectColumns(TRANSFORM_UOM_5,{"TAG NUMBER", "Attribute", "Trim"}), PIVOT_TABLE = Table.Pivot(TRANSFORM_UOM_6, List.Distinct(TRANSFORM_UOM_6[Attribute]), "Attribute", "Trim"), TRANSFORM_PHYS_1 = Table.NestedJoin(PIVOT_TABLE,{"TAG NUMBER"},TRANSFORM_PHYS,{"TAG NUMBER"},"TRANSFORM_PHYS",JoinKind.LeftOuter), TRANSFORM_PHYS_2 = Table.ExpandTableColumn(TRANSFORM_PHYS_1, "TRANSFORM_PHYS", {"EB PHYSICAL CLASS NAME"}, {"EB PHYSICAL CLASS NAME"}), TRANSFORM_PHYS_3 = Table.AddColumn(TRANSFORM_PHYS_2, "PHYSICAL CLASS NAME MERGE", each if [EB PHYSICAL CLASS NAME]=null then [PHYSICAL CLASS NAME] else [EB PHYSICAL CLASS NAME]), TRANSFORM_PHYS_4 = Table.RemoveColumns(TRANSFORM_PHYS_3,{"EB PHYSICAL CLASS NAME", "PHYSICAL CLASS NAME"}), TRANSFORM_PHYS_5 = Table.RenameColumns(TRANSFORM_PHYS_4,{{"PHYSICAL CLASS NAME MERGE", "PHYSICAL CLASS NAME"}}), TRANSFORM_FUNC_1 = Table.NestedJoin(TRANSFORM_PHYS_5,{"TAG CODE", "FUNCTIONAL CLASS NAME"},TRANSFORM_FUNC,{"TAG CODE", "SC FUNCTIONAL CLASS"},"TRANSFORM_FUNC",JoinKind.LeftOuter), TRANSFORM_FUNC_2 = Table.ExpandTableColumn(TRANSFORM_FUNC_1, "TRANSFORM_FUNC", {"EB FUNCTIONAL CLASS"}, {"EB FUNCTIONAL CLASS NAME"}), TRANSFORM_FUNC_3 = Table.AddColumn(TRANSFORM_FUNC_2, "FUNCTIONAL CLASS NAME MERGE", each if [EB FUNCTIONAL CLASS NAME]=null then [FUNCTIONAL CLASS NAME] else [EB FUNCTIONAL CLASS NAME]), TRANSFORM_FUNC_4 = Table.RemoveColumns(TRANSFORM_FUNC_3,{"EB FUNCTIONAL CLASS NAME", "FUNCTIONAL CLASS NAME"}), TRANSFORM_FUNC_5 = Table.RenameColumns(TRANSFORM_FUNC_4,{{"FUNCTIONAL CLASS NAME MERGE", "FUNCTIONAL CLASS NAME"}}) in TRANSFORM_FUNC_5
I hope the issue will stand out.
Temporary workaround; Save the XLSX as CSV and open in Power Query as Text!, Opening as CSV doesn't make the problem go away. This takes much longer to refresh in Power BI which is odd because it's super fast in Power Query.
I think part of the issue is that opening Excel/CSV files initially sets columns as (ABC/123) then I have to change all columns to (ABC) with whatever damage that's happened getting carried over.
Opening a CSV file as a Text File imports all the columns as text (ABC) from the very beginning.
It should work fine importing decimals as is. It sounds like a bug now.
@WALEED sorry been away, did you get sorted?
Proud to be a Super User!
It's a weird problem.
I've saved the worksheet as a CSV and the problem didn't go away.
I've saved the worksheet as a CSV, imported as a TEXT file and the problem was solved.
Kudos to you for troubleshooting with me.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
126 | |
78 | |
77 | |
60 | |
52 |
User | Count |
---|---|
164 | |
84 | |
68 | |
68 | |
58 |