cancel
Showing results for
Did you mean:

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

11 REPLIES 11
Super User I

## Re: Power Query/Power BI Problem with Decimals

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

Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Highlighted

## Re: Power Query/Power BI Problem with Decimals

@vanessafvg

Looks in order in Excel

Memorable Member

## Re: Power Query/Power BI Problem with Decimals

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],
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"data", type number}})
in
#"Changed Type"```

Excel data

## Re: Power Query/Power BI Problem with Decimals

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})),
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.

## Re: Power Query/Power BI Problem with Decimals

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.

Super User I

## Re: Power Query/Power BI Problem with Decimals

@WALEED sorry been away, did you get sorted?

Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!

## Re: Power Query/Power BI Problem with Decimals

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.

## Re: Power Query/Power BI Problem with Decimals

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.

Regular Visitor

## Re: Power Query/Power BI Problem with Decimals

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.

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors