Partial Refresh

When I do Data Refresh All the Refresh (while disproportionally long (10min for a few tables of 4k rows 30 Columns) is almost always only partial. I repaet teh refreshes and after say 4 get a full refresh.
I suspect that it may have to do with dependencies - or it may be something else.

My workbook is setup as follows:

I have in a workbook Query1 that queries Table A in the same workbook and outputs to a Table B.

SheetA has cells that refer to Table B via SUMIFS formulae.

Query2 queries SheetA and outputs to Table C

Table C is then Merged with other data in Query 3 and output to Table D.

Have a got dependencies that cause a partial refresh - If so, how do I solve this
If Not what else might be causing the partial refresh

1 Like

Hi @AllisterB

I’m wondering why you go back and forth to the workbook, can you elaborate with a sample?

Consider Group by as an alternative to SUMIFS and only reference output queries instead of output tables in the workbook.

Thank You Melissa

Perhaps I was not clear re the workbook. All Queries that get Refreshed on a Refresh All are in teh one workbook - so there is no going back and forward. What does happen though is I use the output of a query to populate cell on a sheet and then that cell (with others) is queried by another Query.

I trust this clarifies it.

I am interested in the concept of Group By and of referencing a query - cab you elaborate and/or direct me to some info on this


I’d be glad to take a look, just mock something up so we can work through it.

Thanks ZMelissa

This may take me a couple of days due to other demands.

In the meantim=me do you have some info re Group By and refrencing queries instead of tables.


Okay don’t know if it makes sense without knowing more on your specific scenario but I’ll give it a try…

Attached a workbook that contains 3 tables in which we want to use the SUMIF from another table.
For SUMIFS you can take 2 basic approaches in Power Query:

  1. Merge in combination with List.Sum()
  2. or Group by

The queries myTable3 and. reference Output1 = Group by Store both have a Store Total value but based on a different approche… Note that in the “Applies Steps” via the Gear wheels you can see the details.

I hope this is helpful, if not please try and work up a small sample.
eDNA - Partial Refresh sample.xlsx (96.1 KB)

Thanks for posting your question @AllisterB. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

I have solved this by getting a vba script to stage the fresh of queries. I refresh one query before I refresh all and it works fine.

Thanks for your help Melissa