Exclude from refresh certain files for a folder data source

[danextian]

Hi All,

I thought that it would be simpler to just connect to a folder instead of connecting to multiple files one by one and then appending them to a new query. It worked fine but I realized that there are some files that no longer needs to be refresh because they are not updated but are still needed to be loaded to the data model.

Is there a way to exclude certain files from refresh when the "get data" option selected was "folder"?

[mozuliov]

Hi, danextian,

Here is what I came with for Excel files... When you put files in the folder you intend to read from you need to define names for data ranges you want to read data from. You can come with the same name in each file (e.g. "Data"). In your query you include all files, add column with Add Column->=Excel.Workbook([Content]) and expand Names and Data fields. Among Names you will be able to select just "Data" areas to read for your query. Obviously, if you work with CSV or Text files that solution would not work since those do not have data ranges...

[v-yulgu-msft]

Hi @danextian,

In Power BI desktop, we can enable/disable a query to be refreshed when clicking on the Refresh button in the top ribbon in query edit mode.

Right click one query and select the last option 'Properties'. 

In your scenario, when you load multiple files into a single query, it is not possible to exclude some certain files when refreshing data source. The only workaround is to load files into Power BI as different queries, then, append them into a new query according to your need.

Best regards,
Yuliana Gu

[danextian · Accepted solution]

Well, I ended  up separating my data sources into current and old folders. Thanks for this.

[a68tbird]

Hi danextian,

  I am currently having this same issue, and came across your solution.  I guess the two folder system seems to be the only solution. It's cumbersome that the files have to be moved around manually everytime. I'd have to save the Excel email attachments to a "current" folder, run the PowerBI query, and then move those Excel files to an "archive" folder.  Have you figured out any elegant solutions to this since you posted this back in May?

Thanks

[danextian]

hi @a68tbird

I thought that was the solution but realized that once I have created a new query by appending the old query to the new one, Power BI will still include the  old one in the refresh even I've set the  original 'old' query not to be loaded.  I guess a more elegant solution is to load both query separately to the designer (where 'old' is set not included in refresh) and combine them using UNION function in DAX.  Just a warning: UNION combines columns by position in their respective table unlike the append function in M which is by column name. 

[vanessafvg]

in power query you should be able to right click on the table and untick include in report refresh

[danextian]

Those files are quite big and I want to save time from refreshing them since their data in the model don't need to be updated.  

[vanessafvg]

so what you saying is you combine data?  if you combine data into one table from different files i dont think there is a way to remove the refresh from it individually.

The only thing you could do is create a new folder for data you dont refresh and then append it to the refreshable files.

[danextian]

Hi @vanessafvg,

Doing that would make sense if I connected to a file directly. But I am connecting to a folder with multiple files. Some of the files, like past years' data, no longer need to be refreshed as they will no longer be updated. I want to exclude them from the refresh. Just those files.