# Power Pivot - Change Power Pivot Connection to Power Query Connection



## martinequilibrium (Sep 2, 2020)

Hi lovely People!

I'm trying to update my power pivot data model to keep a table with my measures but change a Power Pivot Data Connection to a Power Query Data Connection.

Using Power Pivot Data Model Connections in my WorkbookDataModel.xlsx

This is my table:










When trying to edit to get the query:











If i try to put the same workbook to "get" the query connection from the active workbook WorkbookDataModel.xlsx it tells me of course that it is open and It cannot connect to itlsef.

Instead of the external workbook i want to use a query from WorkbookDataModel (from the same file that has the data model)

*

*

Trying to load it to the data model to guess how it connects:




This of course creates a new Table because DetalleViajes already exists.





Inspecting the properties




*VBA Method*

Using the VBA Macro recorder with the existing connections tab i got these vba snippets:







```
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\Users\martin\Downloads\directory\filename.xls;Persist Security Info=False;Extended Properties="Excel 12.0;HDR=Yes"
```


```
Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=DetalleViajes;Extended Properties=
```


When I try the VBA route

```
Sub ChangeExcelExternalToPowerQuery()
'
' ChangeExcelExternalToPowerQuery Macro
'

'
    With ActiveWorkbook.Connections("Excel detalle-paradas-julio").OLEDBConnection
        .BackgroundQuery = False
        .CommandType = xlCmdTableCollection
        .Connection = _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=DetalleViajes;Extended Properties="
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("Excel detalle-paradas-julio")
        .Name = "Excel detalle-paradas-julio"
        .Description = ""
    End With
End Sub
```

 I get:

run time error 1004 application defined or object defined error

*¿How con i preseve my measures and pivot tables but change the source?*

The columns are the same of course in the external table and power query since the power query reads this external table too.

Thanks Everyone! 

PS: My Excel Version is 2016.


----------

