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

  • Question

  • 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

     

     

     

    Monday, March 4, 2019 2:12 PM

Answers

  • Very sorry Frederico, I chatted with the Excel PivotTable experts and they assure me there is no way to reconnect your copied PivotTable with the existing PowerQuery connection in the target workbook.

    He did mention that if you didn't do anything advanced you might be able to use the "all" tab to manually swap the old fields for new ones, but if you have done any formatting, captions, etc those will be lost.

    -jeff

    Wednesday, March 20, 2019 9:58 PM

All replies

  • Hi Federico. Can you share what version of Excel and Power Query you're using?

    Thanks,
    Ehren

    Friday, March 8, 2019 10:43 PM
  • Hi Ehren,

    I'm using Office 365 (16.11231.20164) 32-bit version and Power Query (2.65.5313.181)  32-bit version.

    Waiting your reply
    Regard
    Federico

    Wednesday, March 13, 2019 10:12 AM
  • Very sorry Frederico, I chatted with the Excel PivotTable experts and they assure me there is no way to reconnect your copied PivotTable with the existing PowerQuery connection in the target workbook.

    He did mention that if you didn't do anything advanced you might be able to use the "all" tab to manually swap the old fields for new ones, but if you have done any formatting, captions, etc those will be lost.

    -jeff

    Wednesday, March 20, 2019 9:58 PM
  • Very sorry Frederico, I chatted with the Excel PivotTable experts and they assure me there is no way to reconnect your copied PivotTable with the existing PowerQuery connection in the target workbook.

    He did mention that if you didn't do anything advanced you might be able to use the "all" tab to manually swap the old fields for new ones, but if you have done any formatting, captions, etc those will be lost.

    -jeff

    Thanks for the reply and sorry for the delay of my answer.

    As you said, i realized that there is no way to replicate the model in other workbook. It seems that only "in Cloud" you can carry out this task. But my tool is meant to be offline.

    Thanks again

    Federico



    Tuesday, June 18, 2019 4:03 PM