Power Pivot - Change a Table from a Connection to Power Query

[Paul Rockliffe]

I have a complex Data Model that I built in Excel 2011 using Data Connection - Find .csv File and Create Connection to Data Model. I have 7 .csv files that all end up in the Data model/Power Pivot and are then used to build Pivot Tables, Charts and drive various Slicers.

It was done this way because 2011 didn't have Power Query, but the main limitation is that I have lots of versions of this spreadsheet using different versions of the 7 .csv files. I use Refresh All to grab new data, but it means it's quite time consuming to update and if I hit issues with the data - duplicates and blanks mainly - I have to manually edit the csv files to maintain referential integrity in my Data Model.

So I'd like to change the setup so that the Data is imported using PowerQuery, with the result of the Query going into the Data Model. Essentially substituting one result for the other. The data won't change, just the route into the Data Model, so I can have a few more options for fixing issues that arise, but also so I can quickly apply a filter as I refresh to create my subsets and get down to a single Template file and a single system data export.

I know how to import the data and all that side of it, I know how to get it from Power Query into Power Pivot, what I'm not sure on is how I remove the existing connections, add the new tables to PowerPivot, without needing to recreate all my Pivot Tables, Charts and Slicer Connections. Does anyone have some hints etc?

 

[Paul Rockliffe]

Not sure why this has been moved to PowerBI, the issue is Excel Data Model.

I have a partial solution, if I delete the tables in Power Pivot it doesn't screw up the Pivot Tables, so I can then substitute in the Query Result and avoid name changes. That method means only the Relationships need to be reset, which is relatively simple.

Unfortunately on some of my tables I can't delete them because, "Object reference not set to an instance of an object." Not sure what that means.....