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.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Forum statistics

Threads
1,223,214
Messages
6,170,774
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top