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?


    Monday, June 25, 2018 10:25 AM

Answers

All replies

  • Why not load the tables to different sheets? Better still, don’t load them to Excel at all, but Instead load them to the data model

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Monday, June 25, 2018 11:26 AM
  • Hi Matt,

    Thanks for your response, apologies for the delay in following up, for some reason I didn't get notified that you'd responded and I've subsequently been away.

    The reason I am loading the tables into Excel is 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), also 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, 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.  Is there a way to get this working in one sheet ?

    Best regards

    Richard


    Thursday, September 6, 2018 11:44 AM
  • Hi Richard,

    I had this problem too- my solution was to right click on the table, click table > external data properties... > and then selected "Overwrite existing cells with new data, clear unused cells" under "If the number of rows in the data range changes upon refresh:"


    Sneaky little spot to hide it in, but it worked!

    • Proposed as answer by remypaige Friday, October 26, 2018 5:58 PM
    Friday, October 26, 2018 5:58 PM