Results 1 to 2 of 2

Thread: Text numbers are being changed by PQ to have many decimal places

  1. #1
    Seeker jbaisch's Avatar
    Join Date
    Apr 2016
    Location
    Arizona, USA
    Posts
    8
    Articles
    0
    Excel Version
    2010 and 2016

    Text numbers are being changed by PQ to have many decimal places



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am pulling multiple .xlsx files in using PQ. One of the columns in the data files is called "Orig Cust Reference" which is formatted as a text, but looks like a decimal.
    500.1903
    717.71714
    2.5
    505.3111

    When PQ pulls the data in, it is adding what appear to be additional decimal places. The 717.71714 shows 717.717409999999997 and 505.3111 goes to 505.3111000000001
    Stepping through the PQ load, the data type is never changed from text.

    Any suggestions on how I might resolve this issue?
    Thank you.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hey,

    If you go back to the source Workbook, select that cell and increase the number of decimals on the cell, what happens?

    My guess would be that the decimals will start to show, and that the source data is actually values not text. When power query pulls in data from Excel, it pulls in the true values, not the formatted values you see.

    Correcting this could be complicated depending on whether there are rules that you can leverage. You may need to round the value down specifically in order to keep values as they are, and possibly come up with more complicated methods if the number of decimals varies.

    Ultimately, though, Iíd suspect your source data isnít what youíre expecting here...


    Sent from my iPhone using Tapatalk
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •