Increase number of decimal places on imported data

[ToffeeMark]

Hi,

I'm trying to import data from an excel workbook (lots of files) but the field is rounded in excel and rounded when I load it into powerquery. e.g. 3.88 instead of 3.875.

I can import an individual sheet ok as 3.875 but when I try to import all the files in the folder it rounds as per the excel workbook.

There are no change type steps in the transform file, it's difficult to pin down, what I'm asking is, can I tell powerquery to import 3 decimal places, or all the digits?

 

[sandy666]

a bit

and after

 

[ToffeeMark]

a bit
View attachment 27708

Yeah, it's annoying. If i do it from a sheet, I get 3.875. if i do it from multiple workbooks I get 3.88 in power query with no transfrom change type steps. it's bugging me. I'll make some dummy files and put them in a folder and try again.

 

[sandy666]

would be nice to see these dummy files with result file (zip and post a link to this via onedrive, googledrive or any similar service)

maybe check system settings

 

[ToffeeMark]

The dummy folder/dummy files worked fine. They were on my desktop though, the problem files were on the shared drive. I'm going to put the folder on my desktop and see if it works, I'm wondering if it might me to do with protected view or something. I'll post back when I've figured it out.

 

[sandy666]

try one more thing
Excel Options

 

[ToffeeMark]

No, unfortunately nothing is working - it brings the data in as displayed in Excel (Number format) - the only way I can see to fix it is to change the format in each workbook to general first in order to reveal all the decimal places, then save, then import. Bit of a pain but theres obviously something in the workbook that.....wait a minute. Just checked, I'm getting these files from an external source and they're in "Microsoft Excel 97-2003 Worksheet" Format, by saving them as "Microsoft Excel Worksheet" - (xlsx) it suddenly all works.

Must be a problem with older format excel workbook imports I've not come across before. Not sure if theres a way of handling that with power query so I'll just go in and save each file in the new format.

 

[sandy666]

Power Query takes what it sees
if in the sheet is 5.875, it is also in PQ, if 5.88 - it also 5.88 in PQ