Decimal imprecision

[jthomson]

Hi,

I'm having an issue which I think is some sort of floating point issue which I need resolving. I am importing an Excel file with a list of invoice numbers on - so this is in no way intended to be a numeric field, it should just be text. Here's a screengrab from Excel:

Quite clearly a text field, quite clearly formatted as text (I've tried changing it to that from general after reading another thread saying this might solve the issue, but it hasn't). I import this to Power BI, and get to this stage in my query:

let
Source = Excel.Workbook(File.Contents("filename"), null, true),
#"step2" = Source{[Item="sheetname",Kind="Sheet"]}[Data],
#"Filtered Rows" = Table.SelectRows(#"step2", each ([Column1] = "P054153"))

where the last step is just filtering it down to that top row from Excel, and I get the following value:

I see other variants where instead of an ending of .001, it shows .0009999999999 or similar things. I kind of know why it's doing this, and it's because floating point arithmetic is hilarious, but what I'm failing to understand is why, when the data is clearly text in Excel, and has not had any data type changes at this stage of the query, Power Query is interpreting this field as potentially being a number and converting it to as near as floating point can manage. If I move filtering down to just this line in question past the next two steps, which promote the headers and then change the field types (this field was previously type any, but is now type text), I get exactly the same results.

I can't just round off, for one it's not a numeric field, and for two we use different suppliers who have different variations of the number of "decimal places" after their "decimal point", so there would be no easy way to implement any sort of rounding in any case. I need Power Query to regard this as text throughout - I fear that this is an issue with the data in Excel, as I've just tried typing over one entry with the same value and it's corrected the problem, but can someone suggest the best way to resolve this issue? The data runs into over 100k rows so searching for everything that might be affected isn't going to be practical

[jthomson]

I stated in the original post that this is in an issue with the data type and that changing the type to text produces the same results...

[nandukrishnavs]

@jthomson 

Try to replace your PQ with this

Source = Excel.Workbook(File.Contents("filename"), null, true),
#"step2" = Source{[Item="sheetname",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"step2" ,{{"Column10", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] = "P054153"))

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[amitchandak]

@jthomson . In edit query mode, Right-click on the table name. Choose advance editor. You will see a line with  data types, correct the data type and check

Something like this

let
Source = Excel.Workbook(File.Contents("C:\Users\Amit.Chandak\Dropbox\power bi\salesAfter regdate.xlsx"), null, true),
sales_rep_Sheet = Source{[Item="sales_rep",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(sales_rep_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Sales_Rep ", type text}, {"Start_Date", type date}})
in
#"Changed Type"