none
Power query renames query/table on refresh causing Powerview to loose everything from that table RRS feed

  • Question

  • I have Excel workbook with 4 power query queries that are loaded into the data model. If I refresh the one query "Dates" it renames to "Dates1" this means all the PowerView reports are broken as all the items from "Dates" are removed. Even if I rename Dates1 back to Dates I then have to rebuild the reports. Big fail

    Interestingly if I try and rename the Dates query before the refresh it says its read only. So it would appear the refresh sees that its read only and thus creates "Dates1"

    Whats causing this?


    twitter - @simon_sabin blog - http://www.sqlblogcasts.com/blogs/simons SQLBits - Largest SQL Server Conference in Europe and its free

    Tuesday, June 9, 2015 7:53 PM

Answers

  • The reason it destroys your model is because the connection has become read-only, and the query was renamed when it was in this state (something we now prohibit, but didn't in the past).

    If you're simply renaming a table that's already loaded to the model (one that's not read-only), we warn you that the table will be dropped and recreated.

    If you're seeing different behavior on a freshly created workbook, that sounds like a bug.

    Ehren

    Monday, June 15, 2015 11:19 PM
    Owner

All replies

  • Hi Simon,

    This is the infamous read-only connection issue. In addition, at one point in time we allowed people to rename their queries even if their connection was read-only (before we discovered this issue), so that’s most likely what happened with this workbook. Now we do not allow you to rename your query if the connection is read-only, and if it wasn’t read-only, we warn you when you rename a query loaded to the Data Model because, even in this case, refreshing the query after the rename will cause the table to be recreated.

    Sorry you ran into this.

    Ehren

    Monday, June 15, 2015 5:51 PM
    Owner
  • Hi Ehren

    Whilst I appreciate the problem the fact that Power Query deletes the old table and creates a new one with a new name is a serious problem. There is no undo for this and so if you have made changes since the last save you will have lost them as the only way to get back is to open your saved version.

    If you don't do that you will break all your power view reports that referenced the old table name.

    I can confirm that the data connection in question does say that the "some properties can't be changed ...." as per Chris's blog. Although I'm sure the only thing I added was relationships.


    twitter - @simon_sabin blog - http://www.sqlblogcasts.com/blogs/simons SQLBits - Largest SQL Server Conference in Europe and its free

    Monday, June 15, 2015 8:09 PM
  • Hi Simon,

    Is it possible you renamed one of the queries, without doing a refresh, at some point in the past?

    The read-only connection issue is one of the top things we hear about from customers, and we're planning to address it (I just can't say when).

    Ehren

    Monday, June 15, 2015 11:05 PM
    Owner
  • You miss my point. So its read only that's not the problem.

    The problem is that in doing a PQ refresh, without warning, it goes and destroys my powerview reports.

    If it warned me that's one thing. But it doesn't it just goes along destroys the model by dropping and creating a new table with a new name.

    Simon

     

    twitter - @simon_sabin blog - http://www.sqlblogcasts.com/blogs/simons SQLBits - Largest SQL Server Conference in Europe and its free

    Monday, June 15, 2015 11:11 PM
  • The reason it destroys your model is because the connection has become read-only, and the query was renamed when it was in this state (something we now prohibit, but didn't in the past).

    If you're simply renaming a table that's already loaded to the model (one that's not read-only), we warn you that the table will be dropped and recreated.

    If you're seeing different behavior on a freshly created workbook, that sounds like a bug.

    Ehren

    Monday, June 15, 2015 11:19 PM
    Owner