cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
danextian
Community Champion
Community Champion

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

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!
"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
mozuliov
New Member

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

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.

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

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
Community Champion
Community Champion

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!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
vanessafvg
Super User
Super User

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

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.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

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

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors