Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
WALEED
Advocate I
Advocate I

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 17
Terraque
New Member

"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".

DonPepe
Helper II
Helper II

Hello,

 

Still no answer ? Did you handle the issue ? 

Regards,

 

Don

MarianaSoares
Regular Visitor

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.

tarman
Regular Visitor

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...

Anonymous
Not applicable

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.

Appreciate it, @WALEED!

Hopefully this will reopen the issue and someone will notice

 

Best,

 

LM

vanessafvg
Super User
Super User

@WALEED must be getting from somewhere though, its probably rounding in excel





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




@vanessafvg

 

Looks in order in ExcelLooks in order in Excel

 

@WALEED,

 

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 dataExcel 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.

@vanessafvg @zoloturu

 

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?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors