locked
Combining tables copy/paste limit RRS feed

  • Question

  • Hi,

    Rather than continually fetch from the SQL server all invoicing data into PowerPivot, I created an .xlsx for previous years  which I now want to combine with data for the current year to date.

    So , I have two PowerPivot tables:

    The first table is the previous years invoicing data from the .xlsx.

    The second is invoicing data for the current year to date imported into PowerPivot from the same SQL table (with a date filter > the history data).

    I now want to combine the two PowerPivot tables, so I select the history table in PowerPivot and choose 'copy'.

    I then click 'Paste' to a new table but I get the error back:

    'Unable to copy the data to the Clipboard. The amount of data you selected to copy might exceed the amount of data that can be copied at one time. Try to copy a smalle amount of data. The process can be repeated as many times as necessary in order to copy all the data.'

    The table is only 72k rows with 30 columns. A bit of experimenting sees around 45k being the maximum that can be copied/pasted at a time. However, I also wanted to use the same process on other areas where the 'history' file is likely to be several hundred k for an individual year so having lots of smaller history files (e.g. monthly) or manually copy/paste within the row limit just isnt practical.

    Any thoughts or perhaps a better process to achieve this?

    Regards,

    Gordon

    Friday, December 6, 2013 1:00 PM

Answers

  • Couldn't you manipulate the SQL Query to combine the PowerPivot tables?

    Using the Copy/Paste technique is not appropriate, and I would recommend that only for PoC purposes.

    If you have Excel 2013, you can use Power Query also to combine two tables.


    Cheers,
    Jason | www.SqlJason.com
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    Tuesday, December 17, 2013 2:56 PM
    Answerer

All replies

  • Does anyone have any ideas for Gordon?

    Thanks!


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

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

    Monday, December 16, 2013 6:45 PM
  • Couldn't you manipulate the SQL Query to combine the PowerPivot tables?

    Using the Copy/Paste technique is not appropriate, and I would recommend that only for PoC purposes.

    If you have Excel 2013, you can use Power Query also to combine two tables.


    Cheers,
    Jason | www.SqlJason.com
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    Tuesday, December 17, 2013 2:56 PM
    Answerer