Another wonderful video, thank you for explaining in detail. It was confusing why on some files in spite of clicking refresh all button still I had to click once more to have everything updated.
Hi Celia, Have question. I work with lage data sources. Each time when I am making modification in power query step query is reffreshing each time and it take really long time. Is i possible to stop/disable this auto refreshing?
Aleksandrs, try the following option - from Excel do: tab Data > Get Data > Query Options Under Global on the left, select Data Load. Check "Fast Data Load" and see if this helps with what you need. The last time I tried this, I didn't like it because it puts a yellow alert sign on the side of the query ad if there was an error, but it is just because the data preview was not refreshed. I am not sure if Microsoft already changed that behavior. Please remember that this is a global setting and it will apply to all the workbooks. Under the Current Workbook section, try unchecking "Allow Data preview to download in the background." I haven't tested this.
@@CeliaAlvesSolveExcel Unfortunately all rurns in to the error and was not able to do any adjustments and creations on steps, so this solution did not solve problem. But thanks any way ;)
Hello Ma'am, Every time I refresh the power query, the query table gets highlighted. I just want to get rid of this and stay on the sheet wherever I was just before the refresh. please help me with this. Regards.
Anish, I think this is the default behavior and I don't think there is a setting to disable that behavior. If you can use VBA in your file, you could create a macro with like this: Sub UpdateData With ThisWorkbook .RefreshAll .Sheets("Sheet Name").Activate End With End Sub Replace Sheet Name with the name of your sheet where you want to be after the query refresh. Put a button in that sheet to run the macro and use that button to refresh the query. Note: RefreshAll with refresh all the queries and pivot tables you have in your file. If you just want to refresh that one query, record a macro while refreshing that query. Then, replace the first line of code in the macro above with the code you get on the macro recorded. I hope this helps.
@@CeliaAlvesSolveExcel Thank you so much Ma'am for the quick response.... I would like to tell you that the probable solution that you had given, was one of the many tries i have already done, but the problem is still intact. What happens is that the code doesn't wait for the table to load, rather keeps running and reaches to "End sub" while the table is yet to load. Which is, why "Sheets(Sheet name). Activate" statement doesn't even get executed.
@@anishjain1874 that problem should get solved if you uncheck in the query properties "enable background refresh" has explained in this video. Have you tried that? If you have more than one query, do that to all queries, including the helper queries created for queries connecting to folders. Let me know if this works.
I cannot refresh at all in my pc, it loads the table when I create it from other excel workbook but if I try to refresh it wont aloud it, cannot connet to origin =( help excel 2016
Jesus, I recommend you ask for help in forums related to Excel where you can show your file and you might find people available to help you. Try MrExcel.com.
How to update original file while power query refresh and transfer data to other file It's placing lock on original file so not able to update original file
Excellent topic Celia! I have the double refresh issue in a few of my workbooks but had no idea why it did that. Such a great fix once you know it!
There you go, Gráinne! Glad that it helped!
Another wonderful video, thank you for explaining in detail. It was confusing why on some files in spite of clicking refresh all button still I had to click once more to have everything updated.
@Ahad Alavi, I am glad that the mystery is now solved. ;-)
I just simply love your videos, clear and simple. I really like learning power query with you.
Thank you, Diego. I am glad my videos are helpful.
Excellent. I agree with you: 99% of my PQ files have pivot tables included.
I'm doing two refreshes all time mechanically
This tip should now solve that issue. :)
Excellent tutorial. Thanks Celia for your kind sharing 👍👍
My pleasure, Kebin! Glad that you found it useful.
Thanks for this precious information
You're very welcome! :)
Great! Thanks Celia. Thumbs up!!
Thank you, @Wayne Edmondson! ;-)
Hi Celia, Have question. I work with lage data sources. Each time when I am making modification in power query step query is reffreshing each time and it take really long time. Is i possible to stop/disable this auto refreshing?
Aleksandrs, try the following option - from Excel do: tab Data > Get Data > Query Options
Under Global on the left, select Data Load.
Check "Fast Data Load" and see if this helps with what you need.
The last time I tried this, I didn't like it because it puts a yellow alert sign on the side of the query ad if there was an error, but it is just because the data preview was not refreshed. I am not sure if Microsoft already changed that behavior.
Please remember that this is a global setting and it will apply to all the workbooks.
Under the Current Workbook section, try unchecking "Allow Data preview to download in the background."
I haven't tested this.
Also, please check:
www.excelinppc.com/recommended-power-query-settings/
www.sumproduct.com/blog/article/power-query-blogs/power-query-querying-query-options
@@CeliaAlvesSolveExcel Thank You Celia so much, I am excited to test it. Fingers crossed that it will work. You are wesome.
Let's not celebrate yet. :) Please test it and let us know about your findings.
@@CeliaAlvesSolveExcel Unfortunately all rurns in to the error and was not able to do any adjustments and creations on steps, so this solution did not solve problem. But thanks any way ;)
Thank you!!
You're very welcome! :)
Can you show us how to use “definition” tabt within query properties
Pro tip : you can watch movies at flixzone. I've been using them for watching loads of movies lately.
@Sincere Harrison yup, been watching on Flixzone for since november myself :)
Hello Ma'am, Every time I refresh the power query, the query table gets highlighted. I just want to get rid of this and stay on the sheet wherever I was just before the refresh. please help me with this.
Regards.
Anish, I think this is the default behavior and I don't think there is a setting to disable that behavior.
If you can use VBA in your file, you could create a macro with like this:
Sub UpdateData
With ThisWorkbook
.RefreshAll
.Sheets("Sheet Name").Activate
End With
End Sub
Replace Sheet Name with the name of your sheet where you want to be after the query refresh.
Put a button in that sheet to run the macro and use that button to refresh the query.
Note: RefreshAll with refresh all the queries and pivot tables you have in your file. If you just want to refresh that one query, record a macro while refreshing that query. Then, replace the first line of code in the macro above with the code you get on the macro recorded.
I hope this helps.
@@CeliaAlvesSolveExcel Thank you so much Ma'am for the quick response.... I would like to tell you that the probable solution that you had given, was one of the many tries i have already done, but the problem is still intact. What happens is that the code doesn't wait for the table to load, rather keeps running and reaches to "End sub" while the table is yet to load. Which is, why "Sheets(Sheet name). Activate" statement doesn't even get executed.
@@anishjain1874 that problem should get solved if you uncheck in the query properties "enable background refresh" has explained in this video. Have you tried that? If you have more than one query, do that to all queries, including the helper queries created for queries connecting to folders. Let me know if this works.
@@CeliaAlvesSolveExcel okay ma'am... I'll try this and will definitely let you know whether it works or not.
@@CeliaAlvesSolveExcel Sorry to inform you, but it didn't work. Still the table is getting highlighted and selected.
how to refresh power query while workbook protected
As far as I know, you can't. You need to unprotect the sheet, refresh and then protect it again. This process can be done manually or using VBA.
I cannot refresh at all in my pc, it loads the table when I create it from other excel workbook but if I try to refresh it wont aloud it, cannot connet to origin =( help excel 2016
Jesus, I recommend you ask for help in forums related to Excel where you can show your file and you might find people available to help you. Try MrExcel.com.
Hi Celia! thanks for great video. I would like to ask if background refresh works when the file is not open, is it possible?
Hi, Joann. Do you mean having your queries in your Excel file updating with the Excel file closed?
@@CeliaAlvesSolveExcel yes, but i meant if is it possible to carry refresh on without opening excel file
@@Joann7000i have the same exact issue. Is there a solution
How to update original file while power query refresh and transfer data to other file
It's placing lock on original file so not able to update original file
Not sure I understand your question. Do you get an error message in Excel or Power Query for this issue?