Forum Discussion
Help please, Filtered File Folder import still evaluates contents of files filtered out
I have a folder that contains monthly files that go back years. For my purposes I only require the last 12 months. I filter the created date. This works fine, and the resultant import is limited to that data, however when I refresh the data I can see it is loading the filtered files before filtering them out. I.e. during the load I see "x MBs of january 2005.csv" And therefore the load takes forever!
Source = Folder.Files("Path")
Table.SelectRows(Source, let latest = List.Max(Source[Date created]) in each Date.IsInPreviousNDays([Date created], 730) or [Date created] = latest)
4 Replies
I_Like_Pi
Power BI can only load the entire data then filter them out after. This is caused by huge amount of data to be processed, how many files and rows in your folder?
Take a look at my blog, try List.MaxN function.Dynamically Display the Most Recent N Days using P... - Microsoft Power BI Community
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.That solved nothing. Using the folder import function provides a list of files with all of their file properties. This also includes the binaries themselves, and it appears PQ takes the time to load those binaries before you have had an opportunity to filter them from the list. It seems strange as you haven't yet defined how to translate the files yet (i.e. excel, csv, txt, ...). Regardless, in the end I built a Python to run in the background to copy out the current year files to a separate folder. This duplicates data, not what I wanted, but I moved on.
Power Query does NOT load the binaries until you open them on the initial load, so you can easily filter out data. It depends on where the files are being loaded from too. For example, by default, if you load files from SharePoint, it uses the SharePoint.Files() connector. But if you change it to SharePoint.Contents(), it can be much MUCH faster as it only has to load the metadata for the files in a specific folder, not the entire site.
Hi I_Like_Pi
Use Transform File sample query
Import all files once with Combine & Transform.
In the generated query list, apply your date filter in the “Transform Sample File” step, not the main folder query.
This way, Power Query filters early, before full load.
Thanks✨🌹
buttom kudo
