none
Unable to load the tables in the Power Pivot Window – An Item with the Same Key has already been added RRS feed

  • Question

  • Hi there,

    I recently had the following situation, where I changed the source of my CSV file in Power Query.

    Once I had reloaded the file, it would then not load into Power Pivot. So I disabled the loading from Power Query into Power Pivot. I then enabled the loading to the Data Model. Which then successfully loaded the data into Power Pivot.

    But once I went into Power Pivot, had a look, then saved the Excel file. Once saved I closed the Excel file. I then opened the Excel file again and all the sheets that interact with the Power Pivot data work fine.

    But if I go and open Power Pivot I get the following error: Unable to load the tables in the Power Pivot Window – An Item with the Same Key has already been added.

    This is what I get from the Call Stack

       at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
       at Microsoft.AnalysisServices.Common.LinguisticModeling.SynonymModel.AddSynonymCollection(DataModelingColumn column, SynonymCollection synonyms)
       at Microsoft.AnalysisServices.Common.LinguisticModeling.LinguisticSchemaLoader.DeserializeSynonymModelFromSchema()
       at Microsoft.AnalysisServices.Common.SandboxEditor.LoadLinguisticDesignerState()
       at Microsoft.AnalysisServices.Common.SandboxEditor.set_Sandbox(DataModelingSandbox value)
       at Microsoft.AnalysisServices.XLHost.Modeler.ClientWindow.RefreshClientWindow(String tableName)

    I would assume that the issue is with the synonyms and for some reason, when I disabled the loading of my data into the Power Pivot Model, it did not remove the associations to the actual table and synonyms.

    If I renamed the table in Power Pivot it all works fine. So that was my work around. Fortunately I did have a copy of the Excel workbook before I made this change. So I could then go and add back in all the relevant data to my table.

    Has anyone had this before and know how to fix it?


    http://www.bidn.com/blogs/guavaq


    • Edited by Gilbert QMVP Wednesday, February 11, 2015 9:48 PM
    Wednesday, February 11, 2015 9:48 PM

Answers

All replies

  • Hello guavaq78! Would it be possible for you to share the workbook with us?

    Thursday, February 12, 2015 10:02 AM
  • Hi there

    I can share the work book, if possible to send me an email address. As the workbook size is about 9Mb.

    Thanks

    Gilbert


    http://www.bidn.com/blogs/guavaq

    Thursday, February 12, 2015 10:00 PM
  • Sure. It's yitzhaks at Microsoft dot com.
    Thursday, February 12, 2015 10:04 PM
  • Thanks Gilbert for sending me the workbook. I'll look into this first thing next week.
    Friday, February 13, 2015 2:06 PM
  • what was the resolution here? I am having the same issue.
    Tuesday, May 12, 2015 2:28 PM
  • Hello Excellentz, the team is actively investigating the issue, and I'm hopeful they will deliver a fix in Excel 2016.
    Tuesday, May 12, 2015 8:08 PM
  • What was the outcome to this. I am getting the same error, and the more data I throw at Power Pivot, the more flakey and unstable it seems to get. Please can you guys post the solution to this???

    'If this was helpful, please mark and an answer or as being helpful'

    Tuesday, September 8, 2015 6:23 AM
  • Hi there, the solution was that it is a design error, and should hopefully be resolved in Excel 2016.

    http://www.bidn.com/blogs/guavaq

    Tuesday, September 8, 2015 6:26 AM
  • Seriously Microsoft! Companies don't just jump from one edition as you release them! Bigger companies can take a number of years and skip editions. This error is becoming a show stopper and in a crowded BI and analytics market you cannot afford these kind of errors! Can you not release a patch for Excel 2013 to fix this??? Or at the very least advise a suitable work around!

    'If this was helpful, please mark and an answer or as being helpful'

    Tuesday, September 8, 2015 11:25 PM
  • I'm getting the same error message with Excel 2016.

    Here are the message details:

    ============================

    Error Message:
    ============================

    Object reference not set to an instance of an object.

    ============================
    Call Stack:
    ============================

       at Microsoft.AnalysisServices.Common.SandboxEditor.RefreshSandboxEditor(String tableId)
       at Microsoft.AnalysisServices.XLHost.Modeler.ClientWindow.RefreshClientWindow(String tableName)

    ============================

    Tuesday, January 5, 2016 10:49 PM
  • Digging into it deeper, the problems arise after editing and creating measures using the right-clicks within the PivotTable Fields List in Excel 2016.


    • Edited by Deango Wednesday, January 6, 2016 1:10 AM
    Wednesday, January 6, 2016 1:09 AM
  • We have fixed Linguistic model issues (the issues mentioned in this thread) in latest Excel 2013 and Excel 2016 updates. Those issues prevent loading the model if Duplicate entries exist in Linguistic model 

    Please install latest update

    1) Excel 2016 update - KB 3114297

    OR

    2) Excel 2013 update - KB 3085077

    After you installed the update the issues should be resolved

    Thursday, January 7, 2016 1:21 PM
  • Was the issue resolved after installing the latest update ?
    Sunday, January 10, 2016 2:49 PM
  • I had the same issue today, it is caused because you have 2 pivot tables with the same name in your file.

    I could solve it by getting a list of the PivotTables using vba (https://www.extendoffice.com/documents/excel/5182-excel-list-all-table-names.html), and then identify the ones that were causing the issue, and then change the name.

    But I could do this, because I had a previous version of the same file. After the error appeared, I couldn't fixed it on it directly.

    I hope this can help. :)

    • Proposed as answer by angamont Friday, August 23, 2019 2:13 PM
    Friday, August 23, 2019 2:13 PM