What is the right/most efficient way to update Excel data model?

[cr731]

I have a workbook with Power Queries coming from:

1. An Access database
2. Tables within the workbook

In total, about 8 queries.

The results of the queries are passed to the data model, after which, I have created some pivot tables to summarize the data.

I'm wondering what the right way to refresh all of this is. Is it simply clicking Refresh All in Excel? Will that be redundant in result in duplicate refreshes (once when it refreshes power query, and then refreshing again upon refreshing the pivot tables?).

And will Power Query be smart enough to refresh queries in the right order (such as if one query depends on another, will it refresh the precedent query first?).

Sorry lots of questions, but I'm just trying to wrap my ahead around on to properly use these tools.

Thanks

 

[ImkeF]

Currently you cannot influence the refresh order in Power Query. But MS is working on this.

Normally PQ would detect the necessary order itself so that dependent queries come last. But this doesn't work reliably now. So you really have to check your workbook. For my experience: If it uses the correct order once, it will continue (as Long as you don't change anything).

In workbooks where the refresh order isn't correct I'm building a group with the queries to be refreshed first. You can start the refresh on the group level (check the Group Label - rightclick your mouses - refresh). If you want to avoid double-refreshes of this query you can edit these query properties and deselect "Refresh on Data-Refresh-All (Excel 2013).

 

[happy1001]

@ImkeF, thank you so much for these ideas.

@cr731, it would be great if PQ gives us some systematic manner to define the sequence of the query refresh order. But till we have that solution, I would do the following -
1. Not to use Auto Refresh
2. Define the Exact Sequence in which the Queries should be refreshed, in order to get the correct output.
3. As ImkeF suggested, break it into step wise Groups and then manually refresh them in sequence.
4. If it is easy in a specific situation or scenario, then I might even break up the whole process into 2 or more sheets. So that one sheet can be refreshed in one go and then this processed data gets passed to the second sheet through PQ, where the second level of steps gets done.

Thanks and regards

 

[ImkeF]

@happy1001: Glad you found this useful.

Just realized that the deactivation of "Refresh on Data-Refresh-All" doesn't make any sense here, if these queries are referenced by the other ones. The following queries will trigger a refresh anyway. So: Refresh first - yes, but you cannot prevent the second refresh.

 

[happy1001]

@happy1001: Glad you found this useful.

Just realized that the deactivation of "Refresh on Data-Refresh-All" doesn't make any sense here, if these queries are referenced by the other ones. The following queries will trigger a refresh anyway. So: Refresh first - yes, but you cannot prevent the second refresh.

Thanks for that correction ImkeF. I am a newbie in Power-bi stuff. Just learning it slowly on a daily basis. It has got real good features, but I must say that it is a bit daunting for the newbies in the initial days. As there are quite a lot of things that must be kept in the mind, so that the Data Models do not break up in the future. But it is great that helpful members like you are available on the Help Forums, so all such issues gets solved, one by one.

Thanks and my Best Regards