Power Pivot - Change Power Pivot Connection to Power Query Connection

martinequilibrium

New Member
Joined
Feb 26, 2016
Messages
13
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:

1599095851423.png


1599095573877.png


When trying to edit to get the query:

1599097568298.png

1599097581511.png

1599097740348.png


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)

1599095670517.png


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

1599095808001.png

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

1599095894050.png


Inspecting the properties
1599095929020.png


VBA Method

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

1599098050791.png



VBA Code:
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"

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


When I try the VBA route
VBA Code:
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.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Watch MrExcel Video

Forum statistics

Threads
1,118,126
Messages
5,570,331
Members
412,319
Latest member
akshat1231
Top