How do you copy the powerpivot data into the excel workbook as a table?

Question

I have data in powerpivot that I've modified and I'd like to place it into the workbook as a regular table (and not as a pivottable). I need this so that I can run use the table for some vlookups from another worksheet in the same workbook.

Solution 1

I found this answer: link which I will try and summarize with out cutting and pasting to much. This assumes you have powerpivot installed.

1. goto powerpivot and "import from and external source" the data you want to munge and bring back into the workbook as a table
2. Key here is to select the checkbox “Enable selection of multiple tables”.
3. when prompted at the "import data" window, pick pivot report (you won't really use this)
4. go back to powerpivot with the "Manage Data Model:
5. add a new measure below your data. something like: NRofOrders:=COUNTROWS(values(FactResellerSales[SalesOrderNumber]))+0 (you won't really use this but this seems to change the query to this table so that you can use DAX)
6. also add any columns that you want ( for me, this is want I wanted that I wanted to be reflected back in the workbook)
7. back to Excel, select the data tab, click on Existing connections and select Tables: and then pick a table from your query.
8. boom, you're done (the link continues on and you should read that as it's interesting but at this point you should have powerpivot query with your modification as a table in your workbook)

The July 2014 update to Power Query (pushed out at the end of Aug) simplifies the answer. With the new Power Query Update you can pull the data into the Data Model with out having to also copy it to the Works sheet.

1. goto powerquery and import the data you want to munge. Use the option to just add it to the Data Model.
2. go back to excel and then go to powerpivot with the "Manage Data Model" button.
3. munge your data (add columns, whatever)
4. back to Excel, select the data tab, click on Existing connections and select Tables: and then pick a table from your query.
5. boom, you're done

Solution 2

In the PowerPivot window you can select the table, or elements of it, and then copy (nornal right click or ctrl +c) and paste that into your spreadsheet.

This works for reasonable amounts of data but if you try and do it with thousands of rows you may find that excel objects and falls over, based on my experience.

Vlookups kind of defeat the purpose of PowerPivot :-)

Comments

[Alex]

yeah, i'm a newbie with the PowerPivot stuff and there's just to much stuff that i need to get done that I can't figure out how to do 100% in Powerpivot so I'm sorta straddling both sides of the fence :)

[Jacob]

Nice find, will take a look!

[Alex]

Today's ( Aug 28 2014 ) PowerQuery update contains the option to do a PowerQuery to just the data model. This simplifies the answer.

[Alex]

The functionality in the above comment is found in the "Load To" menu accessed via the Queries listed in the Workbook Queries sidebar. (PowerQuery->Manage->Manage->Workbook Queries->[Query]->(right click)->Load To->Load to worksheet.

[Alex]

can't get this work work...the thread is pretty old. what version of Excel are you using?