Power Query to Power Pivot

[Comfy]

Finally have a version in work that will allow me to start using PP & PQ, yey! only 2 years late (Excel 2010, Win 7)

Anyway, I have used PQ to format some data and I get different results depending how I create the connection to PP.

If in PP I goto Design > Existing Connections and select the Query - PP returns the data excluding the calculated columns.

If in Excel I goto Data > Connections > Select the appropriate connection > Properties > Definition > Copy the Connection string > Create an OLEDB/ODBC connection in PP - PP returns all columns.

I have compared the resulting connection strings using EXACT() and the function returns TRUE.

Is there something that I'm missing/need to be aware of?

 

[Ozeroth]

This has been an issue for a while with Power Query & Power Pivot in Excel 2010.
Someone out there may know the technical reason - I don't. It all works much more smoothly in 2013+ though

To restate the problem: In Excel 2010, in the PowerPivot window, when you select a Power Query connection from Existing Connections > Workbook Connections, the table that appears in the Power Pivot window is sometimes called "Query" (rather than the name assigned in Power Query) and rows/columns of the table can be missing.

3 ways around this that I'm aware of:

1. Do what you did: copy the connection string from Excel Data Connections and load via OLEDB/ODBC connection in the Power Pivot window.

2. Load the table once from Existing Connections > Workbook Connections, delete the resulting table from the Power Pivot window, then reload the table from Existing Connections > PowerPivot Data Connections section.

3. Follow Sam's suggestion here (in comments): Load Power Query Directly to Power Pivot with Excel 2010 - Excelerator BI
i.e. in Excel Data > Connections, change the Command Type to Default and delete the Command Text. Then you can load through Existing Connections > Workbook Connections in the Power Pivot window.

 

[Comfy]

Thanks for the reply and noted for future.