Power Pivot losing columns??!

[andydtaylor]

Hi

Trying to use Power Pivot on Power Query connection in Excel 2010.


I open workbook connection in Power Query. Hit next. At “Specify a SQL Query if I do “Design” and “Table import wizard”/text and select “run” all data seems to be displayed. However when I click “OK” and “finish” after the import process completes showing 0 errors only 6 columns have been imported


Thoughts/suggestions appreciated!

 

[Comfy]

If I remember correctly and my knowledge may have been superceeded by an update but the PowerPivot addin for 2010 isn't perfect.

I imagine your Power Pivot connection was created using a PQ Query and this PQ Query has since been updated which isn't being reflected in PP.

To test:

In PP go to Existing Connections > Select your workbook connection > Click Open > Copy the entire connection string > Paste into a cell in the workbook
In Excel go to Data > Connections > Select the PQ connection and choose properties > Select Definition > Copy the entire connection string > Paste into a cell in the workbook

(If the Provider isn't Microsoft.Mashup.OleDb you will need to load the connection to a table)

Compare both and you might find that they are different, due to changes that were made to the PQ Query after the PP connection was created.

You will need to re-create the connection in PP.

 

[andydtaylor]

Hi Comfy,

Thanks for your reply. This does seem to be a bug in the old plugin and using Excel 2010 setup I have. The solution I found initially was to

* Edit connection
* Set connection type to default
* Delete SQL statement from the box below

PowerPivot then finds the Power Query data in its entirety.

However I have for now abandoned Power Pivot where I really would love to use it because Power Query record set updates seem to break Power Pivot. Even just more rows appearing within the same table definition. Which is really annoying.....


Regards,

Andy