none
Power Query refresh won't work with multiple tables in worksheet RRS feed

  • Question

  • When refreshing a query loaded to a worksheet containing multiple queries / tables, I get the error “This won’t work because it would move cells in a table on your worksheet”.  This error occurs even when I have set Table Properties to prevent other tables being moved and created space in the sheet to prevent other tables being overwritten as a result of the refresh.

    The only way I have found to workaround the problem is to duplicate the query within the worksheet, update references within the sheet so they refer to the new table then delete the original table.   This is time-consuming and eliminates much of the benefit of PowerQuery's ability to bring in new data automatically.

    Is there a better solution to refreshing the data that avoids the need to recreate the query?

    The reasons I am loading the tables into Excel at all are that: I need to do some relatively complex calculations for each of the rows of data in each of the tables (which I don't believe can be performed within the data model); and, that the results of the calculations for the rows in one table are referenced in the calculations for the rows of another table.  Given there are a large number of such references between tables, I figured that it would be easier to reference the data between tables if the tables were in the same sheet, and I understood from the options available under the Table Tools > Design > Properties dialog that Excel could accommodate changes in the number of rows in a table in a variety of ways.
    Wednesday, September 26, 2018 7:08 PM

Answers

  • I have the same issues, in my case, the tables are side by side.  The refresh worked for a while, until I modified some of queries.  In most cases, I was removing columns from the query.  The table didn't want to resize itself. 

    I did just find a solution.  On one of Ken Puls blogs (https://www.excelguru.ca/blog/2016/08/23/fix-excel-formulas-dont-update-in-power-query-tables/), he mentioned an issue with refreshing tables and formulas.  In the External Data Properties, uncheck the "Preserve column sort/filter/layout" and try again.

    I've only tried this once, after a LOT of reconfiguring. Can this issue be looked at more closely please?  There are tables where I want to keep the column sort / filter / layout on refresh.


    • Edited by JVD_123 Monday, December 17, 2018 4:24 AM
    • Marked as answer by rsquire Thursday, January 31, 2019 8:24 AM
    Monday, December 17, 2018 3:36 AM
  • Hi JVD_123

    I've now had the chance to try Ken Puls' fix proposed in your post above, and it seems to avoid the error - at least the error hasn't recurred since I've used this approach.  I am using the default sort-order in the underlying query in every case so not being able to preserve the sort order in the sheet is not an issue for me.  Many thanks for the suggestion !

    Best regards

    Richard

    • Marked as answer by rsquire Thursday, January 31, 2019 8:33 AM
    Thursday, January 31, 2019 8:28 AM

All replies

  • Hi rsquire. By default, each query should get loaded to its own new sheet. Is that not the behavior you're seeing?

    Ehren

    Monday, October 1, 2018 7:28 PM
    Owner
  • I'm choosing to load multiple queries to the same sheet to simplify the referencing as described in the original post.  The sheet is laid out so that each set of query results starts 3 rows below the end of the preceding one.  All the queries are set-up so their Design>Properties are set to Insert entire rows for new data, clear unused cells.

    Does that address the question in your response?

    Wednesday, October 3, 2018 2:00 PM
  • Hi Richard. This seems to be an Excel behavior that is independent of Power Query. I've contacted someone from the Excel team who can hopefully clarify things.

    Ehren

    Thursday, October 4, 2018 5:57 PM
    Owner
  • Thanks Ehren,

    FYI when I originally raised the query a few months back I raised it on one of the Excel forums (Excel IT Pro Discussions: https://social.technet.microsoft.com/Forums/office/en-US/03546835-ff17-4257-9055-13f22db36b4a/power-query-refresh-wont-work-with-multiple-tables-in-worksheet?forum=excel) and was advised to raise the query on the Power Query forum.  Looks like it might need both groups to be involved in the resolution.

    Best regards

    Richard

    Friday, October 5, 2018 9:41 AM
  • Hi Ehren, just wanted to check whether there is any update on this ?

    Best regards
    Richard

    Wednesday, October 17, 2018 8:48 AM
  • Hi there,

    Can you please share your Excel version?

    Guy

    - Excel Team


    Guy Hunkin - Excel Team

    Wednesday, October 17, 2018 3:56 PM
  • Yes, although I'll need to remove sensitive data from the sheet first.  Also, is there a way to share the sheet with the support team without putting it in the public domain?

    Best regards

    Richard

    Wednesday, October 17, 2018 9:40 PM
  • Hi Richard,

    For now I think Guy was just requesting the exact version of Excel you're running, not a sample file (although a sample file might be helpful).

    Ehren

    Wednesday, October 17, 2018 10:18 PM
    Owner
  • Hi Ehren

    Thanks for clarifying !

    Guy

    Sorry for the misunderstanding !  My current Excel version is 1809, Build 10827.20150 (MS Office 365).

    It would take quite a while to remove all the sensitive data before sending a sample file, so I'll hold on that for now but please let me know if required to progress the resolution.

    Best regards

    Richard 

    Friday, October 19, 2018 6:24 AM
  • Hi Richard,

    Thank you for reporting this, can you please share some more details with me? I can't seem to be able to reproduce this -

    1. Do you have tables laid out one on top of the other only, or also side by side in the same sheet (dashboard style layout)?

    2. Are all the table sizes fixed upon refresh or their column/row count expected to change?

    3. Does refreshing the queries one by one work?

    4. Is making the spaces between the tables be more than 3 rows / columns mitigate the issue?

    5. Are all the queries loaded to the Data Model? some? none?

    A workbook without sensitive information would be very useful. You can also also send me your session id (from a session with this error) so I'll see if there are any errors associated to it, you should be able to find it under Excel -> File -> Account->About Excel -> Session Id (at the top)

    Thank you,
    Ron (Excel).


    • Edited by Ron Ben Ishay Tuesday, October 30, 2018 9:27 PM emphasis
    Tuesday, October 30, 2018 9:23 PM
  • Hi Ron,

    Taking your questions in turn:

    1.  I have tables side by side as well as on top of one another.  There are 2 columns of tables, the tables in the left-hand column are all Power Query results sets, whilst the tables in the right-hand column are all native excel tables, in which the cell contents are mostly calculations based on data from other tables within the sheet.

    2.  The column counts of the tables are expected to be fixed once development of the sheet is complete, although as the sheet is still under development, the column counts still have to change from time to time.  The row counts are expected to change based on input data contained in other sheets - essentially the number of rows in each table is determined by the results of the Power Query queries that define the tables within the left-hand column of tables within the sheet. 

    3.  Sometimes, refreshing the tables one by one following a change in table size has worked and the error has not occurred.  Other times this approach has not worked and, as a result, I have needed to delete and recreate a number of the tables in order to avoid the error.

    4.  This approach has not worked by itself to mitigate the error once it has arisen - I have needed to delete and recreate tables to do so.

    5.  All the query-based tables are loaded to the data model - the query results from the data model are loaded to the sheet, one above the other.  One of the query-based tables is slightly different from the others in that it is named in External Data Properties as a <Complex Connection> rather than simply referencing the underlying data model query.

    Assuming the above doesn't give sufficient into to resolve or recreate the error, I'll spend some time to create a desensitised version of the workbook; this make take me a few days. 

    Best regards

    Richard

    Tuesday, October 30, 2018 11:14 PM
  • Thank you Richard, this is helpful.

    If you can send us a feedback using Excel's feedback tool (top right corner of the window) when this occurs again, it would help us in trying to analyze if something went wrong in the underlining session. Alternatively, you can send us your session id as I've mentioned before, but it is best to submit it via the feedback tool.

    Ron.


    • Edited by Ron Ben Ishay Wednesday, October 31, 2018 2:21 PM emphasis
    Wednesday, October 31, 2018 12:58 PM
  • Hi Ron, thanks, will do. 

    It will likely be a couple of weeks before I pick up development of the sheet again, so it may be a while before the problem recurs.  When it does however, I'll be sure to send you feedback as described for diagnosis.  I'll also flag the recurrence on this thread.

    Best regards
    Richard 

    Thursday, November 1, 2018 8:58 AM
  • Hi Ron

    Just to let you know that the error has recurred and I have submitted Feedback on the error via the Excel Feedback function as requested.  The session ID is A4E91B78-AC29-4354-8FE8-D828F264240F.

     

    Please let me know if you need more information to proceed with the investigation.

    Best regards

    Richard


    Tuesday, November 27, 2018 12:54 PM
  • Hi

    Just wanted to check if there has been progress on resolving this issue since it just recurred today, for another query in the same worksheet.

    Best regards

    Richard

    Tuesday, December 11, 2018 12:02 PM
  • Hi Richard,

    I tried using your description to reproduce the error you are getting, and I think the issue might be caused by the Insert entire rows for new data, clear unused cells property that you mentioned.

    I was able to reproduce the error with the layout in the below image.

    Assuming there are 3 cells separating Table1 and Table2. If a refresh action causes Table1 to increase its size in 4 rows, according to the ‘Insert entire row’ setting, Excel needs to add to this sheet an entire row before Table2, which causes a conflict with Table3.
    This will cause the refresh to fail and prompt the error message you are getting.

    If this is not the issue you are seeing, and if it’s possible, please share the layout of your document and which table fails to refresh. If you prefer not to share it here, you can send it to tapeleg@microsoft.com.

    Thanks,
    Tal - Excel team

    Table1

    Table3

    1

    1

    2

    2

    4

    4

    5

    5

     

    5

     

    5

     

    8

    Table2

    1

    1

    2

    2

    4

    5

    Thursday, December 13, 2018 1:15 PM
  • Hi Tal

    Many thanks for taking a look at this.

    In fact, the layout of my sheet differs somewhat from this, in that the tables to the right span exactly the same rows as the tables to the left.  i.e. In your illustration, table 3 would have 4 rows so that it ends on the same row as table 1.  In addition, below table 3, there would be a table 4 that starts and finishes on the same rows as table 2. 

    Does this help ?  I can't paste my sheet here as it's too large and, whilst I can send the file in it would require desensitising the data (which I can do if needed, but would take time).

    Best regards
    Richard


    Sunday, December 16, 2018 11:35 PM
  • I have the same issues, in my case, the tables are side by side.  The refresh worked for a while, until I modified some of queries.  In most cases, I was removing columns from the query.  The table didn't want to resize itself. 

    I did just find a solution.  On one of Ken Puls blogs (https://www.excelguru.ca/blog/2016/08/23/fix-excel-formulas-dont-update-in-power-query-tables/), he mentioned an issue with refreshing tables and formulas.  In the External Data Properties, uncheck the "Preserve column sort/filter/layout" and try again.

    I've only tried this once, after a LOT of reconfiguring. Can this issue be looked at more closely please?  There are tables where I want to keep the column sort / filter / layout on refresh.


    • Edited by JVD_123 Monday, December 17, 2018 4:24 AM
    • Marked as answer by rsquire Thursday, January 31, 2019 8:24 AM
    Monday, December 17, 2018 3:36 AM
  • Thanks JVD_123, I'll give this a try next time the problem occurs.  My only solution at the moment is to create a duplicate copy of the table and delete the original - requiring a lot of reconnecting references and formulas, which is both time-consuming and error-prone.  I'll update about the results on this thread once I've had a chance to try it out.

    Best regards
    Richard

     
    Monday, December 17, 2018 8:42 AM
  • Hi Richard,

    I investigated the issue and I think I found a possible workaround to the issue.
    Assuming this is the layout of the document:

    Table1

    Table3

    1

    1

    2

    2

    3

    3

    Table2

    Table4

    1

    1


    My suggestion will be to refresh your queries in the following order (manually or via vba): Table1, Table2, Table3, Table4.

    In addition, I have forwarded the issue to the relevant team in Microsoft. We will keep you posted when this issue gets resolved.

    Thanks,
    Tal

    • Proposed as answer by Guy Hunkin Sunday, January 6, 2019 3:01 PM
    • Unproposed as answer by rsquire Sunday, January 6, 2019 8:28 PM
    Monday, December 31, 2018 1:09 PM
  • Hello,

    I have been struggling with a similar issue for several months. I have 4 different power query tables of differing row counts but the same number of columns on one worksheet and simply want to add a sum column to the left of each of them that refreshes as the data changes. As the row counts change almost daily as the data refreshes, manually entering sum functions is impractical. I can add a column to the top table, but when I edit the second, I get the error described above. 

    I then print the worksheet with the different tables on it for daily distribution, so having the tables on the same worksheet is also important, if possible. 

    Thanks,

    -Mike


    • Edited by mdstrauch24 Wednesday, January 2, 2019 7:51 PM
    Wednesday, January 2, 2019 7:50 PM
  • Hi Tal

    Many thanks for your post and apologies for the delayed response, I'm just back onto this today after a break over the holidays.

    In fact, the refresh order you propose is the one I have been using.  The issue arises after refreshing Table 1; i.e. before I have the chance to move on to refresh the other tables.  So unfortunately this proposal doesn't address the issue. 

    Please let me know if you get any updates from the Microsoft team to whom you referred the issue.

    Thanks again for your help.

    Best regards

    Richard

    Sunday, January 6, 2019 8:28 PM
  • Mike,

    Sorry to hear you have a similar issue to the one I've been experiencing.  The workaround I currently use when the error occurs when adding a column to one of the tables is to delete and recreate the table causing the error, including the additional column(s) in the recreated version of the table.   Whilst this workaround is very time consuming and so impractical if it has to be done too often, it has worked for me when I've been unable to make progress otherwise.

    Best regards

    Richard

    Sunday, January 6, 2019 8:39 PM
  • Hi Richard,

    I'm sorry to hear this workaround didn't work for your case. In my example, it managed to avoid this error.
    If you'd like, next time this error occurs, you can share the layout of your worksheet before the refresh (which tables exist and what are their sizes), which table fails to refresh and its expected size after refresh.
    This way I can ensure that the issue that I've forwarded will also resolve the issue that you are experiencing. If you prefer, you can send it by email.

    Thanks,
    Tal

    Tuesday, January 8, 2019 5:27 PM
  • Hi Mike,

    Can you please share some more information about the issue you are experiencing?
    What is the layout of your worksheet?
    How are you adding the sum columns to the tables (in Excel, in Power Query editor)?
    Are you getting the same error message (“This won’t work because it would move cells in a table on your worksheet”) or a different one? Are you getting this error during refresh?

    Thanks,
    Tal

    Tuesday, January 8, 2019 5:44 PM
  • Hi JVD_123

    I've now had the chance to try Ken Puls' fix proposed in your post above, and it seems to avoid the error - at least the error hasn't recurred since I've used this approach.  I am using the default sort-order in the underlying query in every case so not being able to preserve the sort order in the sheet is not an issue for me.  Many thanks for the suggestion !

    Best regards

    Richard

    • Marked as answer by rsquire Thursday, January 31, 2019 8:33 AM
    Thursday, January 31, 2019 8:28 AM
  • Tal

    Just to close this out from my perspective, the fix proposed by JVD_123 in the chain above works for me.  Note however that JVD_123 still has an issue in that the fix doesn't preserve custom sort orders.

    All the best

    Richard

    Thursday, January 31, 2019 8:31 AM