locked
How to add a column of source data to an established PowerPivot workbook RRS feed

  • Question

  • I have a PowerPivot workbook with calculated columns, measures, pivot tables, and charts driven off the source data that I manually "paste append" every week.  A new column has been added to the source data, which I would like to add into the PowerPivot workbook and perform new analysis.  When I try to "paste append" or "paste replace" in PowerPivot, I get the following error message:

    The number of columns to paste (24) must be the same as the number of columns being pasted into (23).  Calculated columns are populated automatically and are not considered in the column count.

    How do I add this new column of source data to my PowerPivot workbook without starting from scratch?

    Monday, October 15, 2012 4:43 PM

Answers

  • Why dont you copy the data from PowerPivot into excel and then add the new data + new column, and then import it into PowerPivot using LinkedTables?

    You can name the table the same, and then do the relations also... Shouldnt that work?


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

    Some Random Thoughts

    Follow me on Twitter

    Tuesday, October 16, 2012 3:19 PM
    Answerer

All replies

  • you can edit the existing connection and add/remove the fields. Go to Toolbar > Design > Existing Connections > Edit the connection that's used to import data.

    I have a blog post but it's for Tabular model - adding it here just for reference. http://parasdoshi.com/2012/04/11/how-to-add-a-column-in-an-imported-table-while-developing-sql-server-2012-analysis-services-tabular-model/


    Paras Doshi (Blog: ParasDoshi.com | Twitter: @Paras_Doshi )

    Monday, October 15, 2012 5:55 PM
    Answerer
  • Thanks for the reply.  Since I manually paste data from the clipboard, I do not have an existing connection to edit.  Also, Table Properties is not accessible for the same reason.
    Monday, October 15, 2012 6:09 PM
  • oh ok, I cannot think of anything from the top off my head right now - I usually work with models where we establish a connection to a data source.

    Paras Doshi (Blog: ParasDoshi.com | Twitter: @Paras_Doshi )

    Monday, October 15, 2012 6:20 PM
    Answerer
  • Are you using Linked Tables as the source for PowerPivot?

    If yes, then you need to create a column to the right and the model will automatically understand that a new column has been created. And then, you can paste your data (which will have 24 columns).


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

    Some Random Thoughts

    Follow me on Twitter

    Monday, October 15, 2012 6:38 PM
    Answerer
  • No, I'm not using Linked Tables as the source for the particular PowerPivot table that I want to add a column.  I paste the data weekly directly into PowerPivot.  Eventually, I want to connect directly to a database but, for now, the data is emailed to me.
    Monday, October 15, 2012 7:55 PM
  • I am not clear how you paste data directly into PowerPivot without using LinkedTables. Can you upload an image here of the area where you paste the data?

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

    Some Random Thoughts

    Follow me on Twitter

    Tuesday, October 16, 2012 1:58 PM
    Answerer
  • Originally, I started with a blank spreadsheet, opened PowerPivot, and used the Paste button in the PowerPivot Home ribbon.  I pasted my first set of data from the clipboard to a PowerPivot table with no links or data connections.  Next, I created several small tables in the spreadsheet and linked to additional PowerPivot tables.  With these tables in PowerPivot, I then created many, many pivot tables, charts, and measures.  I update the data every week by copying to the clipboard and using the Paste Append button to add to the first PowerPivot table.  Finally, I refresh the pivot tables so the tables and charts include the latest data set.

    In hindsight, I should have pasted the data into the spreadsheet rather than PowerPivot and using a linked table.  I was trying to avoid a large filesize resulting from the data residing in the spreadsheet.  At this point, I'm stuck because I don't know how to either add another column of source data using my current method or connect the existing PowerPivot table to a different data source (linked table or database connection) without the considerable effort of redoing all the pivot tables, charts, and measures. 

    Tuesday, October 16, 2012 2:33 PM
  • Why dont you copy the data from PowerPivot into excel and then add the new data + new column, and then import it into PowerPivot using LinkedTables?

    You can name the table the same, and then do the relations also... Shouldnt that work?


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

    Some Random Thoughts

    Follow me on Twitter

    Tuesday, October 16, 2012 3:19 PM
    Answerer
  • Go to Design Menu in the PowerPivot then select Table Properties, and then select Source. Here you go, click on the check checkbox in the column headers that you need to have in the PowerPivot.

    Cheers

    Ali Mudassar

    Sunday, May 4, 2014 8:24 PM
  • Thanks!! it helped :D
    Tuesday, June 24, 2014 4:43 PM
  • Sorry but how did you figure this out? Im having the same issue. I would like to add a column to an existing databe in powerpivot which is created by pasting data.
    Thursday, July 24, 2014 10:15 PM