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

 

[jmacleary]

Hi there. One thing that will slow it considerably is if the other workbooks you are querying are closed. Try having them all open when you do the refreshall.

 

[AllisterB]

Thanks Ken... I omitted to say that the ones that refer to other workbooks have been set not to refresh on a Refresh All.

 

[sandy666]

maybe try Table.Buffer to load data into memory instead of cache
and split Query to calculated query (as connection) and result query (loaded to the sheet)
also uncheck Allow data preview to download in the background can help a little

 

[sandy666]

and update Power Query in Excel to take advantage of caching in cases where a parent node refers to a child node that has already been refreshed (365, 2019)

 

[AllisterB]

maybe try Table.Buffer to load data into memory instead of cache
and split Query to calculated query (as connection) and result query (loaded to the sheet)
also uncheck Allow data preview to download in the background can help a little

Thank you for your suggestion
I am a newbie to PQ so could you please explain how to do what you have suggested in your two posts so I can do what you suggest

Thanks ahain

 

[sandy666]

I don't know structure of your project but I can show example only

• = Table.Buffer(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]), (this is not mandatory just here)
• structure and code ?
• 
• update means update
• and no mixed type of data in column

 

[AllisterB]

I don't know structure of your project but I can show example only

• = Table.Buffer(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]), (this is not mandatory just here)
• structure and code ?
• View attachment 8677
• update means update
• and no mixed type of data in column

Thank you. I will these.

Which of your suggestions do you think will solve the fact that my Refresh All doesn't seem to fully refresh - I ask this as it is my biggest problem.

 

[sandy666]

you need test it
Buffers a table/list/binary in memory, isolating it from external changes during evaluation.
Data Load - stop unnecessary data load in preview
Mixed data type - too much calculation is a bad idea

in short - I don't know which point will be good for Refresh All and show whole data, maybe 8 GB of RAM is not enough, maybe processor is too slow, maybe access to your HD is not fast enough
you need to check all possibilities

 

[AllisterB]

Thank you that is a bit clearer.
I think I might have a number of columns each with mixed data that came in from source report - text and values in the same column. So could you you explain more about dealing with mixed data types.

Thank you