NOTE: First thing to do is to make a copy of your Excel Workbook, before making any changes to your Power Query Source. If you do NOT, and change your Power Query source, your Power Pivot Model could become unusable!

1.      I had an issue where if I changed the Power Query source from a CSV after I made changes to my data in my Data Model, once saving the Excel workbook, and then going back in I would get the following error:

a.      clip_image002

2.      So what I did to try and fix it was to do the following

a.      Get the data into Power Query correctly, so that it was working as expected.

3.      Then went into the Loading of the Power Query data to the Data Model.

a.      Right click and selected Load To:

                                                    i.     clip_image004

b.      Then un ticked both options

                                                    i.     clip_image006

                                                   ii.     You will then get prompted with the following below:

                                                  iii.     clip_image008

                                                 iv.     Click Continue

c.      It will then come back saying that the Load is Disabled:

                                                    i.     clip_image010

d.      Now go back and again Right click and selected Load To:

                                                    i.     clip_image004

e.      This time select the following:

                                                    i.     clip_image012

f.       Now this will load your data into the Data Model.

4.      Next you can open Power Pivot

5.      What I did next was to rename my Sheet which I am hoping will break the association with the old sheet that was in the Data Model.

a.      Renamed it from:

                                                    i.      clip_image014

b.      To:

                                                    i.      clip_image016

6.      I then went and saved my Excel Workbook, closed it. Opened it again and went into Power Pivot.

a.      So far so good.

7.      I then went back and renamed the sheet in the Power Pivot Data model, back to the Original Name

8.      And I got the same error:

a.      clip_image017

9.      So currently the only way I can see to get this to work, is to keep my new Name.

10.   Which means having to re-do or add my fields to the existing reports.

11.   NOTE: Reading further down the error, it talks about Synonyms, so my educated guess is that it has something to do with the Synonym data, not allowing changes to be made within the Data Model.

Leave a Reply

Your email address will not be published. Required fields are marked *