Refresh of Table

I have a Query that loads to a table as is part of a Data Model.

Often whe I refresh All the Table does not refresh (the data is unchanged even though teh source is changed.

if I close the workbook and reopen it and then go Refresh All or just Refresh the Query that loads to the Table the table refreshes. The Properties for the Query Include use Refresh this connection on Refresh All and Load Fast Data.

Any ideas how to get it to refresh using Refresh All everytime ?

Thanks
Allister

Right click on the table/query and check on “Include in report refresh”. It should refresh this query every time you click on refresh.

1 Like

Thank YOU

I forgot to mention that I am doing this with PQ within Excel 365.

I have attached the properties for the Query as it was when I posted the topic.

What do I do now ?

Thank You

Hi @AllisterB,

Oh, I am not sure about Excel 365 refresh option. But please have a look on below link, it might help you to understand your problem.

Hi

Is it important taht the Query is on teh Current Woprkbook - it queries sheets in teh workbook begining with depot_

The Refresh happens ok after I save the file -= so is it looking to the saved version rather trhan that in working memory.

Is there a fix ?

Excerpt from teh query
Source = Excel.Workbook(File.Contents("S:\ABC National Office\Accounting\A Documents\Projects\001_Revamp NO Budget\ACB Revamp Recontruct ver3.xlsx"), null, true),

Thank YOu

Hi,

Please check following excerpt from the article that I mentioned earlier. It might help you.

The watch outs!

OK, it’s now time to consider the things which might catch us out.

Closed vs. Open files

The location where the source data is stored is important for data refresh.

When the data is stored in an external file, be it CSV, text or even an Excel Workbook, it is the last saved version of the file which is loaded into Power Query. If the external file is open, the changes will not be included in the refresh until it has been saved.

Where the data and query are in the same workbook, all changes are incorporated even if the file has not yet been saved.

Hi I note eth comments made by Hafiz. The workbook I am querying on is teh workbook atht contains teh Query. Refresh still only works after I have saved the file.

Is there a command that instructs Excel to use eth (as yet) unsaved workbook file

Could it be Excel.CurrentWorkbook() - and if so what would the code be in my instance (a current code is above)

If Excel.CurrentWorkbook() does not instruct Excel to look at the Current unsaved file - what would? :smiley:

Thank You

Allister

Just adding a couple of points here.

You need to have the workbook saved for Power BI to pick up any new data. I don’t think there is any other option.

Potentially if you need some auto saving done you could look at vba macros to do this.

Thanks
Sam

1 Like

E- DNA guidelines that PBI requires the workbook to be saved, the same is true with Power Query within Excel. There’s neither dax nor vba that can change that particular behavior.

That being said, I have an idea that may be a viable workaround for you: In the top left corner of your workbook, there should be a switch that says “AutoSave”. Turning that option on will keep your workbook changes saved as you continue working (I don’t remember the save interval off-hand, sorry). Now that your workbook is continually being saved, your refreshes should pull in the newly entered/saved data.

I hope this helps