cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User II
Super User II

Exclude from refresh certain files for a folder data source

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"?

 

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Exclude from refresh certain files for a folder data source

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






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

9 REPLIES 9
Highlighted
Super User II
Super User II

Re: Exclude from refresh certain files for a folder data source

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User II
Super User II

Re: Exclude from refresh certain files for a folder data source

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. 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
Highlighted
Super User II
Super User II

Re: Exclude from refresh certain files for a folder data source

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.  






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
Highlighted
Super User II
Super User II

Re: Exclude from refresh certain files for a folder data source

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Microsoft
Microsoft

Re: Exclude from refresh certain files for a folder data source

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'. 

1.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Super User II
Super User II

Re: Exclude from refresh certain files for a folder data source

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






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

Helper III
Helper III

Re: Exclude from refresh certain files for a folder data source

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

Highlighted
Super User II
Super User II

Re: Exclude from refresh certain files for a folder data source

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. 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
Highlighted
New Member

Re: Exclude from refresh certain files for a folder data source

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...

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors