none
Decimal places rounded up when trying to load Excel file using Power Query RRS feed

  • Question

  • Dear all,

    I was trying to load a data file (in Excel format) using Power Query.  

    In the original file, some of the fields are in number format with 2 decimal places.  When I come to the "Query Editor" of Power Query, those records with no data for those number format columns are showing "null".  The problem is when I check the data after I done with the query, all numbers become integer and rounded up.  

    That is, 1.5 becomes 2; 5.5 becomes 6!!!

    Columns having these issues have no data in the first 16 records.  Also, less than 10% of the records have data in those columns.

    I was thinking of adding sample format at the very first line.  Too bad that I cannot fool the system. 

    Is there a way to fix this?

    Many thanks,

    Swing11

    Wednesday, March 30, 2016 9:52 AM

Answers

  • Hi Swing11. When inferring the data type of a column, we inspect up to 200 rows of data. So if the values containing decimals occurred after the 200th row, the behavior you're seeing is expected and you'll need to manually change the data type from "Whole Number" to "Decimal Number".

    If it's happening when there are fractional values within the first 200 rows, then that's a bug. If you could provide a sample file that exhibits the behavior, that would be really helpful.

    Please let us know.

    Ehren

    Thursday, April 7, 2016 7:36 PM
    Owner

All replies

  • Power Query will automatically add a step "Changed Type" once you import data from Excel. Have a look a the code there: Are the columns in question formatted as "any"? Then you should just change that to "type number".

    Imke Feldmann TheBIccountant.com

    Wednesday, March 30, 2016 4:39 PM
    Moderator
  • I did check the type in the code.  It stated as "type number".  That is the reason why I am so frustrated!
    Thursday, March 31, 2016 1:39 AM
  • Understandable - sounds like a bug.

    What I would be interested in is if this is an "old Excel file": Meaning if it has been create long a go an constantly worked upon and what happens if you copy the data, paste them in a new sheet and access that instead.


    Imke Feldmann TheBIccountant.com

    Thursday, March 31, 2016 6:19 AM
    Moderator
  • The file is just downloaded from other system.  We want to make use of the unpivot function in Power Query to transform it to something that we can load into another application.

    By the way, I found a trick to temporarily fix this.  Not sure if this would work later but I tried several times today and it seems working ok.

    1. In the original data file, change the format for those problemed columns by adding "comma style". (Apparently, it was in number format anyway)
    2. Add a sample record right under the header line.  Type in 0.1 in all the problemed columns in that sample row.
    3. Save it and open it from Power Query.

    After doing the above, it seems Power Query won't round up decimal numbers to integer.  

    Guess there is a bug for fields with very few data in them.

    Thursday, March 31, 2016 8:34 AM
  • Well done!

    However, this bug shouldn't be there. I think the team would definitely take interest and care about it if you would send them a frown.


    Imke Feldmann TheBIccountant.com

    Thursday, March 31, 2016 8:53 AM
    Moderator
  • "Guess there is a bug for fields with very few data in them."

    Probably a bit more than that. I created an Excel file based on your description (including a column with no data in the first 16 rows, followed by a few decimal numbers). I tried many variations, but was unable to duplicate your problem. The issue seems to be peculiar to your file. What is the specific file type - xlsx, xls, etc?

    Thursday, March 31, 2016 1:51 PM
  • The file is a xlsx file.

    The file contains totally 1315 records.

    For those columns having problems:

    column V: 81 out of 1315 have data, others are blank.  The 57th record starting to have data in that column.  Among the 81 records having data, the first 41 of them are integers.


    column AC: 57 out of 1315 have data, others are blank. The 15th record starting to have data in that column.  Among the 57 records having data, the first 48 of them are integers.

    Friday, April 1, 2016 1:57 AM
  • Hi Swing11. When inferring the data type of a column, we inspect up to 200 rows of data. So if the values containing decimals occurred after the 200th row, the behavior you're seeing is expected and you'll need to manually change the data type from "Whole Number" to "Decimal Number".

    If it's happening when there are fractional values within the first 200 rows, then that's a bug. If you could provide a sample file that exhibits the behavior, that would be really helpful.

    Please let us know.

    Ehren

    Thursday, April 7, 2016 7:36 PM
    Owner
  • Did you find a solution?  I am having the same issue.
    Friday, July 1, 2016 2:01 PM
  • Hi Ehren,

    A most dangerous bug.  I beg to differ though, this is not the behavior a user expects.  When will this be addressed, if not already.  Many thanks.

    Wednesday, December 28, 2016 12:28 PM
  • Hi Nin,

    I agree this isn't what a user would expect. However, it's "expected" in the sense that it's currently by design.

    The challenge is that we can't (for performance reasons) inspect an unlimited number of rows when inferring the column types. We have to cap it somewhere, whether it's 200 rows (the current limit) or 2 million. In other words, our type inference is an educated guess. And because it's a guess, at times we're going to get the type wrong.

    I agree that it would be great if this didn't happen, but regardless of what limit we pick, it will still happen in certain scenarios, and the user will have to manually change the type we inferred.

    Ehren


    Tuesday, January 3, 2017 9:54 PM
    Owner
  • My files (reading from a sharepoint folder) do have some empty lines but definitely they have decimal data within the 200 rows limits still I had to open manually one by one and force the affected columns formatted as numbers to get PowerQuery to import them without converting into text formatting.

    Not a massive effort, since they are only 7 files but something is weird...

    and for any new file I will have do reapply the correction.

    Cheers,

    Andrea

    Tuesday, April 25, 2017 4:12 PM
  • Hi Andrea. It sounds like you may be hitting a different issue. You say the columns are being treated as text instead of numbers? Can you give an example of the data contained in these columns?

    Ehren

    Tuesday, April 25, 2017 4:19 PM
    Owner
  • Hi Ehren,

    my excel input files are report from SAP - unfortunately I can't share them.

    Each report has some initial report related info and then the report (first 12 rows).

    The report itself is made up of two parts, everything between A and AM columns is "Text" type in the excel (but some are actually numbers, like SAP ID, Transaction Code, etc). Then from col AN to AW where the transactions data are (hrs, payroll, etc) each cell is formatted as "General" if empty or "Custom" if contains values (#,##0;"("#,##0")";#,##0;@).

    When extracted from Power Query everything is mapped as text and all decimal numbers in AN/AW sections are rounded to integer (and mapped as text).

    I think the reason could be that the actual report body starts in row 13 (real headers) - so while doing the conversion it finds text in rows 13 and so that it is the end of it.

    Thanks,

    Andrea

    Wednesday, April 26, 2017 11:47 AM
  • Ok, that makes sense.

    Ehren

    Wednesday, April 26, 2017 4:30 PM
    Owner
  • But the point that I still don't undestand is: why it is rounding the numbers? 3.25 is 3; 4.75 is 5 and there is no way for me to recover the original value except forcing the formatting in the source files.
    Wednesday, April 26, 2017 5:56 PM
  • Hm, I see. That's indeed odd.

    I'm having a hard time visualizing where things are going awry. Can you share at least some (obfuscated) details of the data and associated queries? For example:

    • What's an example of the kinds of values these (improperly rounded) numeric columns contain? Do they contain headers, or just numeric values and blanks?
    • Do the first 200 rows (starting from the top of the sheet, not where the data starts) contain any decimal place values, or only integers?
    • Is the issue with an automatically added Change Type step?
    • If so, what is the Change Type step converting these columns to? Whole Number?
    • If you delete this step, do you see the original values?
    • If not, what exactly do you have to do in order to get the full numeric values to come through?

    Ehren




    Wednesday, April 26, 2017 6:06 PM
    Owner
  • I think I identified the issue - the files are all "*.xlsb".

    If I open their  "*.xlsx" version everything works fine.

    I don't have any credit on this, I found it after having read this article:

    https://bondarenkoivan.wordpress.com/2016/06/01/power-query-xlsb-vs-xlsx/

    Now, I have a query - by when the xslb engine will have the same abilities of the xlsx?

    I must admit that I find quite odd that the most efficient format is the sub-optimum choice.

    A couple of months ago I've converted all my xlsx files into xslb after having read about the benefit of the binary format and now I realise I made the wrong choice?

    thanks, Andrea

    Friday, April 28, 2017 5:53 PM
  • Hi Andrea. The only puzzling thing about the xlsb theory is that it sounds like you were able to somehow get unrounded values, even from an xlsb source. Can you clarify if this was the case?

    Thanks,

    Ehren

    Friday, April 28, 2017 11:27 PM
    Owner
  • Hi Ehren,

    as per my message above I had to force the type in the xslb source to get power query not to round the numbers to integers.
    If I don't do that the numbers are rounded to integers.

    Why such a massive difference between xlsx and xlsb formats?

    Such a pity...

    Tuesday, May 2, 2017 7:49 AM
  • Hi Andrea. Can you explain what you mean by "force the type in the xslb source"? I'm not sure if you're referring to changing the format of the numbers in the xlsb file, modifying the Change Type step in your query, or something else.

    The xlsb files are read using a legacy library (which we did not write) called ACE, which has quite a few known issues/limitations. The xlsx files are Open Xml are are thus read using a completely different pipeline, which is why the results you're seeing differ.

    Ehren

    Tuesday, May 2, 2017 3:42 PM
    Owner
  • Ok Ehren,

    thanks for the explanation.

    I mean I open the excel xslb file (not from power query) and change the format type of the columns as Number from the excel ribbon itself, in the source (not from powerQuery), and save the files back.

    But understanding the issue with xslb is not worth working on it till this get a proper rework.

    Cheers,

    Andrea

    Wednesday, May 3, 2017 10:57 AM
  •  I just had this problem lately.  Worse, the decimal values are being truncated to integer and not even rounded. I found the first cause was that I had nulls in some of the incoming values,  made it 0 if null.  Now it sees it as numeric integer instead of alphanumeric.  But still losing my decimals.  Instead of 0 if null, I have it 0.0 if null or 0. Now power query correctly sees that as a decimal column. 
    Wednesday, October 24, 2018 7:33 AM
  • Hi cesrlp. Can you create a simple input file that demonstrates the problem you're seeing, along with your expected vs. actual results? It's not clear from your description exactly what the issue is.

    Ehren

    Wednesday, October 24, 2018 4:22 PM
    Owner
  • I am having the same issue as the poster. I have a table with about 5000 rows. I am unpivoting 84 columns into about 400,000 rows. All of the unpivoted columns are in decimal format with 2 decimal places. In power query it says the resulting column is in decimal, and it is for a little over half the columns, but it is converting the other columns to integer. I have tried the workarounds suggested in this thread but none of them work. Is there any more information on how to deal with this issue?
    Wednesday, December 4, 2019 2:40 PM
  • Hi Pope. Can you share a simple repro file that demonstrates the issue?

    Ehren

    Wednesday, December 4, 2019 8:05 PM
    Owner
  • I'm having the same problem in Excel 2016 Query. I have a large data set and the first 200 rows do not have decimals they are all zeros.  The data set is a csv file (Text).  I have tried changing the data type in the query to number:decimal but that didn't work.  It is rounding all the decimals to integers.

    Has anyone found a solution to this problem?

    Wednesday, April 15, 2020 1:35 AM
  • To reiterate what I've said above: For anyone experiencing issues with numbers being rounded/truncated/etc., the best thing you can do is create a sample file that demonstrates the issue you're seeing, and then share it here on the forum. Ideally, you could do this by starting with a problematic Excel file and removing any sensitive information. This is the best way to ensure we understand the issue and are able to either fix it or help you resolve it on your end.

    1stMunchkin, it sounds like changing the column to Decimal may not have helped because the conversion was done after the conversion to Int64. You likely need to replace the automatically added type conversion, changing it from Int64 to Decimal.

    If this doesn't help, a sample file demonstrating the issue would be incredibly useful.

    Ehren

    Wednesday, April 15, 2020 4:28 PM
    Owner
  • Ehren,

    How do I get you the file?


    • Edited by 1stMunchkin Thursday, April 16, 2020 10:21 PM
    Thursday, April 16, 2020 9:06 PM
  • The simplest way would be sharing it via OneDrive or another similar product and pasting the link here in the forum. Does that work for you?

    Ehren

    Friday, April 17, 2020 5:01 PM
    Owner
  • Ehren  Here is the Like https://1drv.ms/u/s!AgKhoLHtUUtvgnbgt2tcgwiQ6WpH

    Let me know if you find anything.

    Thanks

    Monday, April 20, 2020 10:27 PM
  • Thanks. Do you mind sharing a simpler example? I'd prefer not to open a Macro-enabled workbook and have to set up an odc connection.

    Ehren

    Monday, April 20, 2020 10:32 PM
    Owner
  • I took out the macros and the odc connection.  Let me know if this is what you need.  

    https://1drv.ms/u/s!AgKhoLHtUUtvgnbgt2tcgwiQ6WpH

    Thanks For your Help!

    Tuesday, April 21, 2020 11:23 PM
  • It's complaining because it can't find the file "Category Identification.xlsx".

    Ehren

    Wednesday, April 22, 2020 3:49 PM
    Owner
  • Hi, 1stMunchkin.

    Try to change formula of the step #"Changed Type" like this:

    Table.TransformColumnTypes(
        #"Promoted Headers",
        {{"Project Code", type text}, 
         {"User Name", type text},
         {"Task Name or Expense Type", type text},
         {"Billable Hrs", type number},
         {"Project Estimated Hrs", type number},
         {"Entry Date", type text},
         {"Non-Billable Hrs", type number},
         {"Comments", type text},
         {"Client Name", type text},
         {"Project Status", type text},
         {"Project Start Date", type date},
         {"Project End Date", type date},
         {"Project Name", type text},
         {"Total Hrs", type number}})

    Wednesday, April 22, 2020 4:18 PM