Can the Power Query Model be connected to an existing and identical one?

[Fest_BI]

Hi All,

I have an Excel File with a large model created in PowerQuery. In addiction the model is connected to PowerPivot that allows you to use pivots in the file. 

When another version of the file is reliesed i don't want to lose all the custom pivot created. The Problem comes when i try to copy a sheet from one Excel to another that it's identical in the structrure (With the same model...).

The copied of the sheet regenerates another model identical to a copy of the original.

For first the Connections are duplicated by adding the (n) to the end of the name ( e.g. Workbooks("filename").Connections("Query - product (1)") ) because excel find the same Query name in the other file. 

The Query in PowerQuery are duplicated (e.g. "Query - product (1)") for the same reason.

In Power Pivot the model is also duplicated.

Follow the code generated when i copy the sheet to the other Workbook:

Sub Macro1()

Sheets("OldSheetName").Select
    
Workbooks("NewFileName").Connections.Add2 _
        "Query - Product (1)", _
        "Description", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Product (1)"";Extended Properties=""""" _
        , "SELECT * FROM [Product (1)]", 2

'Same Code for the others connections 

Sheets("OldSheetName").Copy After:=Workbooks( _
"NameFile_NEW.xlsm").Sheets(13)

I tried to delete the connections first and then replicate the same connection.

In this way i lose the Pivot related at those connections.

Follow the code used:

Sub Macro1()

Sheets("SheetName").Select

Workbooks("FileName").Connections("Query - Product").Delete 
   
Workbooks("FileName").Connections.Add2 _
        "Query - Product", _
        "Description", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Product"";Extended Properties=""""" _
        , "SELECT * FROM [Product]", 2

'Same Code for the others connections 

Sheets("SheetName").Copy After:=Workbooks( _
"FileName_NEW.xlsm").Sheets(13)

Someone have any proposal for fix the problem? Can the VBA Code be used to link the new model to the existing one?

Thanks in advance

Federico

[v-cherch-msft]

Hi @Fest_BI 

I would suggest you go to POWER QUERY formus for further help.

Regards,

Cherie

[Fest_BI]

Hi @v-cherch-msft ,

Thanks for the advice. I just posted the same question in the Power Query Forum. I hope someone interests the topic for find a solution helpful for me and other users of the tool.

Best Regard,

Federico