none
Power Query not recognizing values stored in XLS-file RRS feed

  • Question

  • I have a XLS-file that won't import correctly in Power Query.

    The file can be found here: https://ufile.io/cz2wi

    The problem is in column 3 where I have numbers with several decimals e.g. 1,1234. The numbers are formatted in excel as numbers with two decimal spaces e.g 1,12. When I import this with Power Query, it only returns the formatted value, 1,12 and not the full value. In other words PQ is dropping decimals. If I change the cell formatting in the Excel file and decrease the number of decimal spaces to one or none PQ drops more decimals. The only way I'm able to get the full value is to increase the number of decimal spaces (alot).

    What is causing this weirdness? What can I do to avoid losing information?

    If i change the file format from XLS to XLSX the problem disappears, but the file is an autogenerated report from a system I don't have access to. 

    I'm currently running Power Query version 2.49.4831.381 32-bit

    Tuesday, February 6, 2018 12:57 PM

Answers

  • Hi Kristian. Unfortunately the mechanism we use to read legacy .xls files only exposes the formatted value. I stepped through our code and the full value doesn't seem to be available.

    Ehren

    Wednesday, February 7, 2018 7:38 PM
    Owner

All replies

  • Hi there. For legacy Excel files, we're only able to get the formatted value while importing the data. Is it possible to generate a different file format from the reporting system (such as CSV)?

    Ehren

    Tuesday, February 6, 2018 9:35 PM
    Owner
  • Thanks for replying.

    I've contaced our system manager and he's looking in to changing the file format. I suspect it shouln't be a problem. Is there anything I can try in the meantime or if he comes up short? Will this issue with XLS-files be solved in a future Power Query release or is this just the way it is?

    EDIT: Apparently changing file format is quite the issue, so any possible workaround for me to try would be greatly appreciated
    • Edited by KristianLP Wednesday, February 7, 2018 1:24 PM
    Wednesday, February 7, 2018 8:47 AM
  • Hi Kristian. Unfortunately the mechanism we use to read legacy .xls files only exposes the formatted value. I stepped through our code and the full value doesn't seem to be available.

    Ehren

    Wednesday, February 7, 2018 7:38 PM
    Owner