Reresh Not Working Fully

[AllisterB]

I have a workbook (4MB) with several queries in it.

4 Queries query sheets in the work bok and four query other workbooks.

My problem is that after I have saved the workbook containing the query I then do a Data|Resh All.

The final table is about 4k rows and 30 columns.

The Refresh takes between 5-13 mins to run on a i5 Quad with 8GB of RAM.

My problem is that

• the refresh takes too long and
• more often than not the table is only partially refreshed. Sometimes I do repeated Refreshes and sometimes this works. Other times I reopen the workbook and try the Refresh All again - and sometimes this works.

How can I get the Refresh All to do a complete Refresh every time

Thank You

Allister

 

[sandy666]

dealing with mixed data types.

first of all data shouldn't be mixed from source
it's hard to say what you should do because I didn't even see the structure, maybe conditional column, maybe pivot column ... too much "maybe"

I suggest to read more about PowerQuery aka Get&Transform

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help

 

[AllisterB]

first of all data shouldn't be mixed from source
it's hard to say what you should do because I didn't even see the structure, maybe conditional column, maybe pivot column ... too much "maybe"

I suggest to read more about PowerQuery aka Get&Transform

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help

When I do Data Refresh All the Refresh it is almost always only partial. I repeat the 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

Thank You