none
Errors when appending two datasets RRS feed

  • Question

  • Hi,

    I have appended data from 3 CSV files lying in a folder using Power Query.  I have named this as All India Import.  I have imported yet another dataset from 3 CSV files lying in a folder using Power Query.  I have named this as JNPT import.  I then combined these two datasets into a single dataset and named that as "All India import data".  In the screenshot below, you will observe that there are 19,128 errors that show up.  When I click on that blue text, the Power Query window shows me 0 error rows.

    Why is this happening?

    Please help.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Thursday, January 19, 2017 2:22 AM

Answers

  • Found it: the quantity field is a whole number in the India query (and in the combined table) and a decimal number in the JNPT file.

    In the example data the JNPT file has 2 quantities with decimals, causing 2 errors in the combined table.

    If you adjust the data type of the quantity field to decimal in the India query, the errors are gone.

    • Marked as answer by Ashish Mathur Thursday, January 19, 2017 8:59 AM
    Thursday, January 19, 2017 8:15 AM

All replies

  • Not sure why this would happen in this particular situation, but I came across a similar situation where errors are reported as in your example, but not showing up when following the link to the errors.

    You can try the following code:

    let
        Source = {250..260},
        Tabled = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        AddedBinary = Table.AddColumn(Tabled, "Custom", each Binary.FromList({[Column1]}))
    in
        AddedBinary

    In the Query Editor you see 2 columns: numbers 250-260 and a column with binaries, no errors.

    However, if you click next to a binary with a number > 255, that field reports an error (as Binary.FromList only accepts lists with numbers 0-255).

    When you load the result in Excel, 5 errors are reported, but you get none if you follow the link to the errors, as there are no "error" fields in the Query Editor.

    So my best guess is that you may have errors somewhere inside composite fields in your table (binaries or nested lists, records or tables).


    • Edited by MarcelBeug Thursday, January 19, 2017 3:45 AM
    Thursday, January 19, 2017 3:45 AM
  • Hi,

    Thank you for your reply.  The question is why do I not get to see these errors in 2 individual tables.  Why does this problem occur only when I am appending the two datasets?

    Any ideas.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Thursday, January 19, 2017 4:19 AM
  • It looks to me like there is no perfect fit.

    But the one with access to the queries and the data is in the best position to figure it out.

    Thursday, January 19, 2017 7:18 AM
  • Hi,

    Thank you for your help so far.  I have reduced the number of rows in my dataset drastically (no more than 10-15 rows per CSV file and there is only 1 CSV file in each of the two source data folders) to reduce the file size to a manageable level.  Even with very few rows, I am facing this problem.  Please download the data from here.

    Please help.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Thursday, January 19, 2017 7:37 AM
  • Found it: the quantity field is a whole number in the India query (and in the combined table) and a decimal number in the JNPT file.

    In the example data the JNPT file has 2 quantities with decimals, causing 2 errors in the combined table.

    If you adjust the data type of the quantity field to decimal in the India query, the errors are gone.

    • Marked as answer by Ashish Mathur Thursday, January 19, 2017 8:59 AM
    Thursday, January 19, 2017 8:15 AM
  • Hi,

    Thank you very much for your help.  That resolved the problem.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Thursday, January 19, 2017 8:59 AM