cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
WALEED Regular Visitor
Regular Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
WALEED Regular Visitor
Regular Visitor

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.

View solution in original post

8 REPLIES 8
vanessafvg Super Contributor
Super Contributor

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!
WALEED Regular Visitor
Regular Visitor

Re: Power Query/Power BI Problem with Decimals

@vanessafvg

 

Capture.PNGLooks in order in Excel

 

zoloturu
Advisor

Re: Power Query/Power BI Problem with Decimals

@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.PNGExcel data

WALEED Regular Visitor
Regular Visitor

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

WALEED Regular Visitor
Regular Visitor

Re: Power Query/Power BI Problem with Decimals

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

vanessafvg Super Contributor
Super Contributor

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!
WALEED Regular Visitor
Regular Visitor

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.

View solution in original post

TobiasOwen Member
Member

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.

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 6 members 3,145 guests
Please welcome our newest community members: