none
Power Query not recognizing values stored in Excel

    Question

  • Hi,

    I have an Excel file from which I need to load data
    unfortunately Power Query returns different values than the values that are actually stored in the excel i am loading

    e.g. the value stored is "1,12345" ("," is the decimal point in Germany)
    Power Query returns this as "112345"

    the funny thing is that a similar value, e.g. "2,234567" is returned correctly
    also funny is that Power Query seems to add its own decimal points for long numbers
    so "0,000595220380345823" becomes "5952203803458,23"

    the sample file is public and can be downloaded here:
    http://www.statistik.at/web_de/static/kfz-neuzulassungen_jaenner_bis_dezember_2012_061767.xlsx

    the values that do not work start in row 264
    Columns C, D and E work fine but F behaves as described above

    any ideas on this?

    kind regards,
    gerhard


    - www.pmOne.com -

    Friday, September 06, 2013 2:44 PM

Answers

  • this issue is now solved with Power Query v2.9.3502.261 and later

    all values from my above Excel file get imported correctly

    -gerhard


    - www.pmOne.com -

    Tuesday, January 07, 2014 8:21 AM

All replies

  • Hi Gerhard,

    I'm getting an HTTP 404 error when trying to download the sample file. My initial suggestion is to make sure that the Workbook Locale (under the POWER QUERY ribbon tab, Workbook Settings group) is set to your locale. This drives how Power Query will interpret the values within the current workbook, also when connecting to external sources.

    You can find more details about Power Query support for Internationalization in this page: http://office.microsoft.com/en-us/excel-help/internationalization-HA104061425.aspx

    Hope this helps. Thanks for using Power Query.

    Regards,
    M.

    Saturday, September 07, 2013 8:14 PM
  • Hi,

    I already tried all kind of different Locale-settings for Power Query, but none of them helped to solve this issue
    and as I said, for some columns/cells it works just fine whereas for others it fails so even if changing the Locale-Setting would work, I am afraid it would affect the columns/cells that are currently working.

    the download works just fine for btw
    shall I send you the document per mail?

    -gerhard


    - www.pmOne.com -

    Monday, September 09, 2013 12:15 PM
  • Hi Gerhard,

    I was able to download the file just this morning. Thanks for sending that out.

    There seem to be a couple of issues here:

    • Parsing decimal delimiter: It seems that the data in the input spreadsheet is represented as 2,330 in the cell (for instance, row 265, column E) but the fx bar value says 2330 for this cell. Could you confirm if you are seeing the same value? Power Query uses the underlying cell value, which seems to not have a decimal component in this case.
    • Long decimal values: It seems that the actual values in the input file and the output from a PQ query when landing on the sheet match.

    Talking about having data coming from multiple locales in a single workbook, you can customize type transformations at column level and specify a locale per transformation. Cell-level locale transformation aren't supported though, but based on the input data that I am seeing on this file, column-level granularity should be enough.

    Thanks,
    M.


    Monday, September 09, 2013 5:51 PM
  • Hi Miguel,

    column E is working fine but column F causes the described problems
    e.g. Cell E265 has a value of "0,693431743102884" in the fx bar (where "," is the decimal separator for german locale)
    so the source seems to be fine, its just that PQ shows this as "69343174310288392" without any decimals??

    workbook setting is already set to "German (Germany)" - I also tried different ones but nothing worked correctly
    also using "Change Type --> Using Locale" did not work as I first have to use "Change Type --> Text" or "Change Type --> Number" before I can use "Change Type --> Using Locale" and once I convert the value "69343174310288392" to text or number it of course would loose the decimal separator anyway

    another funny thing I just realized is that if I change the format (not the value!!) of that cell from originally "Number with 1 digit" to "Number with 2 digits" it works just fine, changing the format back to "Number with 1 digit" it fails again

    so it seems that PQ cannot handle formats with one digit??
    actually I would expect PQ to use the value in any case and ignore the formatting??

    kind regards,
    gerhard


    - www.pmOne.com -

    Monday, September 09, 2013 8:11 PM
  • any feedback on this??

    - www.pmOne.com -

    Monday, September 16, 2013 12:45 PM
  • Any more ideas for Gerhard?

    Thanks!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Wednesday, September 18, 2013 8:39 PM
  • Is this still an issue?

    Thanks!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Thursday, September 26, 2013 10:44 PM
  • Hi Gerhard, Ed,

    Sorry, I can’t seem to replicate the problem that you are seeing.

    First, Power Query doesn’t look at excel formats when bringing in excel data, it just uses the data, and it comes in as “any” type.  Second, the document locale setting only changes how PQ consumes text values, and since these aren’t text values changing the document locale won’t have any effect.

    When I do a from table on the rows that are an issue for you, I get the proper value. 

    I am not sure where else to go with this.

    Thanks,
    M.

    Friday, September 27, 2013 2:34 PM
  • Hi Miguel,

    you obviously moved the data to a different file, changed formattings ("as table") etc.
    this does not really solve my issue - as you said, Power Query (should) does not look at any excel formats so those steps should not be necessary

    can you please try to import the original file?

    I use this M-Query:

    let
        Source = Excel.Workbook(File.Contents("C:\kfz-neuzulassungen_jaenner_bis_dezember_2012_061767_Orig.xlsx")),
        Dezember = Source{[Name="Dezember"]}[Data],
        RemovedFirstRows = Table.Skip(Dezember,263)
    in
        RemovedFirstRows

    and this is the output I get:

    where "Column6" is obviously wrong

    kind regards,
    gerhard


    - www.pmOne.com -

    Monday, September 30, 2013 6:55 PM
  • Hi Gerhard,

    Have you tried importing this with the latest version of Power Query (available here)? I tried reproducing your steps using "From Excel" (en-US locale) and it looks like the product is identifying the numbers as dates. Please let us know if you're still seeing the issue after updating.

    Thanks,

    Theresa

    Tuesday, October 08, 2013 5:23 PM
  • Hi Theresa,

    yes im using the latest version (2.7.3416.121) and if your Power Query returns the values as dates then you just found another bug ...

    just take a look at the original file where "Alfa Romeo" has a value of 0.693431743102884 in column F

    -gerhard


    - www.pmOne.com -

    Tuesday, October 08, 2013 8:14 PM
  • Hi Gerhard,

    The latest version is actually 2.8.3443.101 as we released an update last week.

    Interpreting the cells as datetimes seems to be caused by the initial interpretation of the decimal values as large values (as you were seeing), which is also how Excel represents datetimes. I am now seeing the issue as you described it if I copy your specific table into a new workbook and then try to import it. I can also see that formatting the cells to use 2 decimal place fixes the import on your table. Unfortunately, recreating a similar table is not showing the same issues but at least we have your table to work with. Let me file an issue on our side to investigate a further now that we can fully reproduce it.

    Thanks,

    Theresa

    Wednesday, October 09, 2013 2:37 PM
  • Ok, I just upgraded to 2.8.3443.101

    for some reason my "Update" button on the Power Query ribbon was greyed out before making me assume that I have the latest version??
    is this because 2.x.x.x is just preview and the "Update" button only works for new released versions (1.5.x.x)?
    or do I need to sign in with my Office365 account to get the automatic updates

    anyway, even with the new version I still get the values convert as big numbers, and not as dates as in your case??

    very confusing

    -gerhard


    - www.pmOne.com -

    Thursday, October 10, 2013 7:42 AM
  • Hi Gerhard,

    The update notification has not been sent out yet, usually we try to align it with a public announcement which should be released soon. 

    We've identified the issue - it looks like there were some assumptions about types built into our document processing that we are working on fixing. It should be available in an upcoming release. Until then, you've determined the workaround (updating the formatting) and we appreciate your diligence in reporting the issue!

    Please let us know if you hit anything else. Thanks,

    Theresa

    Monday, October 14, 2013 6:33 PM
  • Thanks, that's great news!

    looking forward to the new version where it is fixed
    could you also post a short notification here once it is public so we can close this thread as resolved

    -gerhard


    - www.pmOne.com -

    Tuesday, October 15, 2013 9:46 AM
  • Hi Theresa,

    FYI I've experienced a bug in this new release. I get an error when i try to read a xml tyables from the web stating some problem with ";". There's no ";" in the XML table and it sure was working fine with the former version (1.5 something).. Any fix for this.. I am trying to get data from this address : http://geo.oiorest.dk/kommuner/0751/postnummer.xml

    Any suggestions ?

    Bobby


    bobhen

    Friday, October 25, 2013 12:56 PM
  • I can answer this myself :) Had to change it to use UTF-8.. That wasn't neccessary before.. But it solved the problem :)

    bobhen

    Friday, October 25, 2013 1:30 PM
  • I cant find that file - it says "File or Directory not found"

    http://geo.oiorest.dk/kommuner/0751/postnummer.xml

    -gerhard


    - www.pmOne.com -

    Friday, October 25, 2013 1:30 PM
  • Hi Bobby,

    I am also not able to access the file but I am glad you found a resolution. The problem discussed on this thread was specific to Excel files so it may not have been the exact same issue. Please let us know if you have any other issues.

    Thanks,

    Theresa

    Tuesday, October 29, 2013 5:11 PM
  • this issue is now solved with Power Query v2.9.3502.261 and later

    all values from my above Excel file get imported correctly

    -gerhard


    - www.pmOne.com -

    Tuesday, January 07, 2014 8:21 AM
  • Hello everyone,

    I seem to be facing a similar issue and since there are not many discussions on this on the web I'm posting this here.

    I accessed an online excel document with power query. You can get the URL for the document here:

    https://www.destatis.de/DE/ZahlenFakten/LaenderRegionen/Regionales/Gemeindeverzeichnis/Administrativ/Archiv/GVAuszugQ/AuszugGV2QAktuell.xls?__blob=publicationFile

    The issue apparently is that some columns (i.e. L-O) have the custom format "## ### ##0" which makes number such as 12345 appear as 12 345. Power query imports these as "12 345". This seems to suggest that somehow the format is being taken into account. In any case when trying to change the format to number Power Query says it cannot do that, supposedly because the text is composed of two separate groups of numbers.

    I tried to reproduce this with a simple local excel file with the same custom format but strangely enough this worked. Maybe this is because in the online file there are a few non-number lines above which I excluded with Power Query.

    Since I'm new to Power Query I might have overlooked something. Thanks for any comments.

    Best,

      Markus


    Tuesday, August 12, 2014 6:21 AM
  • There are three entirely different code paths in Power Query for reading Excel files. The first is when you use Excel.CurrentWorkbook to read the workbook that contains the current query. The second is when you use an OpenXML file (i.e. an "XLSX" file). The third is when you use a binary file like XLS or XLSB.

    For the third of these, the only option we have for reading the data is to use the "ACE" OleDb provider. And unfortunately, that gives us almost no control over how the data is read. You weren't able to reproduce this with a local file because you probably created an XLSX (which is the default file format) and we're able to be much more intelligent about reading those files.

    Tuesday, August 12, 2014 3:18 PM