Refreshing data with dependencies

[sminonese]

Hi,

I have the following queries or tables set up

1: Import static CSV file

2: Import dynamic CSV file from a web query

3: Combine 1+2 (static and dynamic)

4: Get column names from 3, match these with a static table to get new column names

5: Source the merged dataset (3), add column names from 4 and clean up

In the end, I will only use query 5 in the report. But I am wondering what exactly happens when I am in report mode and click "refresh". It seems like I have to set "allow refresh" on queries 2, 3 and 5 in order to get updated data. But then it seems it is fetching the data 3 times which is wholly inefficient. Shouldn't it be sufficient to set "allow refresh" on query 5 and it will update its dependencies?

[Anonymous]

This is the best workaround I've found for the issue (have tables with different refresh options in the query editor, and then union them together with DAX on the front end) 

http://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/

[Greg_Deckler · Accepted solution]

To the best of my knowledge, queries do not update their dependencies, would make a nice Idea though. You might consider digging into the Advanced Editor and finding a way to combine your queries into a single query. I have a blog article on this "Power BI: Merge Queries with M". You should be able to do something similar I think.

[sminonese]

Hi again. Can confirm that this worked well and wasn't too hard. I will try to remember to avoid using too many intermediary queries in the future. It seems the file size was also halved by combining all these steps into one query, so probably several benefits from this.

The power query code is now 21 lines. It makes me wish the editor was a bit more code-friendly and could use some highlighting, auto-completion, and support for commenting...

[Greg_Deckler]

That would be a great Idea to post!

[sminonese]

Thank you for the suggestion. I agree it looks promising as a solution, I will give it a try. The only downside is then that I am including a static dataset with a dynamic one and PowerBI will waste some time fetching the static dataset again, or is this handled efficiently?