Partial Refresh

Power Query
1

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
2

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.

3

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

Thanks

4

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

5

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.

Thanks

6

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)

7

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.

8

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